Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

How do I change Linked Tables in Access?

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
0
Mitchkm
Asked:
Mitchkm
  • 6
  • 5
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
MitchkmAuthor Commented:
Screenshot is attached.

I have done steps 1 to 4 and then tried again.  It did not work.
LINKED-TABLE-MANAGER.gif
0
 
MitchkmAuthor Commented:
I just tried to decompile and it did not work.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I sometimes find it simpler to remove the links, then use the Get External Data tab to recreate those links.
0
 
MitchkmAuthor Commented:
Is there an easy way to update all queries in the database?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure what you mean by that. Are you using Pass-Through queries?
0
 
MitchkmAuthor Commented:
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.
append-query.gif
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
MitchkmAuthor Commented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

dbs.TableDefs.Refresh

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 ...
0
 
MitchkmAuthor Commented:
Thank you I will try this!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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