In my Access database VBA code, I would like to create a linked table to a global temporary table in SQL Server. I can't seem to get it to work. This is what I have tried:

Dim adors as new ADODB.Recordset
Dim adoConn as ADODB.Connection

Set adoConn = New ADODB.Connection

adoConn.ConnectionString = "Driver={SQL Server};server=MYSERVER;database=MYDATABASE;uid=MYUSER;Pwd=MYPASSWORD;"

adors.Open "CREATE TABLE ##MYGLOBALTEMPTABLE (ID INT)", adoConn, adOpenDynamic, adLockOptimistic

Set adors = Nothing
Set adoConn = Nothing

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;Driver={SQL Server};server=MYSERVER;database=MYDATABASE;uid=MYUSER;Pwd=MYPASSWORD;", acTable, "##MYGLOBALTEMPTABLE", "MYGLOBALTEMPTABLE"

The TransferDatabase line fails with

Run time error 3011: The Microsoft Database Access Engine could not find the object '##MYGLOBALTEMPTABLE'. Make sure the object exists...

The global temp table is definitely being created: I can select * from ##MYGLOBALTEMPTABLE in database MYDATABASE, and its there.

How can I create a linked table to an SQL Server global temporary table?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.