Saturday, February 28, 2009

Compact and Repair Database

MS-Access files tend to grow over time, for a variety of reasons such as
  • adding many new records
  • large number of user edits
  • running action queries (insert, update, delete)
  • design changes, especially to forms & reports
  • temp tables that are emptied & repopulated
  • using the Name AutoCorrect options
  • Access replication
  • WorkGroup Security with encryption
The theoretical design limit of an Access mdb file is 2 GB, but as you approach 80-100 MB the performance & stability will begin to suffer; above 250 MB the file can become barely useable. This is especially significant when the file is located on the network & you have multiple users accessing that file. Plus, many anti-virus programs will aggressively scan mdb files.

NOTE: It is essential for all users to exit the database, and to make a backup copy of the file before performing the following...

One solution is to do regular maintenance on the database. In Access versions thru 2003, you can use

Tools ~ Database Utilities ~ Compact & Repair Database

which will in most cases shrink the file considerably. The typical reduction can be 10-20%, but can much greater if the Compact has not been done for a long time. In fact, I've seen one case where there was 100:1 reduction for a file that had never been compacted.

An alternate solution is to turn on the option for Auto-Compact, using

Tools ~ Options ~ General, and check the box Compact on Close

...however this option may crash the app if you are running on Terminal Server and/or using Access replication and/or the Access runtime version.

So, how often to perform this task? Good question. It depends on the list of reasons above. You could actually write some code to check the file size, compare it to some arbitrary limit, and then alert the user if the size exceeds that limit. Here is some sample code to check the file size:

Function GetMyFileSize() As Long
' this requires a reference to
' Microsoft Scripting Runtime
' c:\windows\system32\scrrun.dll
Dim fso As FileSystemObject
Dim oFolder As Folder, oFile As File
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(CurrentProject.Path)
Set oFile = oFolder.Files(CurrentProject.Name)
GetMyFileSize = oFile.Size
Set oFile = Nothing
Set oFolder = Nothing
Set fso = Nothing
End Function

Sunday, February 15, 2009

Access replicas and runtimes

We recently completed a lengthy project, migrating a replicated MS-Access 2003 application to a client/server model using Access as the front-end and SQL Server 2005 as the database. The application had previously been modified as necessary to utilize the Access 2003 runtime on terminal server.

There are a few features in the app that display query results in a datasheet view, and every one of those queries (even queries that are not updateable) is opened as read-only to prevent accidental changes, for example:

DoCmd.OpenQuery "MyQuery", acViewNormal, acReadOnly

Since the Access runtime leaves out all the built-in toolbars, a custom toolbar was added to allow the user to filter & sort the data for analysis. After the user completes their analysis & closes the query, any filter or sort is discarded by the runtime, so subsequent views return to the predefined values. At least, that is what we expected.

Final testing of the client / server version of the app revealed that query filter / sort operations, and even changing column widths, upon close would prompt to the user "save design changes?" how could that happen with the runtime & the read-only status?

As it turns out, the answer is quite simple. In the replicated system, changes could only be made in the design master. Any filter / sort etc. in the replica disappeared when the query is closed. However, the new front-end is not replicated, and so although the Access runtime prevents access to the full design environment, we are exposing the filter / sort capability to the user & therefore they can persist changes to their query.

Now, is that defect or a feature? Each user has their own copy of the front-end file, so they are now able to customize their vision of each query for their needs. The data itself continues to be protected due to the read-only status. That sounds like a feature.

Thursday, February 05, 2009

Identity Crisis, part 2

Talk about being lucky.

We reviewed the tables that were almost out of headroom as far as PK identity values hitting the limit of an int, and found that for the two tables that were almost out of room, the PK's were not FK's but were simply in there to satisfy the basic rule that every table should have a primary key.

These tables had only 20-25,000 records; it was only the fact that they were migrated from a replicated Access database that resulted in the very large autonumbers.

Therefore, for both tables we could just go in there & drop the existing identity column & add a new identity column with a seed of 1 and increment of 1. Problem solved.

Monday, February 02, 2009

Identity Crisis

I am working with a SQL Server 2005 database that was upsized from a replicated Access 2003 database. We used the SQL Server Migration Assistant for Access 2003.

Because the Access file was replicated, the Autonumber fields were set for Random, as opposed to Increment, so the resulting IDENTITY fields in SQL are spread across the range of an int, which is +/- 2 billion give or take. However, for the SQL database the SSMA set them to Increment, and so new records in some of the tables are approaching the upper limit of an int. Even worse, Access does not recognize bigint which would have been a quick fix; when I tried that all the fields of the table displayed #deleted.

My goal was to find out how much "headroom" there was IOW how many records could be added before reaching the int limit. I found this function that helped to answer my question:


which returns the next identity value for the table. This is not for production, since users can be hitting the db all the time, but it gave me the current highest value. Then, I could calculate how many records could potentially be added to the table. Which in some cases wasn't very much.

Now I need a solution for my identity crisis.