Access 2016 VBA Connect to another Access Table

I using the code below to connect to another access table and get the following error:
AccessOn line - Set CurrentDatabase = DBEngine.Workspaces(0)


Dim CurrentDatabase As Database
Dim MyTableDef As TableDefs
Set CurrentDatabase = DBEngine.Workspaces(0)
OpenDatabase ("U:\Serial Claims DB\Serial_Claims.accdb")
Set MyTableDef = CurrentDatabase.CreateTableDef("tblGlobal")
shieldscoAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
You don't have any code in what you posted that would:
1. do anything more than create the table on the remote database.  do you have any code that actually creates fields in that table?
2. link the table to the current database, only create the table in the BE.

In order to link that new table in the BE database into your FE, you would need to do something like the following:
set db = CurrentDb
tdf = db.CreateTableDef("tblGlobal")
tdf.Connect = ";DATABASE=U:\Serial Claims DB\Serial_Claims.accdb"
tdf.SourceTableName = "tblGlobal"
CurrentDb.TableDefs.Append tdf

Open in new window

0
 
Dale FyeCommented:
which line is it generating the error on?  What is highlighted when you select Debug in the error dialog?
0
 
Dale FyeCommented:
Try modifying your declarations:

Dim CurrentDatabase As DAO.Database
Dim MyTableDef As DAO.TableDef
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
shieldscoAuthor Commented:
Still get an error on line Set CurrentDatabase = DBEngine.Workspaces(0)
0
 
Dale FyeCommented:
should read:

Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase ("U:\Serial Claims DB\Serial_Claims.accdb")

all one line
0
 
shieldscoAuthor Commented:
the code ran without error but did not link the table
0
 
shieldscoAuthor Commented:
Here is the code

Dim CurrentDatabase As DAO.Database
Dim MyTableDef As DAO.TableDef

Set CurrentDatabase = DBEngine.Workspaces(0).OpenDatabase("U:\Serial Claims DB\SC Address.accdb")
Set MyTableDef = CurrentDatabase.CreateTableDef("tblGlobal")

Open in new window

0
 
shieldscoAuthor Commented:
I get an invalid use of property on line

tdf = Db.CreateTableDef("tblGlobal")

Dim Db As DAO.Database
Dim tdf As DAO.TableDef

Set Db = CurrentDb
tdf = Db.CreateTableDef("tblGlobal")
tdf.Connect = ";DATABASE=U:\Serial Claims DB\SC Address.accdb"
tdf.SourceTableName = "tblGlobal"
CurrentDb.TableDefs.Append tdf

Open in new window

0
 
Dale FyeCommented:
sorry,

Set tdf = db.createtabledef("tblGlobal")
0
 
Dale FyeCommented:
Whenever you work with objects, you must use SET
0
 
shieldscoAuthor Commented:
Thanks
0
 
Dale FyeCommented:
glad to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.