Access Size Limitations, Move Data to SQL

[Karl Burrows]

I was approached by a company about assisting with the conversion of an
existing Access 97 database to a SQL back end. I am basically charged with
finding a solution and coordinating the process. The first thing I did was
look at the Access data structure. It was an old database converted from
dBase, but the biggest thing I found was the database is over 30GB! This
only includes the 2 most recent years of data and they archive the rest.
The other issue I found is there is one table with over 750,000 records
(very poor job of table structures). After looking up file limitations in
Access, I am really surprised this database even runs.

First, is this database just an accident waiting to happen? Second, I think
I know how to move the data to a SQL side, but how do you interface it with
Access for the front-end? Just link tables like a regular split database or
is there something else that needs to be done. I am trying to determine the
best direction to go with this for a long term solution. Will 97 run a
database upsize to SQL?

What other things should I be concerned with? Thanks!

 

[Rick Brandt]

I was approached by a company about assisting with the conversion of
an existing Access 97 database to a SQL back end. I am basically
charged with finding a solution and coordinating the process. The
first thing I did was look at the Access data structure. It was an
old database converted from dBase, but the biggest thing I found was
the database is over 30GB!

You must be mistaken. A single Access file is limited to 1GB (A97 and older)
or 2 GB (A2000 or newer).

Unless they have their data spread out over multiple files in which case
yes, you should definitely get this data into a server database capable of
dealing with that much data without resorting to such trick to spread it
out.

 

[Karl Burrows]

They must be living on the edge then. There may be some linked tables in
other database files, but the core table with 750,000 records is 2GB by
itself.

What advice can you offer as far as SQL migration or other? Does 97 have
the ability to upsize to SQL? Thanks!

I was approached by a company about assisting with the conversion of
an existing Access 97 database to a SQL back end. I am basically
charged with finding a solution and coordinating the process. The
first thing I did was look at the Access data structure. It was an
old database converted from dBase, but the biggest thing I found was
the database is over 30GB!

You must be mistaken. A single Access file is limited to 1GB (A97 and older)
or 2 GB (A2000 or newer).

Unless they have their data spread out over multiple files in which case
yes, you should definitely get this data into a server database capable of
dealing with that much data without resorting to such trick to spread it
out.

 

[Rick Brandt]

They must be living on the edge then. There may be some linked
tables in other database files, but the core table with 750,000
records is 2GB by itself.

What advice can you offer as far as SQL migration or other? Does 97
have the ability to upsize to SQL? Thanks!

I don't recommend any of the upsize tools. I would import the tables onto the
server using DTS so I have complete control over the data types used and then
set up any relationships and constraints myself.

 

[Dean]

Another thing to check is have these Access files ever been compacted?
750,000 records does not seem like a lot. Granted it might run kind a
slow. I would start with the larges mdb file and go from there.

If that solves their problem, then right a routine that will compact
all the mdb files for them.

 

[John Graham]

There is an Access 97 wizard for upsizing to SQL Server, but in it's
original form it won't work with SQL Server 2000, because it gets an
overflow error on the version number (the version number before 2000 was 7).

The fix for this is to find a source code copy of the wizard, break on
error, and fix up the wizard so that it doesn't crash on the version number.

The source code version used to be a download from Microsoft. Note that it
had a .mde file extension, but that was just the file name - so that it
could be used interchangably with the mde version.

Alternatively, you could use a later version of the wizard. By default, the
Access 2000+ version of the upsizing wizard creates tables with nVarChar
text fields, ie UniCode text fields. Access 97 can't link to tables with
UniCode text fields, so either you would have to go through the tables and
change the nVarChar fields to VarChar, or switch to an Access 2000+ front
end.

None of the wizards are very robust: they fail without much information if
you have data problems like dates which are invalid in SQL Server, or design
problems like a primary key which includes a bit field. If you haven't done
this before, you might wish to consider buying
http://www.ssw.com.au/ssw/UpsizingPRO/Default.aspx. At least have a look at
the demo version.

The other kind of problem you can have is with related tables. If the
wizard upsizes tables in the wrong order, it won't be able to transfer the
data and re-create the dependencies. You may have to break the dependecies,
transfer the data, then re-create the relationships (which is what you would
do if you were doing the whole thing by hand, using DTS).

After we got comfortable with the requirements of SQL Server, and adjusted
our database design and object names to work with SQL Server, we didn't have
any problems with the Access Wizards, and we haven't used DTS for years.

Access 2000+/SQL Server allows you to update and append to autonumber
fields, which is not directly supported by Access97/SQL Server. On the
other hand, DAO transactions are badly broken: I would go backwards to A97
rather than attempt DAO transactions with Access 2000/SQL Server linked
tables.

Note that in general, SQL Server isn't any faster than DAO/Jet, (given the
size of your tables, you may do better). Native DAO "seek" is much faster
than SQL Server linked tables. If you need to get the speed of "seek" in
Access, you will have to carefully rewrite your application to use
server-side indexes.

(david)

 

[John Vinson]

but the core table with 750,000 records is 2GB by
itself.

hmmm...

Are these EXTERNAL tables? .dbf or some other database format? Or are
they native Access/JET tables within a .mdb file, or more than one
..mdb file?

John W. Vinson[MVP]