We help IT Professionals succeed at work.

How do I change Linked Tables in Access?

Mitchkm
Mitchkm asked
on
219 Views
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
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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

Commented:
Screenshot is attached.

I have done steps 1 to 4 and then tried again.  It did not work.
LINKED-TABLE-MANAGER.gif

Author

Commented:
I just tried to decompile and it did not work.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I sometimes find it simpler to remove the links, then use the Get External Data tab to recreate those links.

Author

Commented:
Is there an easy way to update all queries in the database?
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I'm not sure what you mean by that. Are you using Pass-Through queries?

Author

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
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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

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.
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you I will try this!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.