Access 2000 brings a lot of new cool features when comes the migration time, started with the new Migration Wizard. If this tool is great for basic applications or for just migrating data, it is limited when you need to migrate queries or optimize your DAO/ADO code.
Before going into performance issues or optimizing tips and tricks, you should be aware of all the architectural differences between Access and SQL Server. To start, this article focuses on the data structure differences between Access 97/2000 and SQL Server 7, from a table and query point-of-view.
Structural differences
Access 2000 introduces two types of databases: the familiar .mdb and the new project .adp. .adp is designed to work with MSDE or SQL Server. If you need to migrate your Access database, you have an .mdb file (or many .mdb files).
Access 97/2000 .mdb files contain 6 types of objects : tables, queries, forms, reports, macros and modules. Access 2000 .mdb files contain in addition pages. A SQL Server database contains tables, views, stored procedures, defaults, rules and user defined data types.
Access | SQL Server |
Table | Table |
Index | Index |
Query | View or Stored Procedure |
Form, Report | N/A |
Macro, Module | Stored Procedure |
N/A | Trigger |
N/A | Default, Rules |
The previous table shows the basic differences between Access and SQL Server. Triggers, defaults and rules do not really have equivalent in the Access environment, as Forms and Reports for Access compared to SQL Server.
I think the main difference you should remember here is Access is a GUI development tool with an embedded database engine (Jet), when SQL Server is database engine, with no GUI development tool. So, in a combination of Access-SQL, Access will play the role of the User Interface and SQL Server of the database Engine.
If you used the Access Database Splitter and the Linked Table Manager Wizards, you probably have two databases, one containing the tables and one containing queries, forms, reports, macros, modules and linked tables. With Access-SQL Server, the architecture can be identical, the tables are in a SQL Server database and all the other objects are in an Access application. Nevertheless, in an Access project (.adp), only forms, reports, pages, macros and modules are in the Access files, tables and queries (that is views and stored procedures) are in the SQL Server database; no other queries exist in the Access file.
Tables
When you migrate with the Migration Wizard Access tables to SQL Server tables, some modifications occur. To understand them, you should know the table differences between both environments. The following table describes every difference:
Structures | Access | SQL Server |
Field name | From 1 to 64 characters | From 1 to 128 characters |
Characters . ! ` [ and ] are not allowed | All Unicode characters are allowed | |
Data Types | Text | Varchar, nvarchar |
Memo | Text, ntext | |
Byte | Tinyint | |
Integer | Smallint | |
Long Integer | Int | |
Simple | Real | |
Double | Float | |
Replication ID | Varbinary, uniqueidentifier | |
Decimal | Numeric, decimal | |
Date/Time | Datetime, smalldatetime | |
Money | Money | |
AutoNumber | Int (Identity) | |
Yes/No | Bit | |
OLE Object | Image | |
Hyperlink | Text, ntext | |
Description | Standard in the table structure | With Microsoft Repository |
Field properties | Field Size | Length |
Format | CHECK constraint, RULE | |
Input Mask | N/A | |
Caption | N/A | |
Default value | DEFAULT Constraint, DEFAULT | |
Validation Rule | CHECK constraint, RULE | |
Validation Text | N/A | |
Required | Allow Nulls | |
Allow Zero Length | CHECK constraint, RULE | |
Indexed | Creating an index | |
Unicode Compression | N/A | |
Decimal places | N/A | |
Precision | Precision | |
Scale | Scale | |
N/A | Identity Seed | |
N/A | Identity Increment | |
Relations | Apply Referential Integrity | FOREIGN KEY constraint |
Cascading update | Trigger (or SQL 2000) | |
Cascading delete | Trigger (or SQL 2000) | |
Graphical relationships | Database diagram |
Queries
Though the Access 2000 Migration Wizards try to transform queries into Views or Stored procedures if you migrate to an Access project .adp file, the former wizard did not even touched the queries. But it is an important part of the optimization process. The following table describes quickly the corresponding SQL Server object to any type of Access query:
Access | SQL Server |
Select query without parameter | View or stored procedure |
Select query with parameter | Stored procedure |
Crosstable query | View |
Update query | Stored procedure |
Insert query | Stored procedure |
Delete query | Stored procedure |
Create table query | Stored procedure |
Union query | View |
SQL Pass-through query | View or Stored procedure |
DDL query | Stored procedure |
One of the most complicated problems is due to built-in Access or VBA function used in queries. A VBA function cannot be used in SQL Server queries. If the VBA function is used in the Select list, you can keep the query in Access. If the VBA function is used in the Where clause, you’ll probably have to transform you VBA function into Transact-SQL. We’ll see in following articles how to transform VBA code into Transact-SQL quite quickly.
