Last week I had to migrate a Access database to SQL Server
database with Access Front end, Though the actual migration of data was pretty
much straight forward using SQL Server Migration Assistant I had to do a lot of
post migration corrections and taught might me a good idea to share, I won’t go
much into how the data need to be migrated as SSMA does everything for you, the
only thing you need to remember is if are going to use MS Access as frontend
Application after the data is migrated to SQL server don’t forget to check the
Link table option to link the tables in SQL server to the Application data
source.
Right, now once the data is migrated the below are the post
migration tasks you might need to take care.
1. Most of the issues comes around
the datetime datatype as the date formats are different in SQL server and MS
Access, I usually run the following query to find all the datetime columns and
verify the format.
select a.* from sys.columns a
Join sys.objects b ON a.object_id = b.object_id
where a.user_type_id = 42 and b.type = 'U'
2. Some of the date columns might
be recognized as nvarchar due to US/UK date formats and use of date() functions
within MS Access tables, Remember dates are saved in #<date># format in
MS Access; Eg:- #01-01/2013#
I
use the following TSQL to find obvious date/Time columns, You can add few other
strings based on your application. Remember this just obvious date columns
assuming the naming conventions will have date or time string on all datetime
columns there
may some date columns with different column name which should be dealt case by
case basis.
select a.* from sys.columns a
Join sys.objects b ON a.object_id = b.object_id
where a.user_type_id <> 42 and b.type = 'U' and (a.name like '%date%' OR a.name like'%time%' OR a.name like '%dob%')
3. In the Front End MS Access
Application change all Date input box format property to yyyy-mm-dd, and delete
any format given in Input Mask property. This is because when you have the date
column for the text box input in SQL Server the input mask and default Access
format will send the date ad Varchar to SQL server and the Application will
error out with string conversion failure.
4. After the Access database was
migrated to SQL there may be issues with some of the Access reports if it’s
using Date() function in Access query due to the date format, we need to modify
all date() function to format(date(),”yyyy-mm-dd”). If any reports uses hard
coded date in their query change it using format function
Eg:-Format(#01/10/2012#,"yyyy-
5. It is always a best practice to
disable the Navigation buttons in all the forms and subforms after migration,
Any search issued using the Navigation button will kill the front-end as it
issues a very very bad designed query to SQL server using In-built Find command
in MS Access, The search created 100 MB Trace file for one of my search for a
record in 3000 rows SQL server table that because MS Access does not do row
based search instead does a search for every element in the table, say there
are 3000 rows and 10 columns the search runs for all 3000 * 10 rows to find a
single record. Instead create search customized search functionality in the form
using Filter function. Eg:- I used a button for customized search and created a
filed in Click event with the following code,
Option Compare Database
Private Sub
Command26_Click()
Dim strIntBoxname As String
Dim strFilter As String
strIntBoxname = InputBox("Enter Input", _
"Input Filter", "")
If strIntBoxname = "" Then
Me.FilterOn = False
Else
strFilter = BuildCriteria("[Col Name]", dbText, strIntBoxname)
Me.Filter = strFilter
Me.FilterOn = True
End If
End Sub
6. If you have subform in MS
Access, use dataset table object instead of SQL query as some queries may not
be recognized in SQL server, so it’s better to use object based approach. In my
case my sub form did not populate or allow edit functionality to the user.
Hope the above step helps, There were not a lot of article in
the web specific to these post migration issues.
