MSAccess VBA - Issue on Insert statement

Question

Dear all,

by this VBA MSAccess code I'm going to add some values into the MYTABLE placed in and MSAccess database:

INSERT INTO LOG_TABLE ([USER_ID], [USERNAME], [USEREMAIL], [LOCAL_IP_ADDRESS], [DEVICE], [USERACTIVE], [Log Date], [Module], [Action], [Command]) VALUES (MyUSERID, MyUSERNAME, MyUSEREMAIL, MyLOCAL_IP_ADDRESS, MyDEVICE, MyUSERACTIVE,  Now(), MyModule, MyAction, SQLString)

The issue is SQLString variable which is storing the below query statement:

SQLString = "SELECT * FROM MYTABLE WHERE COUNTRY_ISOCODE IN ('AF', 'AL', 'DZ', 'AD', 'AO', 'AM', 'AT', 'AZ', 'BH', 'BH', 'BY', 'BE', 'BE', 'BJ', 'BA', 'BW', 'BG', 'BF', 'HR', 'CY', 'CZ', 'DK', 'DK', 'EG', 'EG', 'EE', 'ET', 'FI', 'FI', 'FR', 'GF', 'GA', 'GE', 'DE', 'GH', 'GR', 'GP', 'HU', 'IS', 'IQ', 'IQ', 'IE', 'IL', 'IT', 'JO', 'JO', 'KZ', 'KE', 'KW', 'KW', 'KG', 'LV', 'LB', 'LB', 'LY', 'LT', 'LU', 'LU', 'MK', 'MW', 'ML', 'MT', 'MQ', 'MU', 'MD', 'ME', 'MA', 'NA', 'NL', 'NL', 'NG', 'NO', 'NO', 'OM', 'OM', 'PK', 'PK', 'PS', 'PS', 'PL', 'PT', 'QA', 'QA', 'RE', 'RO', 'RU', 'SM', 'SA', 'SA', 'SN', 'RS', 'SK', 'SI', 'ZA', 'ES', 'SE', 'SE', 'CH', 'TJ', 'TZ', 'TN', 'TR', 'TM', 'AE', 'AE', 'UG', 'UA', 'UK', 'UZ', 'YE', 'YE', 'ZM', 'ZW')"

[Command] field at database level is defined as Long Text

![](https://learn-attachment.microsoft.com/api/attachments/07461818-d34e-4407-be56-515373c313e6?platform=QnA

Answer 1

Then you must be identifying the VALUES somewhere, that's not clear in your post. Can you explain why you are storing a the text of a SQL statement?

On my little tool I have developed I would like to capture all the actions made by the users and in this scenario I would like to save in a table the sql query coming from the click on button "Find" placed on my userform

Answer 2

Then you must be identifying the VALUES somewhere, that's not clear in your post. Can you explain why you are storing a the text of a SQL statement?

Answer 3

The INSERT INTO statement needs to identify values used in the VALUES clause. You don't do that.

It is not clear where these values are coming from. Can you elaborate?

The INSERT TO statement I built works good except for the last part where I have to add the content of SQLString into the [Command] field of my database.

SQLString is a string variable which contains a SQL query statement

Answer 4

The INSERT INTO statement needs to identify values used in the VALUES clause. You don't do that.

It is not clear where these values are coming from. Can you elaborate?

Answer 5

Remove "@" from the format property.

It causes the field to truncate at 255 chars.