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.
