I have a large system with twenty users each having their own database (Access 2002)linked to a centrally held 'data' database. My company has now decided to move the data database to SQL Server and I am starting to panic. I know I shall have to create New ADODB.Connection (s), but where should this be done to allow all the users' databases to link to the data database just as it did when the data database was MDB. Can anyone recommend articles, or even books that will guide me thorugh this process.

Thanks a million.

Siptah

Hi Siptah,

Marcia Chipman and Any Baron's "Microsoft Access Developer's Guide to SQL Server" is the best book I've found for designing 2-Tier SQL Server applications using Access as a front-end. They cover using:

a) An unbound .mdb front-end that uses a mix of DAO and ADO over an ODBC connection to execute pass-through queries that populate local Jet tables with retrieved data, and SQL Server stored procedures that perform insert, update and delete operations.

b) An unbound .adp front-end that uses ADO over an OLEDB connection to retrieve data into local XML files, and execute SQL Server stored procedures that perform insert, update and delete operations.

Both arrangements use Recordsets with clint-side cursors. Both global connection objects (that remain open for an entire user session) and global connection strings (that allow connections to be opened and closed as needed) are covered.

I think they also cover linking SQL Server tables to a bound .mdb file.

Sample code is included that uses the SQL Server Version of Northwind.

Good book. Spent hours and hours in it.

HTH,

Bob

To get you up and running as quickly as possible with a working 2-tier application, I just wanted to mention that complete versions of the unbound .mdb and .adp applications are located in the Chapter 11 downlaod which is available from SAM'S Publishing at:

http://www.samspublishing.com/title/0672319446#

Bob

Thanks Bob, I've just ordered it through Amazon.

Siptah

Hi Siptah,

While I'm thinking about it, Microsoft does have a sample Client/Server download I've palyed with that uses a bound .adp file. That's definitely the simplest and quickest way to work with SQL Server from Access. The download is at:

http://msdn.microsoft.com/library/de...html/apg02.asp

Unbound projects have a lot of scalability/performance advantages over bound projects, but they require a lot of coding.

Also, the .mdb application in the Chipman/Baron download actually uses two connection strings. One is a DAO/ODBC connection that is set programmatically as each pass-through query's ODBC Connect Str property, the other is an ADO/OLEDB connection to execute the insert, update, delete stored procedures on SQL Server. Both applications also support both Windows Authentication and SQL Server Authentication to log onto the server. Good stuff. Enjoy it, and don't panic! :)