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

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.

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.

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:

SELECT IDENT_CURRENT('MyTable') + IDENT_INCR('MyTable')

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.

SSMA - the Memo bug - part 2

My last post discussed a method for changing the data type of a field in SQL Server. My suggestion was to use the SSMS table designer to create the script for you, but there is a faster easier way provided that you can write some basic T-SQL.

Keep in mind that the design requirement is to create a script to apply the change, because this same change needs to be applied to three different databases with the exact same result. If you were going to do this on a one-off basis then you could just go in there & make the change.

The new approach mimics what I would do in MS-Access:

• Create a new field with the desired data type
• Copy all the data from the existing field into the new field, while making any conversions or adjustments.
• Delete the original field
• Rename the new field to match the original name

Here is a sample of the T-SQL script. The existing field MyField in the table MyTable is being changed from nvarchar(max) to nvarchar(4000):

ALTER TABLE dbo.MyTable ADD Tmp_MyField nvarchar(4000) NULL
GO
UPDATE dbo.MyTable SET Tmp_MyField = CONVERT(nvarchar(4000), MyField)
ALTER TABLE dbo.MyTable DROP COLUMN MyField
GO
EXECUTE sp_rename N'dbo.MyTable.Tmp_MyField', N'MyField', 'COLUMN'
GO

The only shortcoming of this approach is that the "renamed" field will move to the last position in the table, so if that matters in your application then the SSMS script method is the better choice.

In my case, the app didn't care about the ordinal position of the field. Plus, I had 59 fields scattered in 25 different tables that needed conversion, so I wrote some VBA to loop thru the TableDefs in Access & create the change script for me. If you're interested you can view the VBA module here: SQL Memo Fixup.

PLEASE NOTE that if you have Memo fields in your Access database, you will need to run this code to create this script in Access before you run the SSMA conversion, and then apply the script to the SQL database after you run the conversion.

SSMA - the Memo bug

I'm working with the Microsoft SQL Server Migration Assistant to convert an Access 2003 db to SQL Server 2005. Just today I found another little bug.

In Access you can have a Memo field that holds up to 64k characters, give or take. This is great for adding comments and notes.

The SSMA converts those as nvarchar(max) and correctly moves the data, however when linking to it using the SQL Native Client, Access thinks it is a text(255) field and displays only the last 255 characters of the field. Not good.

The fix is that you have to change the data type to nvarchar(4000) and relink; Access will then retrieve the entire field (up to 4,000 chars of course). In the event that you have Memo fields that exceed 4,000 characters - good question. You might want to consider saving those on the file system & storing their locations in SQL.

The safest way to make the table change:

• using SSMS, open the table in design view
• make the change but don't save it
• click the script icon to save the table change as a T-SQL file
• close the table without saving the change
• make a backup of your database - !
• apply the script
• relink the Access front-end
• test

Time Sheets

I am working on an MS-Access time & billing program for consultants. This is an old program that was originally designed in Access 2.0, and over the years it was converted to 97 and then 2000, and finally was split into separate front end / back end files.

One of the new features requested is to allow the consultant to view their hours in comparison to the standard 8-hour day and 5-day week. For example, if they work 6 hours on Monday, then 10 on Tuesday, then 7 on Wednesday, the program should show 23 hours worked vs. the standard 24 hours (3 days x 8 hours) and indicate that they are 1 hour short. This required a few new functions so I'd like to share those.

First of all, I needed to filter the dates to the current week. For that I created two functions, fairly straightforward, as follows:

Function Get_StartOfWeek() As Date
Dim d As Date
d = Date
Do While DatePart("w", d, vbSunday) > 1
d = DateAdd("d", -1, d)
Loop
Get_StartOfWeek = d
End Function


Function Get_EndOfWeek() As Date
Dim d As Date
d = Date
Do While DatePart("w", d, vbSunday) < 7
d = DateAdd("d", 1, d)
Loop
Get_EndOfWeek = d
End Function

...and then the WHERE clause in the query looks like this:

WHERE [WorkDate]>=Get_StartOfWeek() And [WorkDate]<=Get_EndOfWeek()

The next step was a bit of a challenge, because the consultant might work on a Sunday or Saturday and those hours need to be included in the weekly totals, but not affect the "standard week" hour calculation. My solution was to use the Choose() function which then allowed me to set the daily expectations according to the day of the week. This is used inside a query, so I could not use the vbSunday constant, that's why the "1" is hard-coded into the DateDiff() function:

