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.

Sharing Linux folders for Windows

Sharing Windows folders for access by Ubuntu Linux is fairly straightforward using Samba (SMB) but going in the opposite direction had me stumped.

From Windows, I would go into My Network Places ~ Entire Network ~ Microsoft Windows Network ~ Workgroup and I could see the Linux machine. Then I would double-click on it and enter my Linux name and password, but it would not connect.

The solution is that you need to add a Samba user, and you can't do that from the Linux GUI. You have to open a Terminal window and add the Samba user like this, where username is your Linux user name:

sudo smbpasswd -a username

after which you are prompted for the password, which is your Linux password.

At that point, you can access your Linux shares from Windows just like you would any other network share.

Replicated Queries

MS-Access makes it very easy to change the SQL of a query simply by using a statement like

CurrentDb.QueryDefs("MyQuery").SQL = "SELECT * FROM MyTable"

there is no need to Dim or Set anything, simply plug in the SQL and you're ready to go.

However, I just discovered that this does not work in a replicated database. Or I should say, it works in the Design Master but it does not work in a Replica. I needed to apply some complex criteria, more complex than could be expressed simply by using a WHERE condition when opening a report. Testing the code in the DM worked just fine, but users received a message "cannot update...object is read-only" because Access considers this to be a design change & does not allow that in a Replica.

You can code things like changing visibility, font colors, enabling & disabling controls, but QueryDefs are off limits.

The challenge: I had an existing report that used a query for the detail section, and the report footer contained a subreport with a summary that used the same query as the body of the report. Opening the report with a filter or where argument would properly filter the detail section, but the subreport would display all of the data. I tried using the OnOpen or OnFormat event to apply a filter to the subreport but that did not work.

(EDIT) At this point, I tried to change the SQL for the QueryDef using the type of statement shown above, to filter the data for both the body of the report and the summary subreport; this worked in the DM but not the Replica. My original post has not been clear about that. (EDIT)

My solution was basically to create a staging table for the subreport, and flush-and-fill the table prior to opening the report.

In the DM I did this:

• Ran the old (bad) code to set up the SQL statement
• Switched the query to a make-table, to create the staging table
• Switched the query to an append query
• Copied the append SQL and used that to build up a dynamic SQL statement in VBA

The button OnClick would then delete all records from the staging table, run the dynamic SQL to populate that table, and then open the report.

Moving Days

{EDIT} Around about September 2008, I decided not to move articles off this blog after all. I might copy a few to the tips page at my Web site, but otherwise I'm going to keep this blog intact. {EDIT}

When I started this blog three years ago, I had intended to use it as a quick way to publish my technology findings both for the general Internet community, and also to use it as a knowledge base for myself.

As it turns out, the older articles wind up being buried in the archives, grouped by month, so many times I've had to guess the month & drill down from there. The built-in search is quite effective, but being able to scan down a complete list of topics is much faster.

For those reasons, I've been moving articles from blogger to my personal web site, where I use a classic ASP page and the FileSystemObject to present a scrollable list of all articles, sizes, dates etc. The current list can be found at http://www.wvmitchell.com/tips.

Another difficulty using the blogger format is that many html tags are scrubbed-out from posts, so any code samples will lose their indenting & formatting, making them hard to decipher. My workaround has been to present a link to the actual code on a separate web page, but I would prefer to be able to post a complete article intact with all the formatting.

The blogger search facilities work very well, and Google indexes the blogger posts, so much information can be found via a normal Google search, but my web site also includes a search feature so nothing will be lost.

In closing, this blog will eventually be shrunk down to include only tech comments and observations.

Batch Files

By all accounts, DOS is dead. But since cmd.exe is still part of Windows, you can still write DOS batch programs to automate certain tasks.

For example, I am working with an Access application that is rebuilt every month. The previous process was to have the user navigate to a shared drive and use Windows Explorer to copy a new version of the MDB file and then paste it to a specific folder on their C: drive.

The new process uses a simple batch file like this:

@ECHO OFF
CLS
ECHO.
ECHO *** XYZ file setup program ***
ECHO.
IF NOT EXIST C:\XYZ MD C:\XYZ

