Syntax errors when using ADO to export Excel data to Access 2007

Question

I'm trying to export the System, Account Number and WorkOrder fields from a spreadsheet to a table in Access 2007 as long as the WorkOrder fields don't match.

So far I keep getting an error saying that '" is not a valid name'. Is that happening because tblTest has more fields than the Excel recordset? I'm at a loss.

Dim xlString As String
xlString = "[Excel 8.0;HDR=YES;DATABASE="

Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "\SA Daily October.accdb"
dbWB = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
dsh = "[" & Application.ActiveSheet.Name & "$]"
destTable = "tblTest"
destTablePath = dbPath & "]." & "[" & destTable & "]"
xlSheet = xlString & dbWB & "]." & dsh

cn.Open scn
ssql = "INSERT INTO tblTest ([System], [Account Number], [WorkOrder])"
ssql = ssql & " SELECT " & xlSheet & ".[System], "
ssql = ssql & xlSheet & ".[Account Number], "
ssql = ssql & xlSheet & ".[WorkOrder] FROM " & xlSheet
ssql = ssql & " WHERE NOT EXISTS (SELECT [System], [Account Number], [WorkOrder] FROM [" & destTablePath & " WHERE [" & destTablePath & ".[WorkOrder] = "
ssql = ssql & xlSheet & ".[WorkOrder])"

cn.Execute ssql

what's the value of `ssql` when you place a breakpoint (F9) on the `cn.Execute` line? — Mathieu Guindon, Oct 29 '15 at 21:25
I get something like this: INSERT INTO tblTest ([System], [Account Number], [WorkOrder]) SELECT [ExcelTable].[System], [ExcelTable].[Account Number], [ExcelTable].[WorkOrder] FROM [ExcelTable] WHERE NOT EXISTS (SELECT [System], [Account Number], [WorkOrder] FROM [C:\$PATH\SA Daily October.accdb].[tblTest] WHERE [C:\$PATH\SA Daily October.accdb].[tblTest].[WorkOrder] = [ExcelTable].[WorkOrder]) — memearchivingbot, Oct 29 '15 at 21:54

score 0 · Answer 1 · edited May 23 '17 at 12:29

Check your brackets around the database path names in WHERE clause subquery. They are not closing properly. In fact, since you are connecting to the very database using ADO, you don't need absolute path reference of the .accdb file.

Use debug.Print ssql just before executing the query so you can better check punctuation of string output in the Immediate Window towards the bottom. If you can't find it go to on menu of VBA IDE: View / Immediate Window or Ctrl + G:

ssql = "INSERT INTO tblTest ([System], [Account Number], [WorkOrder])"
ssql = ssql & " SELECT " & xlSheet & ".[System], "
ssql = ssql & xlSheet & ".[Account Number], "
ssql = ssql & xlSheet & ".[WorkOrder] FROM " & xlSheet
ssql = ssql & " WHERE NOT EXISTS (SELECT [System], [Account Number], [WorkOrder] "
ssql = ssql & " FROM [" & destTablePath & " WHERE [" & destTablePath & ".[WorkOrder] = "
ssql = ssql & xlSheet & ".[WorkOrder])"

Debug.Print ssql

By the way you can avoid the WHERE NOT EXISTS subquery with a LEFT JOIN ... NULL for readability if not efficiency.

INSERT INTO tblTest ([System], [Account Number], [WorkOrder]) 
SELECT [ExcelTable].[System], [ExcelTable].[Account Number], [ExcelTable].[WorkOrder] 
FROM [ExcelTable] LEFT JOIN tblTest 
ON [ExcelTable].WorkOrder = tblTest.WorkOrder 
WHERE [tblTest].WorkOrder Is Null

the ) at the end of the WHERE clause matches the one outside of the SELECT the way it's supposed to. I triple checked the [] brackets and they all match up properly as well. That being said I'm going to try your LEFT JOIN suggestion — memearchivingbot, Oct 30 '15 at 16:17
As mentioned, you do not need the destination path names as the very database you reference is the connecting ADO database. — Parfait, Oct 30 '15 at 17:38

Syntax errors when using ADO to export Excel data to Access 2007

1 Answers1