When using Microsoft Access Linked Tables, there are some problems I encountered recently.
- Access Number precision limit is 28, CAST or CONVERT SQL Server columns using a View to allow Access to link the data directly as a numeric type. Use something like CAST(mycolumn AS DECIMAL(28,6)).
- Query Datasheet View performance can be severely impacted by showing totals. To clear this, show totals and set visible totals all to “None” then hide totals again. Hidden totals are still calculated impacting datasheet performance. Totals calculated in the query design seem to perform better.
- Queries and Linked Tables against SQL Server tables or updatable views can accidentally be edited in Access Datasheet or form view. Use query property RecordSet Type of Snapshot for read-only result set. For further safety, connect to the database server using an account with read-only access.
I run into strange things like this in Access all the time and never remember to avoid the problem next time. Writing it down will help me remember.