Use VBA to link tables in SQL Server

Posted on 2009-02-17
Last Modified: 2012-05-06
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
Question by:CaroleTSullivan
    1 Comment
    LVL 44

    Accepted Solution

    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)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now