How can I change the database (SQL Server) of my Access front end application?

We have an access application using a SQL Server database back-end.  Connection to the database is via ODBC.  We are setting up a Dev environment - Dev Access front-end and a dev SQL Server database.  ODBC has been created pointing to the Dev database.  How do we now update the table link so that they are pointing to the Dev database and not the original production DB?  I clicked External Data--> ODBC Database and it prompts me to 2 options -
1) Import the source data into a new table in the current database
2) Link to the data source by creating a linked table.

It seems that I need to select option 2.  Then it will prompt me to the Data Source which will allow me to select the Dev database.  Then prompts me to select tables.....Am I doing the right thing???  I've never worked with Access before.
Pacita TibayAsked:
Who is Participating?

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

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.

ste5anSenior DeveloperCommented:
You have two options:

1) use a DSN. In this case you link your tables once against it. When you need to change the database, then you modify the DSN. The DSN must be created on each client having the same name.

2) Use DSN-less connections and change the connection string by code. Modify for each linked table its connect string. See this thread for a code sample.
Pacita TibayAuthor Commented:
We used option1.  We need to change the database and we changed the DSN.  Because the tables were linked to the other database, we need to refresh or update the linked tables so that they are pointing to the new database.  When we linked the tables to the new database, the linked tables are showing dbo_tablename.
Here is a database that contains the objects you need to relink your tables.  I built this form originally because my application linked to two different databases in three staging libraries - i.e. production, QA, and test.  It also used views whose pseudo indexes needed to be recreated when the target database changed.  I left the DDL queries in the database so you could see them.  You obviously would build your own.  The code also relinks passthrough queries if you have any.  You will also need to populate tblConnectionStrings with the strings you need.

You must have deleted the original links and linked again rather than refreshing the existing links.  You will need to fix up the linked table names by removing the dbo_ prefix before attempting this relink code or you'll end up with the same problem.

Here's code to remove the dbo_ prefix:
Public Sub Remove_DBO_Prefix()
Dim obj As AccessObject
Dim dbs As Object
    Set dbs = Application.CurrentData
    'Search for open AccessObject objects in AllTables collection.
   For Each obj In dbs.AllTables
        'If found, remove prefix
       If Left(, 4) = "dbo_" Then
            DoCmd.Rename Mid(, 5), acTable,
        End If
    Next obj
 End Sub

Open in new window


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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

ste5anSenior DeveloperCommented:
When your tables are linked against a DSN, then you don't need to relink the tables. Just change the DSN in odbcad32.
Pacita TibayAuthor Commented:
The DSN name is different.  Making it different from a production database.
Changing the DSN works for your OWN PC as long as you remember to stay on top of it,  but you would still have to refresh the links to pick up any changes in the tables and that may also break pseudo indexes which make the views updateable.

Changing the DSN's for the users isn't viable.  Once the app is in production, they will need to be able to open the production version to do actual work and the dev version to test the new mods so using one DSN and changing it won't work for them.

One suggestion I would make to prevent an accident is when "you" are the user opening the app, have the menu change color or show a banner across the top when your FE is a test version (on your local PC) but is connected to the production Datbase on the server.  Frequently, you'll want to test with production data but you need some visible clue to warn you to NOT update it accidentally.

When I open the app using the production FE and the production BE or using the test FE and the test BE, all the colors are normal.  It is only when the FE is a test version and the BE is production that you are in danger of making a mistake.
Hypercat (Deb)Commented:
If you've got your DSN set up already in ODBC, then all you need to do is what you've already described.  If you're going to be adding and editing data in that database, then choose "Link"; if the database is static and you're just testing the table structures and creating reports, forms, etc., for use with a future active DB, then you could just import the tables into the Access DB.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Rather than changing the DSN, setup multiple DSN's and then switch between them in Access. You'll find this far easier than trying to change a single DSN's.  When I use this approach, I create the DSN's as part of the install.

 You can also use this approach to switch between multiple companies or DB's (i.e. production, test, and dev) because doing it in Access means the users don't need any special privs.

 The other option is basically the same; use DSNless connections, then change the SERVER=  in the tabledefs  connect property.

 I have code for doing both.

When you have multiple back ends and need to switch regularly, it is best to use a tool to control the switch to ensure that all parts including DDL and passthrough queries get updated also.  The linked tables manager is a poor tool for anything  involving multiple databases.
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.