?
Solved

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

Posted on 2014-11-14
3
Medium Priority
?
174 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

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 39

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

719 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