Microsoft Access Linked Table Notes

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.

Advertisements

About notesbytom

Keeping technology notes on WordPress.com to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Uncategorized. Bookmark the permalink.

One Response to Microsoft Access Linked Table Notes

  1. Pingback: Microsoft Access for SQL Server Reporting | Notes by Tom

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s