3

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?

Community
  • 1
  • 1
Elias
  • 2,602
  • 5
  • 28
  • 57

1 Answers1

3

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
Elias
  • 2,602
  • 5
  • 28
  • 57
  • 2
    Thanks for posting your findings. I was just about to suggest that you have a look at my earlier answer [here](http://stackoverflow.com/a/18939600/2144390). :) – Gord Thompson Sep 09 '14 at 15:38
  • @GordThompson Thanks for the additional resources! I believe(assume) that, with temporary table (the single #), the scope of a table definition and the query definition inside of the VBA code are different and thus MSAccess can't find the table to link to in those separate sessions. – Elias Sep 09 '14 at 15:52