Moving a DB to another person's computer

[specialk9203]

I have a search form built that depending on the dropdown selection on the form, it will run a query to SQL Server 2008 and provide data. There are 5 SQL Server db's so there are 5 different queries. I have each SQL Server linked to the Access DB. When I moved to someones else's computer I got a ODBC Call Failed message. In order to fix this, I had to recreate the links on that person's machine, then change the SQL code in the query with the new name of the linked DB. Is there an easier way to do this?

Thanks in advance!

 

[Alfisdad]

Presumably you have an ODBC connection created on your local machine for each SQL database and that you have linked the tables on your Access database using the correct ODBC connection for each?

As long as you create the ODBC connections on the other machine so that they are exactly the same (i.e. same names etc), the database should work on the other machine without you having to relink tables or change the queries in any way.

 

[specialk9203]

Thanks! I was looking to just provide the other user with a copy of that DB, but that doesn't sound like it is going to work.

 

[gerrythefish]

Yes you can create an ODBC link that travels with the database.

In windows go to : Control Panel - System & Security - Administrative Tools - Data Sources (ODBC)

You should see a form with several tabs
User DSN (connections made here only work for current user)
System DSN (connecons made here only work for users on the current PC)
File DSN (connections made here travel with the file, you want this one)

Under File DSN click "Add" and recreate the connection to your data (make sure you test it as the last step)

Then in Access - under External Connections, use ODBC Databases and then use the file DSN connection you created.

 

[specialk9203]

I believe that's what I was missing. Thanks for the help!