SQL Server-Access Integration

[jmon]

A friend asked me the following question; He has to set up a system with a SQL SErver 2K backend and an Access Front End. I'm assuming that the tables should be on the SQL server side; He asked should the queries be on the clients (in Access) or server (SQL2K).

If more info is needed, what are some possible scenarios and their implications.

Thank you,

jmon

 

[DrewConn]

Ive done this is the past and yes you do want the data on the SQL server. Set up your Access tables to link to SQL. As for the queries it depends on the amount of data you have. The most efficient way if you have a lot of processing that needs to be done is to have the view on sql and have a pass through query on Access to run it.

 

[tlbroadbent]

Most, if not all, data should be on the SQL Server. Exceptions should be restricted to small static lookup tables. All data selection, updates, inserts and deletes should be handled at the SQL Server level if possible. Use views and stored procedures to handle data access.

Avoid attached tables whenever possible. Use pass-through queries to access views and stored procedures. Check out the "Microsoft Access and SQL Integration Resources" at the following link.

http://msdn.microsoft.com/library/en-us/dnacc97/html/acsqlres.asp

Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm