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

x
?
Solved

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

Posted on 2014-11-14
3
Medium Priority
?
177 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 36

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 40

Accepted Solution

by:
PatHartman earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

971 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