Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Switching Access External Links using a file based DSN

Posted on 2012-03-20
5
Medium Priority
?
313 Views
Last Modified: 2012-03-21
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?
0
Comment
Question by:CSUGDEN
  • 3
  • 2
5 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37746652
I prefer to use DSN-less connections.  Check out this link for a good explaination
0
 

Author Comment

by:CSUGDEN
ID: 37746761
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?
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 1200 total points
ID: 37746856
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.
0
 

Author Closing Comment

by:CSUGDEN
ID: 37747525
Thanks again. That seems like a great solution.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 37747548
glad to help
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

916 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