Migrating Access Jet Data to SQL Server

Introduction

I recently started a contract to migrate a replicated Access 2007 application to full Client/Server mode using Access 2013 and SQL Server 2012. This tip (which will be updated as other things are discovered) is my list of very hard won tips and GOTCHAs for such a migration.

Using the Information

Many of these things are known about, some even mentioned specifically in the Microsoft Knowledge Base, when you know where to look! From my fairly extensive (and very time-consuming research), it seems that in one or two cases, whilst these things are known, the causes and hence the fixes are not, and solutions are often arrived at by chance (the TIMESTAMP tip is a good example of this). As it took me so much time to debug the application, find what was happening and then look for possible causes and solutions on-line, I thought it might be good to set them down here in the hope that other developers might find it useful.

Access Data to SQL Conversion: Tips and GOTCHAS!

1. SQL Server does not have a Boolean field - Access copes with this in linked tables by mapping a field (BIT?) containing 0 or -1 to False or True. When creating a query to be run on SQL Server, any tests for True or False will fail; test for not 0 (1 in BIT) or 0 instead. Don't forget that BIT can also be Null!
2. Recordsets based on ODBC linked tables must have a unique index to be updateable! A primary key is sufficient.
3. To use pass-through queries, each machine must have the same ODBC connection string. BUT you can't use passthrough queries as record sources for subforms!
4. Creating primary keys on tables in SQL Management Server requires that you allow changes that cause tables to be recreated. (See tools/options/designers). Primary keys cannot contain NULL entries.
5. Updating entries on bound forms from within VBA can cause an apparent conflict with two users trying to update at the same time. This seems to be an issue when there are duplicate results in a query, but see also 11 below.
6. GUIDs are tricky to handle in forms and VBA - - for example, to successfully assign an Address GUID, I had to store it directly in the recordset field - assigning it to the control attached to that field always seems to fail with an incompatible type, although the control displays the underlying GUID perfectly!
7. It appears that autonumber/GUID fields in SQL Server only update on COMMIT, rather than when first created as in Access. That has made using a bound form to add new records very tricky indeed! (Not sure this is entirely true, but difficult to test.) In the end, I used a Stored Procedure and an associated Access pass-through query to add records and return the allocated GUID to the application (as a Recordset).
8. ACCESS VBA: Beware of <object>.Recordset.Clone - this is a useful way of altering records in a loaded recordset without disturbing the original, which may be bound to an object, e.g., a Form. However, the clone is made only after the original is first loaded or on subsequent refreshes - so a new record added to the main recordset doesn't appear in the clone until after a SQL Server COMMIT. Even more importantly, the .Bookmark property isn't cloned, so expecting the clone recordset to be positioned to the same (current) record as the main recordset causes no end of problems.
9. NOTE: There is a serious issue with queries that contain normalising tables. When attempting to add a new record directly in the query, or a form bound to it, all works as expected and the new GUIDs and primary key IDENTITY values update correctly. However, when doing it from a subform bound to the query, the updates cause one or more "Field cannot be updated" dialogs to appear, although clearing these allows the update to proceed. As this error isn't trappable, there is no simple way around this. The cause appears to be the different order in which linked table updates occur when talking to SQL Server rather than using local tables: the initial write of the new record fails to update the key fields because SQL Server only creates new IDENTITY values on COMMIT, unlike Access itself.

   After many hours of research and experimentation, the only way around this seems to be to use temporary local tables in Access, or to use a Stored Procedure to generate the new record and return the GUID to Access - which is what I've done.

10. Not really a SQL Gotcha as such, but serves to illustrate why 9 above occurs: The order of execution of queries in a batch may be optimised by SQL Server in such a way as to cause things to execute in a different order to which they are declared (SQL is a declarative language, unlike - say - Visual BASIC). Thus apparent dependencies may not be satisfied. For example:
    SQL

    ALTER TABLE tblNotes 
        ALTER COLUMN NoteID UNIQUEIDENTIFIER NOT NULL;
    ALTER TABLE tblNotes ADD 
        CONSTRAINT PK_NoteID PRIMARY KEY (NoteID);

    may fail because the SQL optimiser decides that the CONSTRAINT should be applied before the column has been set to disable NULL entries (which are not allowed in Primary keys).

    SQL Server Management Studio provides a way around this - adding the GO command (not part of SQL!) forces the Server to treat all statements up to that point that have not already been executed as a new batch. Liberal sprinklings of GO will force execution in the order the statements have been written, so the above becomes:

    SQL

    ALTER TABLE tblNotes
        ALTER COLUMN NoteID UNIQUEIDENTIFIER NOT NULL;
    GO
    ALTER TABLE tblNotes ADD
        CONSTRAINT PK_NoteID PRIMARY KEY (NoteID);
    GO