StandardWeekToDate:
8*Choose(DateDiff("d",Get_StartOfWeek(),[WorkDate],1),1,2,3,4,5,5)

You may ask, why are there only six items in the Choose arguments, when there are seven days in a week? The reason is that the Choose arguments are 1-based, so if the [WorkDate] is a Sunday, then DateDiff returns a zero and so Choose returns a Null. Which is fine in this particular scenario, because Sunday is not a normal workday.

If you need to load values for all seven days of the week, then you have to subtract 1 from the [WorkDate] in order to get a Sunday date to return item 1 from the Choose. You can't substitute vbSaturday (7) for the start of week in DateDiff; that would give the wrong answer. Here is the seven-day version:

StandardWeekToDate:
8*Choose(DateDiff("d",Get_StartOfWeek(),DateDiff("d",-1,[WorkDate]),1),0,1,2,3,4,5,5)

SSMA bug watch

The SQL Server 2005 Developer Edition installed on my Windows XP SP3 laptop had crashed over the weekend, so after a fresh reinstall including SP2 with hotfixes up to version 3073, I decided to re-run my SQL Server Migration Assistant for Access (SSMA) to create a new conversion file for testing.

The application performance was extremely poor, so I tried running SQL Profiler to try to find the bottlenecks, but I was seeing reads in the range of 20-50 which seemed trivial, however the Audit Login and Audit Logout events were literally taking minutes instead of seconds. I had saved the trace to a table, so I copied some of the SQL statements into SSMS and they ran very quickly, each barely taking a second.

I did a bit of research on connection pooling, but unfortunately that .NET option is not available in either SQL Server 2005 or MS-Access 2003. So why was Access connecting & disconnecting for every single database access?

Then I looked into the Access app and discovered that the SSMA was using the "SQL Server" provider in the connection strings. I changed that to the "SQL Native Client" and voila! the app performance sped up by a factor of at least 10x. Overall, the performance is now marginally slower than when using the native Access tables. That's fine, because this database will ultimately be running on a real Windows 2003 server with four dual-core CPU's and mucho RAM, waaay more powerful than the puny Centrino in my laptop.

I also took a couple of miscellaneous Access files & converted those as a further test, and they all converted using the old "SQL Server" intended for SQL 2000.

I've always run the SSMA using the wizard to save time, but now I'll need to revisit that to see if there is an option to use the correct provider for SQL 2005.

Max Locks Per File

I have a replicated MS-Access database that is used to store archive data from the production system. This archive is Access 2002 format and is approx 140 MB in size.

The design master was recovered from a replica & saved to a local C: drive, and then a new replica was created & published to a network share. So far, so good.

However, when I attempted to sync the two files, I received an error that the MaxLocksPerFile setting in the registry was exceeded and the sync could not be done. The "Help" button said that the default was 9500 (decimal), and suggested that I use SetOption to change it, but SetOption() has no such option.

I found it curious that a brand-new replica would require the complete sync process even though nothing was changed in either design or data, but I digress...

In any event, I did find the key in the registry by that name, it is located at:

HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile

=dword:0000251c

I changed the setting to 12,000 and the progress meter showed two bars and then froze with the error message. With 15,000 it reached five bars; and finally 25,000 which worked. The progress meter displayed the typical appearance & the sync was successful after a few seconds.

I then went back to experiment with a lower setting of 20,000 to try to find the go/no-go level but the sync was nearly instantaneous, so I suppose that the act of moving the replica forced Access to run the sync process on the entire contents of the file.

Excel prompts to save when no changes made

I have a large Excel workbook that is a catch-all for budgeting, time analysis & other miscellaneous items. When I would open the file to look at something, not change anything but simply close it, Excel would prompt me to save changes?

I found this article that explains "volatile formulas" in Excel such as CELL(), NOW(), RAND(), TODAY() and a few others. Here is the full article:

"Save changes in " prompt even if no changes are made

In the database world, expressions like NOW() are called non-deterministic functions, because they don't have a single repeatable value - NOW() always returns the current date / time which of course changes every time you invoke it.

If your worksheet uses those functions, the only way to avoid the "save" prompt is to turn off automatic recalculation & check the option for recalculate before save. But that makes your worksheet "dead" in the sense that changing cell values does not recalc, which is one of the great things about Excel. You'd have to either press F9 or click the Save icon to recalc after every edit.

Apparently this affects all versions of Excel from 97 up thru 2007.