It seems SQL too long in Access-CodePudding

I am a student enter image description herelearning both VBA and Access while doing a project, I have the following sql to run to put this info to the LogInfo table in Access. My sql looks like the following:

INSERT INTO LogInfo ([Date-Time], Description) 
VALUES('2022-04-10 14:50:37', '[AppMsg] 
INSERT INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, CreditCard) 
SELECT MAX(OrderID) 1, 'vvvvvvvv', 'vvvvvvvv', 'vvvvvvvvv', 'vvvvvvvv', 'vvvvvvv', 'vvvvvv', 'vvvvvvv', 'vvvvvvvvv', 'vvvvvvv', 'vvvvvvvv', 'vvvvvvvv', 'vvvvvvvv', 'vvvvvvvv', 'vvvvvvvv' FROM Orders')

enter image description here

However, I got the error message like in the screen shot.

Any suggestion? Thanks!

CodePudding user response：

If you want that second INSERT sql statement input into Description field, all those apostrophes will be an issue. Use quote marks to define primary string and apostrophes for substrings. Following is syntax for a query object (building in VBA will be a little more complicated).

INSERT INTO LogInfo ([Date-Time], Description) 
VALUES("2022-04-10 14:50:37", "[AppMsg] 
INSERT INTO Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, CreditCard) 
SELECT MAX(OrderID) 1, 'vvvvvvvv', 'vvvvvvvv', 'vvvvvvvvv', 'vvvvvvvv', 'vvvvvvv', 'vvvvvv', 'vvvvvvv', 'vvvvvvvvv', 'vvvvvvv', 'vvvvvvvv', 'vvvvvvvv', 'vvvvvvvv', 'vvvvvvvv', 'vvvvvvvv' FROM Orders")

Assume Date-Time is a text type field.

If you want a dynamic date/time input instead of static string, use Now() function instead - without quote marks.

Advise not to use space nor punctuation/special characters (underscore only exception) in naming convention. Better would be DateTime or Date_Time or LoggedDateTime. Also should avoid reserved words as names. Description is a reserved and if it causes issue, enclose in [ ] or change field name (not DESC as that would definitely cause issue).

CodePudding user response：

Thank you guys very much for your advice and answer!!!!!

I changed my code (mainly from ' to " in the sql) as following, it worked!

sql2 = "INSERT INTO LogInfo " & _
      "([Date-Time], Description) VALUES("""
sql2 = sql2 & msg_date & """, """
sql2 = sql2 & msg_str & """)"