I had a recent opportunity to migrate a multiuser Access MDB/ACCDB-based database to one with an SQL Server backend. Sadly I knew very very little about either Access or SQL Server when I started, so along the way I came across a lot of surprises which, had I seen documented elsewhere, may have tempered my enthusiasm to begin the project.
So why in a modern civilized era would one migrate an Access database to just a SQL Server backend, instead of turning it into a nice web app? Mostly because functionally it worked fine, and involved a lot of reporting functions and Excel integration (things which Access still does very well). Some of my gung-ho-ness was due to underestimating the level of incompatibility between the two platforms. Would I still have done it like this knowing what I know now? Yes, because it solved what it needed to, and allows one to extend it in future with both a web interface or Access forms.
Before continuing you should know there are two ways to link an Access database to some external database:
- ADP: The route I chose, the one presented to you via the Upsizing Wizard, and one which is not encouraged anymore. All user data will be stored on the external database.
- Linking external tables: If you just have a few tables which you’re having performance problems on you can link them up to remote tables. This is probably the best route if you’re trying to solve a particular performance problem or integrate with other apps that depend on the same data but don’t mind keeping everything else in Access.
The most important thought to put aside is the idealistic view that just because Microsoft makes both Access and SQL Server, that the upsizing process will be entirely straight forward. It isn’t. There’s major incompatibilities, minor incompatibilities and some things that can best be classified as General Weirdness.
Migration strategy
My initial naive approach was going to be something along the lines of:
- Run the Upsizing Wizard
- Identify problem areas where things were done in such a way that it confused the Upsizing Wizard, and modify the original database so that those were done in some “best practice” way that would be compatible with both native Access format and the upsized version.
- Once the original database was suitably compatible it would be a straight upsize again to the final version.
I’ll spoil the ending: There are things which work when it’s in an MDB file, and things that work when it’s in an ADP file, but can’t be done in such a way that they work in both. And there are many, many things which cannot be made compatible enough to be done by the Upsizing Wizard.
Realistically it’s going to end up as:
- Run the Upsizing Wizard
- Clean up the mess made by the Upsizing Wizard
- Rewrite most of your queries
- Reimplement functionality which boggles the Upsizing Wizard’s tiny mind
- Migrate the user data from your live MDB database into the structure of your new SQL Server backed one (without using the Upsizing Wizard!).
The Upsizing Wizard
The Upsizing Wizard performs his latest trick
It’s not great.
Yes, it’s nice that you don’t have to copy and paste all your forms, and it does save quite a lot of work, but it also leaves so many unexpected landmines where things fail in invisible ways, thanks to the half-working state the UW leaves the database in.
Believe me you’ll be seeing a lot of silent failures. Probably one of the biggest frustrations is Access’s passive aggressive behavior. Not even a meaningless error, with an even more meaningless error code. Just… nothing.
There’s an Upsizing Report that it produces once it finishes its part of the migration, but I found this pretty useless and it doesn’t even begin to cover the real issues that you’ll be facing.
Upsizing Wizard: Table migration
The Upsizing Wizard will begin by copying over your tables and data. I had problems here with:
“Server Error 547: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint …”
Go and check for missing or incorrect IDs in columns referencing other tables. I found a number of “0″s in columns that were supposed to only have valid IDs that occurred in other tables.
“Server Error 4506: Column names in each view or function must be unique. Column name ‘…’ in view or function ‘…’ is specified more than once.”
A query is pulling out the same field twice and with the same name. I just changed the second or third reference to the same field to have an “AS FieldName2″ and “AS FieldName3″ to distinguish it from the first reference. I didn’t want to just delete it in case the number of columns/column order was important and was used elsewhere.
If you have a lookup column which allows multiple values, it’s going to have to go. You can’t just convert it back in Access, you’ll have to delete the column and recreate it.
Upsizing Wizard: Data types that will cause you grief
As mentioned, if you use Allow Multiple Selections on any of your list boxes, you’re out of luck with SQL Server. You need to get rid of these columns before you begin the upsize. Luckily for me, they weren’t critical and were (mostly) there due to the designer playing around with the feature. Otherwise you’re going to have to use a traditional intermediate one-to-many table and add whatever supporting interface code to allow people to easily use it. If other queries worked with the multi-select field, you’ll get errors like “Cannot call methods on int” until you change the ColumnName.Value back to just ColummName in those dependent queries.
Hyperlink fields aren’t directly supported at the database level, you’ll have to change a setting on each relevant control to fake it into looking like a hyperlink. (Text Field with Hyperlink set to Yes)
Attachment fields don’t work with SQL Server. I had to build a collection of simple supporting PHP pages to work around this.
Pivot tables from Access don’t upsize, but again this was something that was in the database but wasn’t being used.
After the Upsize
You’ll get the Upsizing Wizard Report which will probably give your migration a clean bill of health, so fire it up. I think when I tried it the first time, the very first form worked correctly, but after that nothing else.
If you try to run the form and get a silent falure, or when a macro/procedure tries to open it you get a “macro error 2950″, odds are the Upsizing Wizard has butchered some query that the form depends on, and you’re going to have to fix this before going any further.
Go through all the controls on the form and see what sources are referenced under Data -> RecordSet, check any subforms, and make sure that both inline queries (where the SQL is directly in the RecordSet field) and named queries can run. Make sure the controls on the form still reference valid columns. If they refer to columns via “MyTable.MyColumn” they may have to be changed to be just “MyColumn”.
After the Upsize: Fixing queries
Parts of the query just disappear
I had many of the WHERE clauses just disappear entirely in the new database, and it was easy enough to just go back to the original database and copy out the old WHERE statement and it would generally work. Don’t think I found a definitive pattern for when this did or didn’t happen.
Date/time functions
Despite superficial similarities, you’ll probably have to change a fair bit between Access and SQL Server.
Confusion between text and column names and reserved words
The person who originally created the database called one of their columns “Update”, and when the upsizer got hold of it it kept thinking it was meant to be the SQL keyword UPDATE rather than the column name. There were a number of issues with the upsize convertor having similar wobbles due to not properly understanding the query in the same way as the Jet engine would have, most annoyingly where strings inside the query were confused with column names. (e.g. FirstName = “Bryn” could end up complaining about a missing column Bryn)
ORDERing logic
In Access you can do things like ORDER BY FirstName=”Bryn”, to filter all the Bryn’s to the top and have the rest show up below (since it would resolve nicely down to a boolean value which could then be sorted). I had to change these to
ORDER BY CASE WHEN FirstName = ‘Bryn’ THEN 1 ELSE 0 END DESC
Inability to access form values from queries
This is the biggie and results in a lot of code changes. Inside many of the queries/reports, values were being pulled straight out of forms to change the output. Where you may have had something like:
SELECT * FROM People WHERE Firstname = [Forms]![MyReports]![FirstNameSearchBox];
that just isn’t going to fly with SQL Server, since all the queries are stored server side and the server can’t dynamically pull information needed back from the Access client.
Yes, you can create functions and stored procedures with parameters, but these can’t be used directly as Recordsets, making them incredibly less useful.
So how do you get around this?
After the Upsize: Getting around query parameter limitations
It’s ugly as anything and has huge potential for race conditions, but effectively you modify a stored procedure to have the required parameters fixed inside it:
TheAnswer = InputBox("Name:", "Value required")
If TheAnswer <> "" Then
Dim sExec As String
sExec = "Alter Procedure tempSPqryName as select * from qryName('" & TheAnswer & " "');"
DoCmd.RunSQL sExec
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "tempSPqryName", acViewNormal, acReadOnly
End If
So you have your one Stored Procedure which requires parameters, but can’t be called directly by many parts of Access, and each time you want to use it you modify another temporary stored procedure which calls that stored procedure, but has the parameters hard-coded inside it. You can then call what is effectively a parameterless stored procedure and Access will accept it.
Yuck.
Only use this method if you really can’t avoid it (in this case wanting to get a Datasheet view of a parametered query which seems nigh impossible otherwise). If you’re doing regular Forms/Reports you can get away with:
LinkCriteria = "[LookupID]=" & Me![LookupID]
DoCmd.OpenForm "OtherForm", acNormal, , LinkCriteria, , acDialog
to add custom conditionals onto an existing query. Make sure you pick the right choice of function/stored procedure/view for the source, because anything other than a view will either ignore the WHERE clause or pull the entire recordset over the network and perform local filtering. Keep an eye on network traffic as you’re doing the migration to make sure that absurd situations don’t develop where it needs to transfer 10MB of data from SQL Server to show a single form.
Also be careful while working on these things to avoid going insane. When the Filter is set for the Form from your VBA code, anytime you then go to debug the Form the Filter set via VBA will remain in the Form properties. If you make changes to the Form and save it, that Filter gets saved into the Form, and it no longer responds to VBA Filter property changes. Make sure you clear out the Filter property each time you save the Form.
After the upsize: Unsupported controls and OLE errors (502473)
If you try and run a form and you get the message:
“A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control”, press Ctrl-Shift-I and see if the number 502473 shows up in the corner of the window.
I had this when there were controls which weren’t supported in my ADP project (attachment field). The control was gone from the form, but it left its VBA code behind which threw Access for a wobble.
Go and make a new control on the Form, and under Properties on the Other tab change the Name of the new control to have the same name as the missing control. The form should now run, and you can delete the control.
After the upsize: Heavily JOINed forms
“Control can’t be edited; it’s bound to AutoNumber field xxxx”
If you’ve got two tables:
People
------
PeopleID
Firstname
Lastname
DivisionID
Division
--------
DivisionID
Name
Description
And a form that lets you access all the People and change the division they belong to, while also showing information on their currently selected division (e.g. Division Description) and this is done by JOINing the tables together as part of the Forms recordset, you’re going to have a problem.
In a pure Access database you can change the division the person belongs to, and the Description will immediately update on the form. When it’s on SQL Server the control will become read-only and you’ll get the “Control can’t be edited” error.
I worked around this by removing the JOIN and having the Description shown by using a DLOOKUP. e.g. =DLookUp(“Description”,”Division”,”DivisionID=” & [Forms]![MyForm]![DivisionID]). Unfortunately you do get some annoying flicker when working on the form. If this isn’t acceptable you’ll have to do something On Update that pulls the information directly out of the database and sets a Label.
After the upsize: Performance issues and searching
If you migrated to SQL Server for performance reasons with a largish database, you’ll probably be awfully disappointed the first time you run your freshly migrated database. If you’re not careful, Access will often pull over the entire table to show a single record.
I had a Form which needed to be able to step through all the records in the database, sorted, and with a possible WHERE condition attached. There’s a very limited subset of scenarios where Access will request just the necessary row from the database, and it doesn’t take much of a combination before Access starts just wanting to do all the filtering/ordering locally.
Ultimately I created a Stored Procedure which would return just the row needed based on some passed parameters. How do you do this when you can’t pass parameters to a stored procedure on a form? Via VBA: http://support.microsoft.com/kb/281998
Except where it has “SELECT * FROM Customers” you’d use your “EXEC MyStoredProcedure Paramters”
The default Ctrl-F searching in Access needs it to have all the data available locally. This is not ideal.
I used something like http://www.techonthenet.com/access/forms/search2.php to allow people to choose a field and enter a value to search, which could then be passed to my Stored Procedure. That would then append it as part of a WHERE query to limit the results returned to the form. Don’t forget to escape quotes.
I ended up with a Stored Procedure similar to:
set rowcount 1
declare @sql nvarchar(1500)
set @sql='SELECT * FROM People WHERE (PeopleID < '+cast(@param as varchar)+') AND ('+@otherstuff+') ORDER BY PeopleID DESC'
exec sp_executesql @sql
in order to let me run the dynamic SQL necessary. Yes, watch out for SQL injection.
After the upsize: Attachments
If you had attachments stored in an Access DB (>2007), these aren’t going to be transferred across to a SQL Server DB or work with the integrated Attachment field.
I created a small collection of PHP pages that would accept an ID and allow one to add/edit/remove attachments for that ID, and then put in a Hyperlink from Access to those web pages where the PHP attachment manager was running. When running as a native Access DB, the number of attachments are shown, so to emulate this I created a Stored Procedure which could return to Access the number of attachments currently stored in the database for a given ID.
Despite seeming like a large job this actually took less than a day (probably because I’m a lot faster when working mostly in PHP than in Access).
There’s then the process of dumping out all the existing data from your old Access database (this cannot be handled by either the Upsizing wizard or SQL Server’s own “Import and Export Data” tool).
I wrote a small bit of VBA that I ran inside Access to dump all the attachments and create the appropriate SQL that I could execute on SQL Server.
' Dumps out all attachments from two different columns in a table.
' Outputs the files into c:\attach, and writes SQL suitable for importing
' into the migrated SQL Server database.
Dim dbsed As DAO.Database
Dim rsted As DAO.Recordset
fnum = FreeFile()
Open "c:\attach\import.sql" For Output As fnum
currentid = 1
megaquery = ""
Set dbsed = CurrentDb
Set rsted = dbsed.OpenRecordset("OldAttachment")
' Two different columns of attachments on a single database table
' I store both columns of attachments in the new database but given them
' different IDs (1 or 2) to identify which column they originally came from.
While Not rsted.EOF
Set myCurrentRow = rsted.Fields("AttachmentColumn1").Value
While Not myCurrentRow.EOF
myCurrentRow.Fields("FileData").SaveToFile "c:\attach\" + Trim(Str(currentid))
Print #1, "insert into attachment (AttachmentID,InternalID,AttachmentTypeID,UserID,Filename) VALUES (" + Str(currentid) + "," + Str(rsted.Fields("OldAttachmentID").Value) + ",1,0,'" + Replace(myCurrentRow.Fields("FileName").Value, "'", "''") + "');"
currentid = currentid + 1
myCurrentRow.MoveNext
Wend
Set myCurrentRow = rsted.Fields("AttachmentColumn2").Value
While Not myCurrentRow.EOF
myCurrentRow.Fields("FileData").SaveToFile "c:\attach\" + Trim(Str(currentid))
Print #1, "insert into attachment (AttachmentID,InternalID,AttachmentTypeID,UserID,Filename) VALUES (" + Str(currentid) + "," + Str(rsted.Fields("OldAttachmentID").Value) + ",2,0,'" + Replace(myCurrentRow.Fields("FileName").Value, "'", "''") + "');"
currentid = currentid + 1
myCurrentRow.MoveNext
Wend
rsted.MoveNext
Wend
rsted.Close
Close #fnum
Copying the latest data across
This is fairly straightforward. First clean out all data from tables in SQL Server, to be run from SQL Server Management Studio within your database:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
Now go into the SQL Server “Import and Export Data” tool.
- Choose “Microsoft Office 12.0 Access Database Engine OLE DB Provider” as the Data Source.
- Go into Properties for the Data Source and put the path to your .accdb in Data Source.
- Go Next onto the Destination and put in the details to connect to your SQL Server database.
- Choose “Copy data from one or more tables or views”
- Highlight the tables (not the views)
- Put a tick next to one of them and they’ll all get ticked, then choose Edit Mappings.
- Enable identity insert for everything. Don’t enable “delete rows in destination table” because that causes problems with constraints.
- Go into individual tables and disable fields which should not be migrated, e.g. attachment fields, multi lookup fields, other fields not supported via SQL Server from Access.
- Perform the migration.
Then back to SQL Server Management Studio to reenable constaint checking.
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO