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.