Using SQL with Access

[CAM]

I was fortunate to get some tips using SQL Server as the database engine and
Access as the front-end due to the slow response from my companies WAN. My
questions is how much re-programing I have to do (queries, reports, macro,
module, etc.) and won't Visual Basic be better? Who knows a good book on
using Access and SQL Server? How difficult it is to use SQL - Linking
tables? Thank you in advance.

 

[Pat Hartman\(MVP\)]

The most important thing to remember when using Access with linked ODBC
tables is to ALWAYS use queries as your Recordsources for forms and reports.
It is IMPERATIVE that you limit the number of records brought from the
server. You can do this easily either by using the where argument of the
OpenForm method or by using queries with criteria as the RecordSources.
Filtering a form will not reduce the number of records requested from the
server. You also need to be careful about using functions in the where
clauses of your queries. Not all functions can be "passed through". Jet
makes every effort to "pass through" all queries to the server. It succeeds
pretty well with non-updatable queries. So, if your form will not update
data, make the recordset type - snapshot. Reports automatically generate
snapshot queries regardless of how you define them.

I have created many applications that use a variety of ODBC datasources.
All of them worked well with Access. Start by assuming that your queries
won't need changing. Run the upsizing wizard and just ask for the tables to
be linked. (do not create an .adp) Then as you test your app with volume
data, you'll know whether or not you need to resort to stored procedures or
pass-through queries.