CODEWITHSUNDEEP
sqlt-sqlms-accesstemporary
Elias
Elias

Reputation: 2632

Can You Programatically Link a SQL Server Temp Table To Access?

I am currently creating a temporary table in SQL Server from Microsoft Access to aid in what can be a relatively slow update (Local MSAccess table to SQL Server). I tried using the solution from this question here: Programatically create ODBC connection and link tables in MS Access, but I get an error

The Microsoft Jet database engine could not find the object 'TABLE NAME'. Make sure the object exists and that you spell its name and the path name correctly.

Now I know that I am creating the temp table in the tempDB database so I assumed that the following would have worked.

Dim tDef As TableDef
Set tDef = db.CreateTableDef("#TEMPTABLENAME")
tDef.Connect = "ODBC;Description=DESCRIPTION;DRIVER=SQL Server Native Client XX.X;
                SERVER=SERVERNAME;Trusted_Connection=?;DATABASE=tempdb;"

tDef.SourceTableName = "USERNAME.TEMPTABLENAME"

DATABASE.TableDefs.Append tDef

Am I doing something wrong or is this just not possible?

Upvotes: 3

Views: 1795

Answers (1)

Elias
Elias

Reputation: 2632

After messing with this for a bit I found that using a Global temp table fixed the issue. One extra pound symbol made the difference. 

Dim tDef As TableDef
Set tDef = db.CreateTableDef("##TEMPTABLENAME")
tDef.Connect = "ODBC;Description=DESCRIPTION;DRIVER=SQL Server Native Client XX.X;
            SERVER=SERVERNAME;Trusted_Connection=?;DATABASE=tempdb;"

tDef.SourceTableName = "USERNAME.##TEMPTABLENAME"

DATABASE.TableDefs.Append tDef

Upvotes: 3

Related Questions