Access to SQL Server – How To Handle

Topic

New Reply

FRONT END: MS Access 2007 (Multiple workstations; less than 10) on Windows XP, Vista and Windows 7
BACK END: SQL Server 2008

At our organization we have a department that contracted and had created its own small Microsoft Access database (by an outside developer) for its use. After a few years and several users later that Access DB was splkit so that the data is in a SQL Server Database (SQL Server 2005 at the time) and Access is the front end using the Linked Table Manager to connect the 2 pieces. This has worked albeit with support issues here and there but all were eventually resolved.

We tried to move the database to a new SQL Server (Version 2008) but we are having problems with getting Access to see the new DB. I must admit it’s a nightmare trying to figure out how to use the LTM (Linked Table Manager) and get it to see that the DB is on a different server. I have setup a SQL Login and also added the users NT Domain Accts to the DB (members of the DBO Role) so that permissions should not be an issue and yet no matter how I try to connect the LTM to the new DB on the new SQL Server it fails.

QUESTION : What is the best method/process/code/module for using an ACCESS 2007 front end with a SQL Server 2005/2008 database so that if the DB is moved or changes in some way so that the same login info does not work, one can point ACCESS to the new info and with the right credentials it will re-link to the tables in the new copy of the DB?

NOTE: While this would normally fall onto the ACCESS developer who created the DB I believe he does not possess the knowle3dge to resolve this and so I am actively working it so that our corporate people who need this can move forward with using it.

Thanks

Reply | Quote

Replies

You have to create an ODBC data source for the new database. With that done, using the External Data tab, you can select the ODBC datasource you have created and then select the tables you want to link to.

Reply | Quote

I’d like to re-word what you;ve said to make sure I understand correctly.

The best way to handle this would be to gerenate an ODBC Data SOurce (a DSN I assume) on a system that conatins a copy of the ACCESS DB (The Frong End piece of it) and that has access to connect to the SQL Server instalce where the DB is that store the tables the Access front end wants to link to.

Go thru the External Data tab selecting the ODBC data souce and then relinking the tables.

Save the Access DB file.

Close Access.

Distribute the DSN I create don my system to others that will use this Access DB front end.

That about sum it up? Assuming yes then the question is what type of DSN to use, file, user, or does it matter?

Also, does the DSN info used in the Access DB stick with the Acess file? In otherwords after doing all of the relinking of the tables and saving teh ACcess database file, if I then copy that Access file to another system and load it in Access on that system will it automatically try to use the same DSN that was used on the prior system? Does this new system even need a copy of the DSN?

Thanks

Reply | Quote

See: Beginner’s Guide to ODBC

Also see these: SQL Server Links

Boyd Trimmell aka HiTechCoach
Microsoft MVP – Access Expert

Reply | Quote

See: Beginner’s Guide to ODBC

Also see these: SQL Server Links

Boyd Trimmell aka HiTechCoach
Microsoft MVP – Access Expert

I take it that Access even as of Access2010 is unable to use ADO? I’d love to find a way to avoid ODBC and DSN’s al together. I did see the link about a DSN-Less setup but it still uses ODBC I think.

Thanks

Reply | Quote

I take it that Access even as of Access2010 is unable to use ADO? I’d love to find a way to avoid ODBC and DSN’s al together. I did see the link about a DSN-Less setup but it still uses ODBC I think.

Thanks

Curious why do you want to avoid ODBC?

Boyd Trimmell aka HiTechCoach
Microsoft MVP – Access Expert

Reply | Quote

I take it that Access even as of Access2010 is unable to use ADO? I’d love to find a way to avoid ODBC and DSN’s al together. I did see the link about a DSN-Less setup but it still uses ODBC I think.

To be complete, you can use ADO to connect to a SQL Server database, but in a traditional .MDB format database you would have to use unbound forms. As long as you want to be able to manipulate tables directly, or create queries, you need the linked tables. (Another alternative is is go to the .ADP format, but the ongoing support for that approach is questionable.) So we always use an ODBC connection unless performance is a critical issue. We have systems running all versions of Access from 2000 forward, and SQL Server from 2000 forward quite successfully, and very limited maintenance issues. The one problem with ODBC is that you have to create a DSN on each workstation that wants to use the database. But once you have done a few it is a pretty straightforward process.

Added: To answer your question about the type of DSN, we usually make it a System DSN so roaming user profiles can be used, but it seems Windows 7, particularly the 64-bit version, doesn’t always work, in which case we make it the User version.

Reply | Quote

This might be helpful:

Create a 32-bit DSN on 64-bit machine for SQL Server

Reply | Quote

Have you considered using an ADP with a direct connection to SQL Server, thereby avoiding the ODBC issues altogether? That’s what we do. The only down side is that when you migrate the database, you will have to change the ADP’s database pointers, but that’s a small thing.

Dale Napier
Fleming & Associates
Houston, Texas

Reply | Quote

The other requirement is permission to use the database. Adding each user to the database is slow and time consuming, you are better off using an AD group and using that to also set the DSN / install software / add Icons etc. Then management is via AD group management which can be offloaded to users rather than IT.

A DSN is just a registry entry, so you can poke the values remotely as part of the group membership.

cheers, Paul

Reply | Quote