IF NOT EXIST C:\XYZ\XYZ.MDB GOTO NOFILE
ECHO.
ECHO You already have a copy of XYZ on your computer
ECHO ===============================================
ECHO.
ECHO  Do you want to replace your existing copy?
ECHO  ------------------------------------------
GOTO HASFILE

:NOFILE
ECHO *** COPYING FILE, PLEASE WAIT ***
COPY /Y \\MYSERVER\XYZ\XYZ.MDB C:\XYZ
GOTO END

:HASFILE
ECHO  Y = Yes, replace my old file
ECHO  N = No, keep my existing copy of the file
ECHO.
SET CHOICE=
SET /P CHOICE= Type the letter and press {ENTER}
IF /I '%CHOICE%'=='Y' GOTO NOFILE
ECHO.
ECHO  (Nothing was copied or replaced)
ECHO.
:END
PAUSE
EXIT

For a new user, the folder is created if it does not exist.
Then, if the file exists the user is prompted to either overwrite or keep their existing copy.

Without the CHOICE statement, DOS would ask the overwrite question as
Yes/No/All
and this avoids the confusing "All" option.

Removing Access Replication

Using replication with MS-Access adds numerous system fields and tables, and combined with the replication-tracking system, a replicated Access database can grow to an unwieldy size, especially if you have many users and/or there are frequent changes in the design master.

The customary solution is to rebuild the database, i.e. to create a new non-replicated file and then use that to create a new design master and the replicas.

Unfortunately, you cannot simply create a new empty file and import all your Access objects, because all the system fields in the tables will also be carried over via the import. You cannot simply go into design view & delete those replication fields because they are system fields.

The basic process is to create a new empty file, and then export all the Access objects except the tables, and then export the tables but without the replication fields.

Import the VBA module Unreplicate_Access into your design master, and when you execute the code it will create a new non-replicated version of your database. It will also set a number of startup properties such as hiding the DB Window, turning off shortcut keys and so on.

Once you have created the new file, simply copy it over your existing design master (after you make a backup copy, of course) and then click Tools ~ Replication ~ Create a Replica and follow the prompts.

This module is still a work in progress; it does not set any primary keys, nor does it copy any default values for the tables. This will appear in a future post. But there won't be any code to create relationships; Microsoft actually recommends against using RI in a replicated db due to the mechanics of replication.

SSMA, part 3

My previous posts about the SQL Migration Assistant for Access reported generally favorable results with this product which is used to migrate Access data into SQL Server. It does a remarkably better job than the old Access Upsizing Wizard which very often failed, especially when dealing with date fields. When SSMA has completed its work, the system is mostly operational.

However, there are two common Access design flaws that can impact the application - the first is relying on the behavior of Boolean fields to evaluate as -1 = True and 0 = False. Many Access developers will test for "-1" meaning True, but SQL Server returns "1" for bit fields so the comparison logic fails. The fix is simply to substitute "-1" with "True" (without the quotes) and the operation will succeed.

A second design flaw recently noted is that in Access you can subtract one date from another date, and then perform math on the result - for example

24 * ([date_end] - [date_start])

in Access will give you the number of hours between the two date/time values. However, once your data is moved into SQL Server, this expression will result in

Implicit conversion from data type smalldatetime to int is not allowed. Use the CONVERT function to run this query.

The solution is to use the DateDiff function which exists in both Access and SQL Server. In Access you would write

DateDiff("h", [date_start], [date_end])

and when converting this query to T-SQL for SQL Server you might use

DATEDIFF(hh, [date_start], [date_end])

Access Replication

Replication in Access provides a method of allowing remote users to work with a local copy of the database while disconnected from the network, and then synchronizing their changes when they do connect. This feature has been part of Access since the 90's but sadly, although Access 2007 supports it if you stay with the .mdb file format, if you choose to convert to the new .accdb format this feature goes away (along with WorkGroup Security).

The idea of replication is that you create a Design Master (DM) that adds additional hidden system fields which Access uses to track record changes that need to be replicated. You also create a Replica which has all the programming and data of the DM but without any design options, thus protecting the design from unauthorized changes. During the sync process, data is exchanged between the DM and replica, and design changes made to the DM are also propagated to the replica.

The replication model is sensitive to the drive, path, and file name; moving the DM will cause it to become a replica; copying the DM will result in creating another replica.

