I am experimenting with using ADO to connect an Access frontend to an Access backend. Frontend is just a data entry tool. Users won't be making edits/updates to any records in the backend. Reason for doing it this way is because more than 255 users will be using the frontend concurrently, so normal linked tables with persistent connection to the backend will not work (I know this setup seems illogical; please refrain from saying that I shouldn't use Access for the backend).
I've gotten it to work for a few things. A short SQL Insert statement works as expected, but a longer Insert statement (11 columns in my case) results in an error:
Runtime error '-2147217900 (80040e14)': Syntax error in INSERT INTO statement
Here's what the code looks like:
Public Const ConnectionStr As String = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\\[Network]\[Folder]\[File].accdb"
Dim conn As New ADODB.Connection, SQLstr as String
conn.Open ConnectionStr
SQLstr = "INSERT INTO [mytable](field1, field2, [...], field11) values(1, 2, [...], 11)"
Debug.Print SQLstr
conn.Execute SQLstr
conn.Close
The values in the insert statement are all taken from controls on the frontend from, so there is a bunch of string concatenation, but that isn't the issue. I copied the debug.print output from the immediate window, pasted it in the Access query window, ran it, and the record was inserted without problem.
(I was also getting "syntax error in FROM clause" when doing a select query with a where clause, but can't seem to replicate it now)
So what is the reason for this error, and is there a way to fix it? Is there a text length limit for SQL strings executed with ADO connections? I feel like that wouldn't make any sense... But I tested reducing the number of fields in the insert statement to 9 and it worked.
My workaround is to just use DAO, creating a temporary linked table to the backend, executing the insert statement, and then deleting the linked table
Picture added in response to Jonathan's answer below: VBE screenshot
Language is a reserved word
Your SQL executes without error when you paste it into the query designer because that is using DAO, and DAO is more tolerant of reserved words.
But ADO is more likely to choke on reserved words. A frequent example is naming a column Password. If you test that name, you should see the same pattern as you're seeing with Language: syntax error with ADO; no error with DAO.
