Migration is the process of moving some or all database objects from an Access Database (.mdb) to a new or existing SQL Server database or new Access Project (.adp).  A move from Access to SQL Server is generally considered for performance, security and stability.  With the increased volume of databases and with a large number of database users, one may need to convert the Access database to a client-server environment of SQL Server.  Upsizing the access database to SQL server will solve this problem and provide a better application for a large number of users with less network traffic.

Creating a two-tier application

In this case the server based tables are linked with the database tables.  The other existing database objects (queries, forms, reports, modules and macros) are used as before with no modifications.  This creates a two-tier application in which the SQL server stores only the data and copies of the interface are stored in Access.  In this method code modification is not needed in the application because the application is still using the Jet Database Engine, but the queries are still processed locally and a large amount of network traffic is generated.  Therefore, it is not a good method to follow.

Upsizing data without making any modifications to the Access Application

In this method the application specific logic and the user interfaces are not migrated.  Only data is migrated.  You have to rewrite the whole application code and recreate the forms and reports. It is a very tedious method.

Creating a client – server application

In this method an Access Project (.adp) is created which is connected to the SQL Server database, creating a client-server application.  All tables and data definition objects, such as views and stored procedures, are stored on the server.  However, the forms, reports, etc. are stored at the client side.  Minimum code modifications are needed in this approach.  In this case, most data processing is done at the server minimizing network traffic.  We followed this approach.        

The Upsizing Wizard upsizes an Access database to a new or existing SQL Server database or new access project by migrating data and data definitions and then moving database objects to the new database structure.  The upsizing wizard provides different options for migrating the table attributes, such as indexes, validation rules, defaults and table relationships.  One should always choose the DRI (Declared Referential Integrity) option under the table relationships to enforce referential integrity.  The upsizing wizard adds a “CS” suffix to the current access database name and stores this ADP project in the same location as the existing Access Database.

After migrating the tables and queries, the upsizing wizard creates a report that provides a detailed description of all the objects created and any errors encountered during the process.  It provides information about Database details (size, device names etc.), upsizing parameters (table attributes chosen for upsizing, table information including column names, data types, indexes etc.), table relationships and any errors encountered (tables not created, inadequate permissions, queries not upsized, validation rules skipped etc.).

Access and SQL Server vary in the way data is stored, the data types available and the storage capabilities.  All the tables that are upsized should be manually inspected.  So after upsizing the database through the Upsizing Wizard, we compared the tables in the access database and SQL Server according to the Upsizing Wizard Report and then made necessary changes to the SQL tables.  All the column names, data types and the index property were checked and modified if required.  All access database data types were converted to their equivalent in SQL.

ACCESS                                              SQL

Number                                      Int/TinyInt/SmallInt

Yes/No                                                Bit

Text                                          Char/Nchar/Varchar  

Currency                                    Money

Date/Time                                  Datetime/SmallDatetime

Memo                                         Text/Ntext

OLE Object                                 Binary/Image

AutoNumber/AutoIncrement           Identity

Only simple SELECT queries were converted to views.  SQL statements in record source, control source or row source of the forms, reports and controls were not converted.  The Modules and Macros also remained the same.

Changes Made After Upsizing the Database

Some features of access are not supported by SQL server, so one has to manually analyze and rectify any such issues during and after upsizing process.

1. The Forms, Reports, Modules and Macros are imported from the Access .mdb file.  From the File ->Get External Data ->Import options all forms, reports, modules and macros were imported.

2. Then the queries were changed.  Access queries can be divided into 3 types.

Views: These are the simple SELECT statements with no input parameters or return values.  These are used as virtual tables.

Stored Procedures: These are the queries that accept input parameters.  INSERT, DELETE and UPDATE statements were also converted to SQL Stored procedures.

User-defined Functions: These are the queries that accept some input parameters and return a single value or a table.  These are the subroutines that are used repeatedly in the application.

3. To make the .adp file access data from the SQL Server we changed the connection string and converted the DAO code to its ADO equivalent.  The connection string was made pointed to the SQL Server database.

Data Access Object (DAO)

DAO is a data access technology primarily designed to use the Jet databases like Microsoft Access or dBase, FoxPro, etc.

ActiveX Data Objects (ADO)