In addition to the fields added to each table, there are also a number of hidden system tables created when you replicate a database; the net result is that the DM and the replicas are considerably larger than the original non-replicated file. For example, a 27 MB file can grow to 44 MB when replicated. Once the users begin working with the file and syncing, that file can grow to 48-59 MB (these numbers are taken from an actual production system). For that reason, it is strongly recommended that you enable the Compact on Close option to keep the file as small as possible under the circumstances.

It is important to note that creating a replica will change AutoNumber fields from Increment to Random. This is necessary to prevent conflicts - for example, if user A added a new record, and user B added a new record, Increment would otherwise result in a key violation when attempting to sync. Therefore, if the app relies on a sequential series of new numbers, e.g. invoice numbers, you will need to develop a different strategy for that numbering system.

During the sync process, any conflicts will result in a hidden table being created, e.g. if a table tblOrders has sync conflicts then Access will create a tblOrders_Conflict table that holds the GUIDS for the "winning" and "losing" replica, table, and field.

It should be noted that simply deleting old records will not necessarily reduce the file size; Access maintains a system table, MSysTombstone, which retains a pointer to every deleted record. Unless you utilize Replication Manager (from the Office 2000 developer kit) or a third-party utility, the default retention period is 1000 days, so all those deleted records will be adding to the size of the file with little real benefit.

A common technique of dealing with the bloat of replication is to rebuild the master file periodically. To accomplish this, you first have all users sync & then delete their local copy, then you would need to create a new empty file, export all the Access objects into the new file, and also create new tables in the new file that do not have the replication fields & export the data into those new tables. (It is not possible to delete the replication fields from the tables because they are system fields.) The final steps would be to convert that new file to a design master & create a replica, and then distribute that replica to the database users.

SSMA, part 2

The SQL Server Migration Assistant for Access does a fine job in converting Access data into a SQL Server database. But there are a few issues.

One issue is that SSMA does not convert relationships where the PrimaryKey in Access is a compound index, i.e. if your PK uses more than one field, that relationship will not migrate so you'll have to create that manually.

Another issue is that SSMA does not convert relationships that do not enforce referential integrity (RI). Not a common scenario, but if you do have those relationships in Access then you'll need to create them manually in SQL Server.

A third issue relates to the Access application itself. Access represents boolean fields as -1 = True, and 0 = False. It is very common for Access developers to use this property in code or queries, for example, testing if SomeField=-1, rather than testing SomeField=True.

This will not work correctly after using SSMA, because SQL Server represents 1 = True. To fix this, you will need to inspect every query to find those occurrences and change SomeField=True. This should be done in Access before using SSMA, to preserve the Access code base so that the Access-data version is the same as the MSSQL-data version.

One final issue, if you want to call it that, is that SSMA retains all of the Access tables but renames them by bracketing the name with SSMA$ and $local. If you have a large amount of data, the resulting Access file will be somewhat larger after conversion because the linked tables add a small amount of bulk to the file. After the first conversion, it is useful to have the Access tables in the event that something does not convert as expected, but if you are using an iterative process - convert the data, test the app, modify the app as required, repeat the conversion - there is no option I found in SSMA to delete the original Access tables. That would be a nice addition to the product.

SQL Server Migration Assistant

"Microsoft SQL Server Migration Assistant (SSMA) for Access is a tool for migrating databases from Microsoft Access 97 through Access 2003 to Microsoft SQL Server 2005. SSMA for Access converts Access database objects to SQL Server database objects, loads those objects into SQL Server, and then migrates data from Access to SQL Server." (from the readme file)

This is an extremely useful tool, the SSMA does a very nice job of quickly upsizing an Access 97 - 2003 database to SQL Server, creating the tables and relationships & migrating the data, with just a few clicks.

It renames the existing Access files, for example tblCities becomes SSMA$tblCities$local so your original data is preserved until you're satisfied that the upsize worked as expected, and the linked tables assume the names of the original tables e.g. tblCities, omitting the "dbo_" prefix that you normally see when linking to MSSQL tables.

Note that you must have a primary key defined for every table, otherwise it only grants SELECT permissions on those un-keyed tables.

The only possible downside is the way that auto-numbering works - in Access, an Autonumber value exists as soon as you begin to create a new record, but in SQL Server an IDENTITY value does not exist until the new record is saved; if your Access app depends on that behavior, then you'll need some re-write in your coding.

