Switching Access External Links using a file based DSN

I am using Access 2010 on a migration project to SQL Server 2008. In my development server I have imported all the necessary tables and have created external links to them within the Access databases to be converted. I figured I would use a file-based DSN so that I could easily change the location of the dev server to the production server when it was time to do so.

But my strategy seems to not be able to work. I changed the file based DSN and used the Linked-Table Manager to reset the links but the links on each of the tables still points to my developement database server.

Is there anyway to accomplish this task without manually relinking the hundreds of tables involved in the migration?
Charles SugdenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Dale FyeOwner, Developing Solutions LLCCommented:
I prefer to use DSN-less connections.  Check out this link for a good explaination
Charles SugdenAuthor Commented:
That sounds like it might work. I suppose that the property change 'sticks' after the one time you run it or does it have to be run every time the database is accessed?
Dale FyeOwner, Developing Solutions LLCCommented:
If you leave the tables linked to the front end, you would only need to run the code when you need to switch back and forth between your production and development data.

I tend to delete the linked tables from my applications when they close the application.  This means that I need to relink all of the tables each time the application starts.  It takes a little longer to load, but helps to minimize unauthorized access to the actual data.

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
Charles SugdenAuthor Commented:
Thanks again. That seems like a great solution.
Dale FyeOwner, Developing Solutions LLCCommented:
glad to help
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.