11. A known GOTCHA this time (See KB278696). Access has a problem with BIT fields that contain NULLS -it converts them to 0 when loading and displaying them in linked tables or queries, but cannot then update the records if anything changes. This is because ACCESS checks all fields to see if a change needs committing, but sees the NULL BIT fields as having been changed (from 0) as T-SQL doesn't do the reverse conversion. So, if you import a table (or add columns to one) which results in BIT fields holding NULLS, ACCESS will return a WRITE CONFLICT error when you attempt to update these records, insisting that another user has already updated them!

    There are three solutions:

    1. Run a T-SQL QUERY on the table to replace all NULL BIT fields with 0 (False). You must also set a DEFAULT value for the fields to avoid new records acquiring the problem.
    2. Or (completely counter-intuitive!) add a TIMESTAMP column to the table. Replacing the NULLS is then not necessary. This solves the problem because ACCESS will now use only this field to test whether a record has been updated and needs re-writing. (This can also solve problems when updating floating point number columns as JET and T-SQL do not represent all numbers in identical fashions.)
    3. Change the BIT fields to INT or TINYINT.

    Whilst b. is a solution I've seen mentioned elsewhere to both this and other ACCESS record update problems, I'm against it as it is adding yet more redundant data to the tables. For large tables, this also increases storage requirements, slows down access to data across networks, etc., so I'm going to avoid it if possible. Solution c. I dislike because the column type then misrepresents the use case, and would allow more than two values unless strictly controlled.

    However there is another solution in the case of amended table structures, and that is to not allow NULL in any added BIT columns, thus forcing T-SQL to assign a default value to each record for the new column.

12. It appears that overwriting a replicated SQL database (even from a backup of a copy of itself and with 'preserve replication settings' ticked) breaks the (push) replication settings. Does this have implications for restoring a replicated database from its own backups? Hmm, more later!

13. To amend the structure of a linked table in Access, you have to alter the structure in SQL Server and then update the linked table. Default values must be set on the server too if new records are to be created via queries. I have added a bit of code, called when the application is first loaded, to refresh all linked tables to ensure they conform to the current schema: (Note the need to set the DSN, etc. It is possible to use DSN-less connections too...)
    VB.NET

    Public Sub relinkTables(dsn As String, dbs As String)
    Dim tdf As DAO.TableDef
    
    For Each tdf In CurrentDb.TableDefs ' check if table is a linked table
      If Len(tdf.Connect) > 0 Then
        tdf.Connect = "ODBC;DSN=" & dsn & ";_
        Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=" & dbs
        tdf.RefreshLink
      End If
    Next
    
    End Sub

14. A variation on the problems related to items 7 and 9 above: If you have a bound sub-form (even just to a table) and want to prevent the user simply typing in the offered blank new record, you can turn off the "Allow Additions" property. However, this also means that you cannot - directly - add a record using VBA by manipulating the recordset associated with the form. Instead you must use a separate recordset, Stored Procedure, etc. to add a suitable blank/prefilled new record and then refresh the sub-form.

    If you use VBA to add a record to a linked table with an IDENTITY field or GUID, then to make the new record current in the recordset ("rs" in this example) used you need to add the following commands after using .AddNew and amending any fields:

    VB.NET

    rs.Update
    rs.Move 0,rs.lastModified 

    If the table or a query built with it is bound to a form and you are attempting to add a new record whilst viewing the form, then...

    VB.NET

    Me.Requery

    ...is also required to force the form to update.

15. It isn't possible to set fields as 'Required' in linked tables, so you must either use some VBA to check the contents of form controls, or set validation rules for them to force users to make entries.

