Data Structure Differences between Access and SQL Server

June 29, 2000

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.

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.

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.

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:

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:

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.