Oh yes, you need to install the J# 2.0 redistributable package prior to installing SSMA but the installer alerts you and provides a quick link to download & install it.

There is also a CTP available for migrating the new Access 2007 to MSSQL 2005, and another CTP for migrating to MSSQL 2008 that also supports Access 2007.

Office 2007 creates PDF's

There is a significant new feature in Office 2007 that may encourage users to migrate to the 2007 version - the ability to create PDF files without requiring the full version of Adobe Acrobat. This is especially useful with Access 2007.

In previous Office versions, you would need to install a PDF-creation program such as Acrobat, pdf995, or other program. Those programs generally install themselves as a printer, so for an Access report you would need to create a copy of the desired report, and set the default printer to be the PDF creator. Even then, the PDF program would prompt the user to navigate to the desired drive & folder, and supply a name for the PDF to be generated.

It should be noted that with previous versions, it is possible to use the same report, but run some complex code to modify the PrintDev settings for the report to "print" to the PDF creator; this code can be found in Ken Getz's Access Developer's Handbook, among other sources. But this is no longer necessary with Office 2007.

Microsoft has published a free add-in to create either PDF or XPS format files for Office 2007. The file name is SaveAsPDFandXPS.exe and if you do a Google search for "Microsoft Save as PDF or XPS" (include the quotes) you should see the link within the first couple of search results.

You will need to validate your copy of Office, and then download & install the add-in. Once you have done that, to print an Access report as a PDF is as simple as can be:

Sub SaveAsPDF(MyReport As String, MyFile As String)
DoCmd.OutputTo acOutputReport, MyReport, acFormatPDF, MyFile
End Sub

Just supply the report name, and the desired full path & filename (which must end with ".PDF"), and the PDF will be created. Note that if the file exists, it will be over-written without warning.

Moving to Access 2007

Office 2007 has generally received a lukewarm response, but mainstream support for Office 2003 is set to end in April 2009, so remember the Boy Scout motto of "Be Prepared".

To date, I have had three customers that upgraded a single PC to 2007, while the rest of the users remained with 2003. Two have since downgraded back to 2003 due mostly to issues with Access 2007 compatibility. Some users prefer the new Ribbon interface as compared to the old-style menus and toolbars, but others, especially power users, feel lost. But I digress.

Upgrading an older-version Access database for use with 2007 is very much like what happened years ago when upgrading from the 16-bit Access 2.0 to the 32-bit versions such as Access 95/97 or 2000+. Many issues cannot be resolved using the new version - in some cases 2007 cannot open the database at all, so you will need to retain at least one PC with Access 2000-2003 in order to make the necessary changes.

There are a few things that are not supported, like using ENVIRON() expressions for default values in tables, but the most critical changes relate to the VBA environment, especially when using code-behind with forms and reports. Unlike the prior 32-bit versions, Access 2007 apparently examines all modules upon startup, so any code artifacts or compile errors can prevent opening the VB project, and thus the MDB cannot be opened. In fact, a 2000-2002 format file can compile successfully but then crash when opened using 2007. The only good news is that 2007 will tell you which module it can't load, so that gives you a starting point. But none of the VBA code will run until that module is fixed, and don't be surprised if there is more than one module that needs rework.

In a previous post, I described the workaround for that scenario - open the MDB with 2000-2003, export and delete the code module, compact & repair, then reopen with 2000-2003 and re-import the code module, compact & repair, and then it will probably work with 2007 as expected.

In the past, it has been possible to share a 2000-format MDB with users having Access 2000, 2002, and 2003 without conflict. In fact, I once had a single test PC with four versions of Access installed - 2.0, 97, 2000, and 2002 - but I would not attempt this with 2007.

The bottom line: I strongly recommmend that when moving to Access 2007, all users must have 2007 (only) installed to prevent corruption and/or data loss.

Vista and PCL6

Vista continues to exhibit incompatibilities with peripherals, not just legacy devices but brand-new printers as well.