16. Another item related to 7, 11b (and slightly to 14): It is possible to use VBA to add records to linked tables that contain GUID fields, providing a) all fields that are not allowed to be NULL have defaults set, b) you write something to one of the fields - presumably this is another manifestation of the TIMESTAMP gotcha mentioned in 11b - something has to have changed since the new record was created for Access to force a COMMIT. Simply doing an

    VB.NET

    .AddNew
    .Update

    on a linked table recordset seems to generate error 3146 - "ODBC call failed";.

    This does actually mirror the behaviour when editing a table in 'spreadsheet' mode in Access - if you click in the new record area, the focus moves there, but nothing is written unless you edit at least one field. I'd guess this is one of those things that you should just know - all the MSDN examples I've looked at always write something to a new record before updating it - the idea that you might want a new one for later updating, simply so that you can get the GUID assigned doesn't seem to occur anywhere.

    (Interestingly enough - in Access the new row created by manual editing in that way appears on screen to duplicate existing GUIDs and data from a previous record, but examining the table with SSMS shows that a new, unique record was in fact created...)

    However, there is a further severe GOTCHA here with linked tables: the above process also gets hit by a known bug in Access (which Microsoft has acknowledged but has no schedule for a fix) in which an attempt to position to the new record fails because Access writes the SQL query generated by .Move 0,.Lastmodified incorrectly. If there are any NULL inserted fields, the query to retrieve the record is written with "<field> = NULL"</field> instead of "<field> IS NULL"</field>, and hence no record is returned, and the recordset's current record is marked "Record is Deleted".

    The only solution to this appears to be to add records using Stored Procedures as mentioned above etc

17. If you are using GUIDs to guarantee referential integrity etc, be aware that the Recordset.FindFirst method will not work on GUID fields.

18. Not a SQL related matter, but worth noting that a form's IsLoaded property is not set when that form is a sub-form of another, because such sub-forms are not, apparently, part of the Forms collection of loaded forms! 

19. Transferring a SQL Express database between machines that are not connected: Take a backup on the originating machine. Transfer the file to the destination machine. Use the SSMS to connect to the local database collection, right click on "Databases" and select "Restore Database". In the dialog that appears select "Device" as the source and then find the .BAK file and add it to the list. If the .bak file is good, then the dialog should be filled with the details of the database you are about to restore...

20. To find empty (or used)tables in a SQL database, you need to query the Database related dynamic management views in Transact-SQL. sys.dm_db_partition_stats can be queried to locate, for example, all non system tables that have entries in them...

USE <database>
GO 

WITH TableRows AS   
(
SELECT SUM(row_count) AS [RowCount], OBJECT_NAME(OBJECT_ID) AS TableName
FROM sys.dm_db_partition_stats
WHERE LEFT(OBJECT_NAME(OBJECT_ID),3) <> 'sys' AND (index_id = 0 OR index_id = 1)
GROUP BY OBJECT_ID
)
SELECT * FROM TableRows WHERE [RowCount] > 0;
GO

21. When using ODBC calls to talk to SQL Server, you may get an error such as "ODBC Call Failed". This tells you nothing useful, because the error reported by Access is always the last in a chain. To see what the original error was, you need to inspect the DAO Errors table either in code or in the immediate window whilst debugging:

? Errors(0).Description

should tell you something more useful.

22. A half a gotcha this time 8):

    When doing wild card searches within data, the Jet engine recognises "*" as a wild card, whereas SQL uses "%"

23. AutoNumber columns (or their SQL equivalents IDENTITY) are not allowed to be added to SQL replicated data tables, as their uniqueness cannot be guaranteed if subscribers are creating records. It's not clear what happens if a table with an existing IDENTITY column is then published…

    If you require random (unique?) autonumbers that are not GUIDs, then define the column as INT and set the default value to be CHECKSUM(NEWID()).

24. Using ACCESS queries to build large recordsets to be processed one record at a time is extremely slow if there are any built-in functions or calculated fields, as the query appears to fetch one record at a time from the server. Convert the query to a SQL view, and the recordset is returned in one hit. Limit the scope with WHERE and the perfomance boost is really pronounced. VIEWs were not intended as performance enhancers, but by moving the processing to the server, they often have this effect.

History  

• 15th November 2013. Three more useful discoveries (22-24).
• 22nd October 2013. Some new tips relating to data transfer and ODBC errors. A couple of very minor corrections.
• 4^th October 2013. A few more discoveries added; an important one regarding creationg of new records.
• 18^th September, 2013: Added a further tip relating to linked tables and bound forms
• 6^th September, 2013: Added another tip
• 2^nd September, 2013: Revised; added another issue regarding replacing databases
• 31^st August, 2013: First edition; no doubt more will follow!