Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 29, 2000

Data Structure Differences between Access and SQL Server

By Marc Israel


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.




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date