How do I change Linked Tables in Access?

Posted on 2014-08-13
Last Modified: 2014-08-15
I am getting an issue with Linked Table Manager.  It cannot find the Object.  
I am changing DSN from Oracle to SQL.  How do I change the queries to look at the New Table Names?

On the screenshot I am trying to change DSN=LawsonP (Oracle) to DSN=MFDBPD;DATABASE=PPROD
Question by:Mitchkm
    LVL 84
    There's no screenshot, but if you're having an issue with the Linked Table Manager, which is a feature of Access, then you really should correct that. I'd strongly suggest you make a backup of your database, and then do this:

    1. Make sure your Office and Windows installations are fully up to date.
    2. Compact your database
    3. Compile your database - from the VBA Editor window, click Debug - Compile. Fix any errors you may find, and continue doing this until the menuitem is disabled.
    4. Compact again

    You may also need to Decompile your database. To do that, create a shortcut with this as the Target:

    "full path to msaccess.exe" "full path to your database" /decompile

    Run that shortcut, then perform steps 2, 3 and 4 again.

    You also may need to create a new, blank database and import everything into that new container.

    Author Comment

    Screenshot is attached.

    I have done steps 1 to 4 and then tried again.  It did not work.

    Author Comment

    I just tried to decompile and it did not work.
    LVL 84
    I sometimes find it simpler to remove the links, then use the Get External Data tab to recreate those links.

    Author Comment

    Is there an easy way to update all queries in the database?
    LVL 84
    I'm not sure what you mean by that. Are you using Pass-Through queries?

    Author Comment

    I am running append queries.  I have to update all queries.  I am switching from Lawson_GLTRANS to dbo_GLTRANS.

    Is there an easy way to do this.  I have multiple queries and databases to do.

    That is why I was hoping that Linked Table Manager would work.
    LVL 84
    But are your queries "Pass Through"?

    That is, if you open the query in Design view, and view the Properties, is there anything in the Connect property?

    If there is, then you're running a "Pass Through", and you have to handle those outside of the Linked Table Manager.

    If you're not, then your queries use the linked tables, and all you have to do is fix the links, and they should work. From the view you show, it looks like that's what you've got.

    Have you tried removing the links and recreating them? As I mentioned, that's generally the simplest way to make sure that you're pointing to the correct database.

    Author Comment

    There are no Pass Through queries.

    Yes I have tried removing the links and recreating them.  I just have about 50 different databases and a minimum of 10 queries in each.  I was hoping for a shortcut.
    LVL 84

    Accepted Solution

    So that you're clear: You do NOT have to do anything with the queries unless they are Pass Through queries (i.e. they have a value in the Connection property). Most people don't use pass-through queries, so relinking the tables should suffice.

    You can use code to recreate your link:

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Set dbs = CurrentDB
    For Each tdf in dbs.TableDefs
      tdf.Connect = "Your full connection string"
    Next tdf

    Open in new window

    Note too you can set your dbs variable to point to a remote database:

    Set dbs = DAO.OpenDatabase("Full path to the remote database")

    After that, the code above would work on the tables in that database ...

    Author Comment

    Thank you I will try this!

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    754 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

    24 Experts available now in Live!

    Get 1:1 Help Now