Data Structure Differences between Access and SQL Server



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.


Latest Articles