ADO uses the OLE DB technology to gain access to any type of data source.  The ADO object model is based on three types of objects namely Connection, Command and Record set. The connection object holds data source connection information like data source name, its location, the user id and password, the name of the OLE DB provider, etc.  The command object is used to execute SQL commands, queries and stored procedures.  The Record set object holds the results returned by the queries and used for adding records to the tables and updating the database.  In ADO, "CurrentProject. Connection" determines the connection string.

The connection string is:

Provider=Microsoft.Access.OLEDB.10.0; Persist Security
 Info=False; Data Source=192.168.10.3; User ID=sa; Initial Catalog= Database
 Name; Data Provider=SQLOLEDB.1

Opening and Adding Value to a Record Set

Let us now examine how to open and add value to a recordset using DAO and ADO.

Listing 1 - DAO    

Dim db as Database
Dim rs as DAO.Recordset
Set db = CurrentDb( )
Set rs = db.OpenRecordset(“tblPeople”)
rs.Edit
rs(“TextFieldName”) = “New Value”
rs.Update

Listing 2 - ADO

Dim rs As New ADODB.Recordset
rs.Open "tblPeople", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic           
rs.AddNew
rs("TextFieldName ") = NewValue
rs.Update
rs.Close

Executing a Query

Listing 3 - DAO

db.Execute "SELECT ……………………………."

Listing 4 - ADO

Dim CommandText As String
CommandText = "SELECT……………………….."
rs.Open CommandText, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly, adCmdText   

These changes were made throughout the code in the application.

4. The record source, control source or the row source of all the forms and reports remained unchanged after upsizing.  So we had to manually change the record source properties of all the forms and reports used.  The record source of all the Forms and Reports were modified to make them compatible with the SQL.  Listed below are some keywords that need to be changed for making them compatible with SQL Server.

ACCESS                                              SQL

TRIM                                         LTRIM/RTRIM

DISTINCTROW                             DISTINCT

IIF (expr)                                   CASE…WHEN

TRUE/FALSE                                1/0

& (for string concatenation)           +

FORMAT ( )                                 CONVERT ( )

CINT                                          CAST

UCASE                                       UPPER

LCASE                                        LOWER

ORDER BY in Views                       Not Supported

NOW ( )                                     GETDATE ( )

In Access the ISNULL () function returns 1 if the supplied parameter is NULL and 0 if it is not.  In SQL Server there are two parameters and the function works more like a CASE statement.

5. When a Form’s Record Source takes some parameters it has to be changed to a Stored Procedure and the parameters are to be passed through the input parameter property of the form.

Record Source: Stored Procedure Name

Input Parameter: [Forms]! [Form Name]! [Fieldname]

The Sub forms record sources that took the input value from the Main Form are converted to stored procedures so that they will be populated according to the value in the main form.

Complex queries

The cross tab queries and other complex queries were not converted to their equivalent in SQL.  We had to manually convert those complex queries to make them compatible with SQL Server. Those were converted to views, stored procedures or functions.

Write-Conflict Error

“This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.”

Parent!LastUpdateBy = CurrentUser( ) and Parent!LastUpdateDate = Date.  These two lines of code were not supported in Access ADP and we found this to be the reason for the Write Conflict error on the sub forms in our project.  We removed these two lines of code to avoid a write conflict error.  However, we had to add LastUpdatedBy = CurrentUser () and LastUpdateDate = Date only in the Save Button Function to update the forms at the end.  In this case, the main form did not get updated immediately after any modifications were made to the sub forms value.  It only got updated after pressing the “Save Button.”  The cause of write conflict error may be different for other ADP applications.  It may be due to not assigning a default value to a bit data type in SQL or due to some primary key problems.

The Write Conflict error may appear when two forms that update the same data source are opened at the same time.  The first form puts an edit lock on the record(s) and then the second form changes the record(s) and saves the changes.  When the first form tries to close and write back to the table, the changes that were made by the second form are detected.  This causes the error to be returned.

Docmd.TransferText and Docmd.TransferSpreadSheet

These commands are used to export records to a text file or an excel sheet with proper format and are not supported by Access ADP.

For the Docmd.TransferText Command we had to create a file system object and use that to write the whole record into a text file.  The record was fetched by executing a view or stored procedure.  All the columns of each record were written into the text file using a loop.  The CreateTextFile and Writeline function of the file system object were used to write the records into a specific text file in a specified location.

