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
MitchkmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MitchkmAuthor Commented:
Thank you I will try this!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.