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.CloseExecuting 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.
