Disabling Undo & Garbage Collection

[Guest]

Is there any way to completely disable ALL undo functions for an Access 2003
database? I work with a database that processes large text files, and often
have to deal with Undo warnings and errors. As I am trying to automate the
import process entirely, I need to be sure that the process will continue
unattended. In no case will I ever need any step in the process to be
undoable. (If it did, I wouldn't be automating it in the first place.)

Also, on a side note, does anyone know how Access manages garbage
collection? During the import process, we build and export various tables
(some over 600MB) and although we DELETE * FROM tables, the database size
retains its previous size. Can Access 2003 reuse the space those records
occupied, or will I be forced to manually stop the import process to Compact
the database each time it approaches the 2GB limit?

Thanks in advance to anyone who can help on these issues.

- Devon

 

[John Spencer]

What is your code? Are you using DoCmd.RunSQL?

You can try surrounding that with DoCmd.SetWarnings False and
DoCmd.SetWarning True

Or you can use
Dim dbAny as DAO.Database

dbAny.Execute StrSQL, dbfailonerror

Access has to be compacted to recover used space.

 

[Tim Ferguson]

Is there any way to completely disable ALL undo functions for an
Access 2003 database? I work with a database that processes large text
files, and often have to deal with Undo warnings and errors.

If you want to stop the alert boxes, you can do one of two things:

a) Use the DoCmd.SetWarnings method; this carries the risk of forgetting
to set them back on again and silently wrecking a database.

b) use the Database.Execute method with the dbFailOnError argument. This
is probably quicker, allows longer command strings, gives you a trappable
error if there is a problem with the sql command, but misses the VBA
expression service so you can't refer to vba procedures or GUI objects.

Also, on a side note, does anyone know how Access manages garbage
collection? During the import process, we build and export various
tables (some over 600MB) and although we DELETE * FROM tables, the
database size retains its previous size. Can Access 2003 reuse the
space those records occupied, or will I be forced to manually stop the
import process to Compact the database each time it approaches the 2GB
limit?

In a word: yes. Actually I would worry about file corruption with this
amount of activity -- take lots of backups!

One good strategy with temp tables to make a brand new mdb
(DbEngine.CreateDatabase) to hold them and link them into your main mdb.
You can then delete if off the face of the hard disk when you are
finished with it.

HTH


Tim F

 

[Guest]

I highly recommend option b)
It is much, much faster than DoCmd.RunSQL because Currentdb.Execute goes
directly to Jet without passing through the Access UI and you don't have to
worry about the SetWarnings.

 

[John Nurick]

Hi Devon,

This article is useful if you need an Office app to run unattended:
Considerations for server-side Automation of Office
http://support.microsoft.com/?id=257757

Obviously I don't know what you're doing to your data, but have you
considered doing all (or most) of the processing with tools such as Perl
and/or textutils (which are designed to handle humungeous text files)
rather than Access/Jet (which isn't)?