• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 639
  • Last Modified:

Use VBA to link tables in SQL Server

I have an MSAccess database that links tables to a SQL Server database via ODBC.  I would like to dynamically link those tables when the database is opened to avoid having the user needing the ODBC entry on their PC.  I can establish the link to the SQL Server database but from there, how do I LINK the tables.

This is how I establish the connection to the sql server database.  Can I use VBA code to link to the tables I need from here?
        strsql = "Driver={SQL server}; Server=" & g_dbserver & "; Port=1433; Option=0; Socket=; Stmt=; Database=" & g_db & "; Uid=" & g_dbuserid & "; Pwd=" & g_dbpassword & "; Network=DBMSSOCN;"
        OBJdbConnection.Open strsql
        objCommand.ActiveConnection = OBJdbConnection
        OBJdbConnection.CommandTimeout = 0
        objCommand.CommandTimeout = 0
1 Solution
Leigh PurvisDatabase DeveloperCommented:
In your example code you go on to use an ADO object which wouldn't play a part in your table linking.
What you do need though is the ODBC connection string you've built before that.

Then you have a wealth of sources for creating linked tables via code.
And JStreet's utility which Armen has made available to the world - mentioned on Tony's blog here (with a few others)
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now