Excel VBA generated INSERT statement for Access does not work in VBA but does in Access

Question

I am using VBA in Excel to generate an INSERT statement to be executed within the VBA procedure. When I run the code, I get the runtime error 'Syntax error in INSERT INTO statement' when I get to the Execute statement - the same useful message you get if you have done something wrong in the SQL view within Access. However, when I run the SQL string generated by the VBA code in Access, using old fashioned Copy & Paste, it runs fine - no error message, and the records are there when I open the table.

It is not the way I am executing the command in VBA either as I have a DELETE statement that works as it should do. Code works as below:

Set Cn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")

Cn.Open cnPath
SQLStr = "SELECT * FROM [" & tblName & "]"
Rs.Open SQLStr, Cn, 1, 3

'code builds SQL str here by looping through
'table headers and relevant row

Cn.Execute SQLStr

Resulting SQLStr is something like this:

INSERT INTO [Size Profiles] (Season,Region,Department,ProfType,ColourID,ArticleNumber,StyleName,Colour,Comments,Channel,Fit,SizeProfile,LastChanged,Identifier,FileName,Size,RegionSizeRun,Site,ActualSizeProfile,ActualFileName,ActualLastChanged,ActualPairs,Fixing,Oddity) VALUES ('AW2018', 'UK', 'Childrens', '', 188473, 26145715, 'Product Name', 'Product Colour', '', 'Retail', 'F(M)', 0.186, #05/09/2018#, '26145715AW2018DC01UKF(M)', 'New Sizing Template.xlsb', 70, '', 'DC01', 0, '', #05/09/2018#, 0, '0', '')

Has anyone seen this problem before?

Try to add extra parenthesis ([Size Profiles]) around the table name and let me know if it helps. — Maciej Los
– Maciej Los, Commented May 10, 2018 at 7:43
Try replacing all the empty string values ('') with Null. Storing empty strings is not very common in Access. — Gustav
– Gustav, Commented May 10, 2018 at 7:46

jackelsey · Accepted Answer · 2018-05-10 07:46:41Z

1

Gave up with this after a long day, and sure enough after sleeping on it and looking back at it in the morning I've realised that one of the column names (Size) is a reserved word in MS Access. Pasting the SQL into the design view of Access, the program must do something to fix this this without informing you.

I resolved the issue by adding a piece of code that adds square brackets around the field names, along the lines of:

insertPart1 = "INSERT INTO [" & tblName & "] ("
For c = 1 To maxC
    insertPart1 = insertPart1 & "[" & .Cells(1, c).Value & "],"
Next c
insertPart1 = Left(insertPart1, Len(insertPart1) - 1) & ") VALUES "

answered May 10, 2018 at 7:46

jackelsey

1791 gold badge7 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA generated INSERT statement for Access does not work in VBA but does in Access

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related