Import and 2G limit

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

There is a large amount of waste space that accumulates during import. My
database has gotten large enough that I have to compact & repair the database
half way through the file imports in order to avoid the 2Gig file size limit.

Is there any way to set up Access to minimize the amount of waste space that
accumulates so I can eliminate the extra compact & repair step?

Thanks
 
There's no set up optoin that I know of, but taking care with indexes
can help quite a bit. First, ensure that there are no unnecessary
indexes in the database (sometimes, you'll find that there is more than
one index doing the same job because Jet creates some automatically).

A further step is to drop (delete) some or all of the indexes on the
table before the import. After importing, compact; then re-create the
indexes and any relationships that had to be dropped before you could
drop the indexes.

After that, it's linked tables or a database server<g>.
 
Deleting and recreating the indexes may also increase the speed. Will Access
through the Jet engine accept SQL commands for creating and deleting indexes?
Also, are there SQL commands for creating and deleting relationships? If
so, I can batch the creation and deletion and maybe speed things up at the
same time.

Thanks
 
Yes, you can execute SQL DDL statements (e.g. DROP INDEX, CREATE INDEX)
or use the DAO library and manipulate the Indexes collections of
TableDef objects.

Likewise you can manipulate relationships either with a CONSTRAINT
clause in an ALTER TABLE statement or with DAO. I've never tried doing
it either way myself, but get the impression that there are various
traps for new players, so go carefully and make sure your backups are in
good order!

Jamie Collins, who also posts in the Access newsgroups as "onedaywhen",
knows a great deal about what can be done with constraints in Jet SQL;
if you want to work that way and aren't familiar with CONSTRAINT syntax
it might be a good idea to google for his messages.
 
Are you importing tables and then appending the records to existing tables?
If so, you can reduce the database bloat by importing the data to a
temporary database, link the tables into the production database and then do
your append. This will not bloat the database.

You can also do this all programmatically. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"ImportToTempDatabase.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Back
Top