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

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.

"Devon" <(E-Mail Removed)> wrote in message
news:7CFC5255-227C-47DB-9A31-(E-Mail Removed)...
> 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

=?Utf-8?B?RGV2b24=?= <(E-Mail Removed)> wrote in
news:7CFC5255-227C-47DB-9A31-(E-Mail Removed):

> 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

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.

"Tim Ferguson" wrote:

> =?Utf-8?B?RGV2b24=?= <(E-Mail Removed)> wrote in
> news:7CFC5255-227C-47DB-9A31-(E-Mail Removed):
>
> > 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
>
>
>

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)?



On Wed, 3 May 2006 09:12:02 -0700, Devon
<(E-Mail Removed)> wrote:

>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 Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.