Listing 5

In Access: 
DoCmd.TransferText acExportDelim, , "QueryName", sPath & "\FileName.txt", True, , 65001
 
Modified Code:
Dim CommandText As String
Dim rs As New ADODB.Recordset
Dim strMsg As String
Dim strText As String
Dim strFileText As String
Dim strHeaderText As String
Dim fs
Dim a
 
CommandText = "Exec QueryName '" & parameter& "'"
rs.Open CommandText, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
  
If rs.RecordCount = 0 Then
  DoCmd.Hourglass False
  MsgBox "No Records to Export!"
  Exit Sub
  End If
 
strHeaderText = strHeaderText & """FieldName1""" & ","
strHeaderText = strHeaderText & """FieldName2"""
strFileText = strFileText & strHeaderText & vbCrLf
 
rs1.MoveFirst
 Do While Not rs.EOF
    strText = strText & """" & rs1 ("FieldName1") & """" & ","
    strText = strText & """" & rs1("FieldName2") & """"
    strFileText = strFileText & strText & vbCrLf
    strText = ""
    rs.MoveNext
 Loop
     
     Set fs = CreateObject("Scripting.FileSystemObject")
     Set a = fs.CreateTextFile(sPath & "\FileName.txt", True)
     a.WriteLine (strFileText)
     a.Close
     rs.Close
     Set rs = Nothing
Docmd.TransferSpreadSheet:
For this command also we changed the syntax accordingly.
 
In Access: 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QueryName", sFilename, True
 
Modified Code:
DoCmd.OutputTo acOutputServerView, _
 “ViewName ", acFormatXLS, sFilename, False
OR
DoCmd.OutputTo acOutputStoredProcedure, _
 "StoredProcedureName ParameterName", acFormatXLS, sFilename, False

Docmd.OpenForm Taking Parameter

Docmd.OpenForm taking input parameters was not supported in access ADP.  We had to convert the record source of the form to a view.  Only then did this command work.

DoCmd.OpenForm FormName, , , Condition

Print Functionality

In the print functionality we faced the problem of Header Record Delimiter Error.  For that, a header file with all the columns with appropriate delimiters had to be written.  We added the header file with “,” as field delimiter and “enter” as record delimiter and that solved our problem.

Updatable View

If the record source of a form is not an updatable view then one cannot edit any field of the form.  And the status bar always shows the error message “The Recordset is Not Updatable.”  Tables without unique index will be upsized, but they will be read-only in SQL Server.   We added a unique index to each of the tables used and made the required view updatable.  The view should not contain the following things to be updatable.

- A Set Operator (Union, Intersect, Minus, Union All)

- Distinct Operator

- Aggregate Functions (Sum, Avg, Count, etc.)

- A Group By Clause

- The select list with components other than column specification or more than one specification of the same column.

Error While Inserting Records to a Form

Sometimes when adding a record to an ADP form an error message appears:

“The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source.”

For this we have to set the ResyncCommand Property of the Form in the design view to a SQL statement.

For Example: Resync Command: Select * From tblName where FieldName = ?

If the Rowsource contains a stored procedure with multiple tables joined together then the Resync Command Property of the form should be set to an SQL statement that selects the same fields as the stored procedure and parameterize the primary key of the table that is designated as the Unique Table.

In the MDB application, after changing anything in the sub forms, the main form used to be updated immediately.  However, in our ADP application we had to press the “save button” to update the main form after any modifications were made to any of the sub forms due to the “write conflict error” problem.

An ADP is a specific Access file type that stores user objects such as forms, reports, macros, and Visual Basic for Applications (VBA) code modules.  All the other objects—the tables, stored procedures, views, and so on—are stored on the database server.  It has its own advantages and disadvantages.  Here the server is responsible for all query processing and fewer resources are needed at the client side.  It can handle a large number of database users.  On the other hand, one cannot create local tables or local queries.  It is tied to one database engine and cannot be linked to multiple data sources.

We had a very good experience working on this project.  We learned a lot about the differences between Access and SQL Server database.  Not many code modifications were needed.  Only DAO to ADO code conversion was applied throughout the application.  Creating an ADP file was the best option we had for migrating the Access database to SQL server.