I have an Access database that launches a mail-merge with Word (Office 2003 with SP3, running under Vista Business) and the customer is printing to a new HP color laserjet. Using the latest PCL6 driver, the first page prints correctly but then is followed by a printer error page that mentions "KERNEL ERROR" and "IllegalOperatorSequence". After that happens, every attempt to print that, or any other merge document, the doc does not print but the printer issues another printer error page. The user has to log off & log back on to Windows, after which they can print one more page before the error messages return.

By trial and error, we found that using the PS driver does indeed work properly.

In a previous post, I mentioned that a new HP black-and-white laserjet would print text OK but graphics would be jumbled with the PCL5e driver; the PCL6 was better but not perfect, so that also requires using the PS driver.

Printing from DOS

Yes, hard to believe, but there are still some DOS applications out there in production environments.

To redirect DOS printing to a network printer, open a command window

Start ~ Run ~ cmd {ENTER}

and then run this command

net use lpt1 \\servername\printername /persistent:yes

Access and mail merge

Recently I've been running into some challenges using Access 2003 (2000 format) as the data source for mail merge, when using either Word or Publisher to produce merge documents.

In either of those two programs, you select your Access file as the data source, and normally you're presented with a list of all tables and select queries in the database. But on several occasions, with different Access files and different Word and Publisher documents, not all the items were listed. Not sure why. There is no workgroup security in place; only a single user; none of the items were tagged as hidden in Access. When the first attempt failed, we tried again using a different login that had full administrative permissions; same result.

When using Publisher to generate annual contribution letters for one customer, the Access file had a totals query built with the desired data, but Publisher did not show it on the list. At first I thought it was the totals query that Publisher did not like, but numerous other ordinary select queries did not show either.

When using Word to produce attendance certificates for another customer, again Access contained the desired query but Word could not see it. This was especially perplexing because when the Access & Word merge was set up back in 2004 it ran just fine (before the files were moved to a new server).

In both cases, I had to create an Excel export file and then use that for the merge, which worked as expected.

If I discover the reason those queries did not display, I'll post my findings.

EDIT: 45 minutes later...

Found the answer here (applies to 2003, too):

Re: missing queries in data source list (Word/Access 2007)

...as it turns out, if you are using functions written in VBA (or even a few of the built-in functions) in your query, it will not display in the Word or Publisher list. This behavior was definitely altered by a service pack, hotfix or other MS patch, because they used to work. What a letdown. This means that mail-merge queries need to avoid VBA functions to guarantee they can actually be used for mail merge.

The price of progress, in pursuit of enhanced security? Or a severe boo-boo from Redmond. You decide.

Last Restore Details for SQL

In a previous entry, I posted T-SQL code that will list the last restore dates for all db's on a SQL Server. The original purpose was to verify the dates that production backups were restored to the test environment.

This enhanced script will also display the name of the MDF file and the name & location of the backup file that was used for the restore. This can serve as a double-check that the dev servers are running the latest copies from production.

CREATE PROC [dbo].[procLastRestoreDetails]
(
@database_name AS varchar(50) = NULL
)
AS

SELECT
BS.database_name,
RH.restore_date AS Last_Restored_DateTime,
RF.destination_phys_name AS Restored_To_Database_Location,
BMF.physical_device_name AS Restored_From_Backup_File
FROM
msdb.dbo.restorehistory RH
INNER JOIN msdb.dbo.restorefile RF
ON RH.restore_history_id = RF.restore_history_id
INNER JOIN msdb.dbo.backupset BS
ON RH.backup_set_id = BS.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily BMF
ON BS.media_set_id = BMF.media_set_id
WHERE
RF.destination_phys_name LIKE '%.mdf'
AND RH.backup_set_id =
(
SELECT
MAX(backup_set_id)
FROM
msdb.dbo.restorehistory
WHERE
destination_database_name = BS.database_name
)
AND
(
BS.database_name = @database_name
OR
@database_name IS NULL
)
ORDER BY
BS.database_name

Access File Size

This function will return the size of the current Access database file. This function requires a reference to the Microsoft Scripting Runtime library, usually located at:

C:\WINDOWS\system32\scrrun.dll

An Access mdb file can grow over time, especially if you have a lot of edits, empty / refill temp tables, and so forth. The purpose of this function is to get the current size, which (using other code not shown here) is then compared against some arbitrary value, and then alert the user if Compact and Repair is recommended to improve performance.


Function GetMyFileSize() As Long
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