Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Upsizing the Access Database into the SQL Server

By Dinesh Asanka,

Introduction

    Most of the small and medium scale organizations are used to start their operations with Access 2000 due to many reasons. Main influence behind this is the licensing fees. Other than the above use, more developers are using Access as a prototype. Nevertheless, after a while there are many complains from the access users about the corruption, data losses, etc etc. Therefore, there are many reasons needed to be consider before selecting a database system for your organization or for your duties.

Performance : Performance is the major factor need to be considered when it comes to choosing a database. Obviously, SQL Server is far ahead on this as it is a RDBMS not like a file flat as Access. SQL Server is designed for multiple users in a network environment, whereas Access Jet Database was never designed for multiple users. 

Robustness : If you are a heavy access user you must know how many times that you need to repair access databases. I have experiences of loosing data after repairing the access database. There is no such pains in database systems like SQL Server.

Backup Management: As SQL Server offering a true database server environment it has integrated backup, maintenance and management features like scheduling that access lacks.

Security : Security is a major concern in the Access, where as in SQL Server has many facilities with users and user groups. In SQL Server even it has the ability to integrate windows authentication into it.

More importantly access will give tremendous headaches when the mdb file grows into more than 2 GB.

Above are just the few of the features the SQL Server has over the Access( I am sure many of the DBAs and gurus would know these)

Data Types Access vs. SQL Server

When upsizing the database it is very essential to know equivalent data types.

Access

SQL Server

Yes/No bit
Currency money, small money
Date/Time Datetime, smalldatetime
Double float
ReplicationID uniqueidentifier
Long Integer int
AutoNumber identity
OLE Object image
Memo text
Single real
Integer smallint
Byte tinyint

Methods of Upsizing

from the available methods of upsizing, following two methods are most commonly and more frequently used.

1. DTS Import/Export Wizard.

        Above SQL Server wizard is very much used due to its most features. In DTS, developers have found that it is much flexible due to following reasons.

  •     Ability to transfer selected data into to the SQL Server.
  •     Ability to change designation table's format
  •     Ability to schedule the upsizing task so that developers do not have to configure and execute this again and again

Dispute the its capabilities the wizard won't automatically copy primary keys from the data source to the SQL Server destination tables. You have set those primary keys manually, or you can simply set them in the finished destination tables by editing SQL Script at the Column Mapping and Transforming screen of the DTS wizard.

There are a few other things that the DTS wizard will not do for you.

  •  The wizard won't maintain relationships which are needed to be recreated in SQL Server.
  •  The wizard won't enforce referential integrity rules set in the data source.
  • The wizard won't create auto identity property in the SQL Server which is supposed to be equivalent to Access AutoNumber

2. Access Upsizing Wizard (AUW)

    Above method may not be as quite frequent as the DTS but another method of achieving our objectives. This wizard operates inside Access to copy data to SQL Server. Unlike DTS which is common tool for the data importing and exporting , AUW  is a customized tool to upsize Access into SQL Server.

 Even though, there are few drawbacks with compared to DTS there are few advantages also than that to the DTS. Most important feature is, it does have an ability of transferring relationships , primary keys and auto identity properties to the SQL Server. In addition, AUW also creates an link to the SQL Server in Access database where it duplicates the data which are entered in the SQL Server. Following User interface is provided at the Access to link the SQL Server data in to Access

Main drawback that this AUW has is , that it does not have a facility to select only the needed data to be transferred to the SQL Server. In addition, it does not have a feature change the SQL Server create table script.

Conclusion

When you are given with an upsizing task, use either the Access upsizing wizard or SQL Server's DTS. Any of the methods will provide you with the specified job. However, you may find the DTS wizard less confusing and more dependable than to its competitor Access Upsize Wizard. But it is a decision which will be made by you for your benefit and easiness.

Total article views: 16671 | Views in the last 30 days: 11
 
Related Articles
ARTICLE

Access to SQL Server: The Upsizing Wizard

SQL Server 2000 is the basis for the new Access database storage. However there are still many cases...

FORUM

Access Upsizing Wizard Problems

My client uses an Access front end currently.  He would like to link these tables to SQL Server beca...

FORUM

Using SQL Server Express from Access and Upsizing Wizard

SQL Server Connection Error

FORUM

Upsizing from Access 2003 to SQL Express 2005

Lack of any upsizing being done

FORUM

Access to SQL Server: The Upsizing Wizard

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column...

Tags
access    
programming    
sql server 7    
upgrading    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones