Microsoft Access can be a marvelous Front End application to a SQL Server database, IF you plan well. Here are some quick tips

USE TIMESTAMPS ON EVERY TABLE! Without a timestamp, Access makes a column-by-column comparison on every column in your table for DML queries. So, a simple update like:

UPDATE t1

SET C1 = XX

WHERE ID = N,

is converted to,

UPDATE t1

SET C1 = XX

WHERE t1.c1 = t1.c1, t1.c2 = t1.c2, t1.c3= t1.c3….,

This can cause full-table-scans and locks. Just watch the sql profiler and you will be amazed at the differenece a timestamp makes! This is the biggest mistake that all my students and customers make when using Access against SQL server and it causes everyone to poo poo Access.

USE FILTERS. Depending on your access version and connection, filters are processed server side, so you don’t need to dump the entire table to the Access client in order to filter your SQL server data. see http://support.microsoft.com/kb/304259.

USE PASS THROUGH QUERIES. Depending on your access version and connection, Access queries use the local Jet engine to perform query work. So, selecting one record from a table of 1 million records may require all 1 million records to be dumped to the client in order for Access to process the query and return one record. See http://support.microsoft.com/kb/303968.

QRYTMP

I use one Access query (qrytmp) to run all my procs. It returns and DAO recordset that can be bound to forms and reports, or just browsed in the Access query analyzer. So, I use ONE Access query for EVERYTHING!

Remember, however, passthrough queries return non-updateable recordsets. you still need linked tables and/or access queries and filters for that.)

Public Function ram_fnCreateTmpQuery(strSQL As String, intTimeOut As Integer, blnReturnsRecords) As Integer

Dim qdf As DAO.QueryDef

On Error GoTo Proc_Err

ram_fnCreateTmpQuery = 1

' Creates a temporary access query to call stored procedures

' where strSQL is any T-SQL statement, such as

' EXEC sp_WHO2

' UPDATE t1 SET C1 = X WHERE t1.ID = nn

'Delete the Query if it exists

On Error Resume Next 'in case qrytmp does not exist.

DoCmd.DeleteObject acQuery, "qryTmp"

On Error GoTo Proc_Err

Set qdf = CurrentDb.CreateQueryDef("qryTmp")

With qdf

.Connect = gostrODBCConnect 'Use this if u you have a global ODBC or DSN Connection string

'CurrentProject.Connection 'http://support.microsoft.com/kb/281784

'CurrentProject.AccessConnection 'http://support.microsoft.com/kb/281784

.SQL = strSQL

.ReturnsRecords = blnReturnsRecords

.ODBCTimeout = intTimeOut

End With

ram_fnCreateTmpQuery = 0 ' No Error occured

Exit Function

Proc_Err:

ram_fnCreateTmpQuery = 1 ' An error occured

Exit Function

qdf.Close

End Function