I’ve been seeing a lot of talk lately about linked SQL Server tables…ever since I’ve started this blog I’ve address it several times, but there have been some changes since my last diatribe that warrant revisiting the issue again.

ADVERTISING
MS Access Consulting

Linked SQL Server Tables – Should you use them?

YES! Linked tables are a great way to leverage SQL Server in your app. In the old days some developers would use unbound forms and elaborate code to read/write to SQL Server, and for good reasons:

  • Networks were not as fast back then, remember token rings?
  • The ODBC stack was horrendous in the early days, a charm to work with now
  • Finally, Access was not as good in handling linked tables as it is now

Linked tables will allow you to bind your forms and make it easier to design and use them, run queries in Access and use lookup tables.

So what’s the catch? 

There are several issues you need to be aware of when using linked tables. The first one is NEVER use a combination of linked tables with local tables in your queries. Instead download the server side tables as temp tables in your app or upsize your local tables to SQL Server and do the processing on the server in it’s entirety.

Don’t do massive update queries using Access

If you ever need to update a lot of records at once, say, increase pricing on all items by 10%, you’re much better off doing the update via a pass-thru query or on the server directly.

Don’t load more data than you need to

I’ve bounded forms to tables with several million records with no problem, how? By using the where clause of the DoCmd.OpenForm command. Limiting data to just a fraction of records at any given time. Here’s how: Say you have a table of customers called tblCustomers and that the primary key is CustomerID, to load just one customer in your frmCustomers form you would use the following syntax:
DoCmd.OpenForm "frmCustomers", WhereCondition="[CustomerID] = " & lngCustomerID
Where lngCustomerID is a variable holding the CustomerID you wish to see.

Do design your SQL Server tables for optimum  performance with Access

Make sure you follow my guidelines for designing SQL Server tables with Access:

  • All tables need to have a TimeStamp or RowVersion field.
  • All dates should be in the Date/Time format
  • All bit fields need to have a default value of Zero
  • All text fields should be VarChar unless you need international characters, then use nVarChar
Beware the security issues
If you store your password with your linked tables you’re just asking for it if it falls into the wrong hands. Instead use Ben Clothier’s method when linking tables, or recreate and delete them when your app starts and quits. We recommend using Active Directory and trusted links as your best scenario.