Solved

Access front-end SQL back-end : quickly switch between prod & dev box

Posted on 2014-11-14
3
157 Views
Last Modified: 2014-11-17
Hi All,

I have two boxes - prod & dev. I have an Access 2007 front end, with an SQL back end.

I'm looking for the quickest way to switch the entire DB between pointing at the different boxes.

Unfortunately, I connect to the server in pretty much every way possible, including VBA (ADO & DAO), pass through queries, linked tables.

Now this is fairly easy to do in VBA for the coded connections, but I wonder whether there's quick way of doing the same for all my pass through queries and tables.

Does anyone have any pre-scripted routines? Or perhaps there's a way to make the connections rely on a central file wherein I can change the connection string?

Any pointers would be appreciated.

Thanks
0
Comment
Question by:James Elliott
3 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 40442874
First of all: It requires a restart of Access to do it safely. Cause connection pooling may interfere here.

Then you do the same as the table link manager, just in your code. You set the Connect property for each object which is affected.
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40443170
I've attached a sample extracted from one of my applications.  We had two databases and three stages - test, QA, and Prod.  It relinks the pass through queries and rebuilds the pseudo indexes that make linked views updateable.
RelinkODBC-POMS140228.zip
0
 
LVL 12

Author Closing Comment

by:James Elliott
ID: 40446977
Just what I was hoping for. Thanks!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Launch app from Access custom ribbon 8 33
Access Crosstab Query with Multiple Values 4 32
Webservices in T-SQL 3 31
Question about DB Schema 27 53
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 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