Access User Group of San Diego logo

AUGSD Member Application:
Access 97: Accessing SQL Server Using Jet/ODBC Direct

(submitted 5/24/00)

Introduce building scalable, replication ready client/server solutions in Access w/ SQL Server. Including calling stored procedures, pass-thru queries, transactions and control/form binding. Name of download: TM_ODBCDirect.zip

Files in download:

1) Accessing SQL Server Using Jet.doc;

2) sdaug.sql; SQL Server script for recreating SQL database

3) sdaug_cs1.mdb; Access 97 database

4) sdaug_data.mdf; SQL Server database

5) sdaug_log.ldf; SQL Server transaction log

Installation:

Install the database file in the data folder for SQL Server (usually C:\MSSQL7\data). Run a stored procedure on the master database to attach the database. You can use a pass through query, query analyzer, or the QBE in the mmc snapin: sp_attach_db 'SDAUG', 'C:\MSSQL7\Data\SDAUG_Data.mdf', 'C:\MSSQL7\Data\SDAUG_Log.ldf' You must create a login "sdaug" for the SQL Server using SQL Server Authentication. During this process, set the default database to "sdaug". A user will be created for the "sdaug" database. Then assign exec permissions to the user stored procedure and read permissions to the tables. Create a DSN for the above database in your ODBC manager applet called "sdaug". Use defaults for everything else. You can run the Access mdb using the default workgroup found in the Windows system directory.

ED Note: The Access DB contains routines that may be of interest even if you are not yet supporting SQL Server, but merely researching or learning client/server development.

Auxilliary programs needed: MS SQL Server 7.0

Version of Access: 97

Click here to download Access SQL Server Presentation

As with all info posted on this web site - you use it at your own risk and no guarantees related to accuracy nor correctness are neither implied nor stated.


 
For more information:
please email the AUGSD
or call (760) 737-8929


[Return to AUGSD Home Page]