Import MS-SQL data into an Access Table ADO-CodePudding

I hope that you can help me or point me in the right direction. For many years, I have used a very clean and simple method of importing data (within VB6, yes!) from a MS-SQL table into an MS-Access database/table as follows:-

WORKING QUERY WITH NO INNER JOINS

Dim myConnection As ADODB.connection
Dim mySQL As String
Set myConnection = New ADODB.connection
myConnection.Open "Driver={Microsoft Access Driver (*.mdb)};" & "Dbq=DB1.mdb;" & "DefaultDir=C:\Temp;" & "Uid=Admin;Pwd=;"
mySQL = "SELECT * INTO [Xray] FROM [ODBC;Driver=SQL Server; SERVER=myServer;DATABASE=myDatabase;UID=myUID;PWD=myPassword;].[ShipTo] WHERE [ShipTo].Company='ABC001'"
myConnection.Execute mySQL
Set myConnection = Nothing

However, for the first time, I now need to start working with Inner Joins. I thought that I could do something as simple as above, but I have come unstuck rather heavily and have no idea how to progress. My Inner Join query is below:-

SELECT        
    dbo.InvcHead.InvoiceNum AS DocNum, 
    SUM(dbo.InvcTax.TaxAmt) AS DocTaxAmt
FROM                        
    dbo.InvcTax
        INNER JOIN
    dbo.InvcHead ON (dbo.InvcTax.InvoiceNum = dbo.InvcHead.InvoiceNum) 
GROUP BY 
dbo.InvcHead.InvoiceNum**

Because of my clear lack of experience using joins with my tried and tested method, I get an error which is probably because of my syntax, but I just cannot see the light. Below is the new connection string/query attempt:-

FAILED QUERY WITH INNER JOINS

Dim myConnection As ADODB.connection
Dim mySQL As String

Set myConnection = New ADODB.connection
myConnection.Open "Driver={Microsoft Access Driver (*.mdb)};" & "Dbq= DB1.mdb;" & "DefaultDir= C:\Temp;" & "Uid=Admin;Pwd=;"

mySQL = ""
mySQL = mySQL & "SELECT "
mySQL = mySQL & "dbo.InvcHead.InvoiceNum AS DocNum, "
mySQL = mySQL & "SUM(dbo.InvcTax.TaxAmt) As Doctaxamt "
mySQL = mySQL & "INTO [Xray] FROM [ODBC;Driver=SQL Server; SERVER= myServer;DATABASE= myDatabase;UID= myUID;PWD=myPassword;].[dbo.InvcTax] "
mySQL = mySQL & "Inner Join dbo.InvcHead ON (dbo.InvcTax.InvoiceNum = dbo.InvcHead.InvoiceNum)"
mySQL = mySQL & "Group By dbo.InvcHead.Invoicenum"

myConnection.Execute mySQL
Set myConnection = Nothing

The error message I get is "Syntax Error in Join Operation"

Is it possible to do this extraction using this method with an inner join query? I suspect my problem relates to the area of the script where I reference the first table of the join.

.[dbo.InvcTax] "

If this is not possible, is there a way to create a table dynamically (as I have done) but from a recordset instead?

Any help of a steer in the right direction will be massively appreciated.

Thanks, Jack

CodePudding user response：

The dbo prefix fails in Access SQL and you miss the database reference for the second table:

mySQL = ""
mySQL = mySQL & "SELECT "
mySQL = mySQL & "T1.InvoiceNum AS DocNum, "
mySQL = mySQL & "SUM(T1.TaxAmt) As Doctaxamt "
mySQL = mySQL & "INTO [Xray] FROM [ODBC;Driver=SQL Server;SERVER=myServer;DATABASE=myDatabase;UID=myUID;PWD=myPassword;].dbo.InvcTax AS T1 "
mySQL = mySQL & "INNER JOIN [ODBC;Driver=SQL Server;SERVER=myServer;DATABASE=myDatabase;UID=myUID;PWD=myPassword;].dbo.InvcHead AS T2 ON (T1.InvoiceNum = T2.InvoiceNum)"
mySQL = mySQL & "Group By T2.Invoicenum"