How Can I Edit a Macro in Code?

[Smartin]

Hopefully the question is self-explanatory.

I've seen very little on the topic, and what I have seen poo-poo's the
notion so an FAQ is in order...

Why? I have a database that uses a macro that executes about a dozen
queries. Several of these queries create tables that are used
downstream to create other tables.

The problem is the database exceeds the 2GB limit (this is A2003 BTW)
in mid-process. When execution breaks I export the temp tables to a
new MDB, link them in, compact, and resume where execution left off.
It's rather tedious as I have to do this several times, and each
export and compact is both time and resource consuming. Unfortunately
the path to my files changes every month (else I would hard code a
path to an external MDB in the make-table queries).

So... I just learned I can compact in code. Fantastic! I can automate
exporting and relinking tables, and running a compact. What I'd like
to do is break up the big control macro into smaller bits and set an
autoexec macro to resume execution at the appropriate control step
before each compact fires. Then I can set the whole thing in motion on
a second machine and come back two hours later to enjoy the finished
product. Seem reasonable?

FWIW I saw one idea to load a macro from a text file but I'm not clear
on how to do this in code.

 

[Wolfgang Kais]

Hallo "Smartin".

Hopefully the question is self-explanatory.

I've seen very little on the topic, and what I have seen poo-poo's
the notion so an FAQ is in order...

Why? I have a database that uses a macro that executes about a
dozen queries. Several of these queries create tables that are
used downstream to create other tables.

The problem is the database exceeds the 2GB limit (this is A2003
BTW) in mid-process. When execution breaks I export the temp tables
to a new MDB, link them in, compact, and resume where execution
left off. It's rather tedious as I have to do this several times,
and each export and compact is both time and resource consuming.
Unfortunately the path to my files changes every month (else I
would hard code a path to an external MDB in the make-table
queries).

So... I just learned I can compact in code. Fantastic! I can
automate exporting and relinking tables, and running a compact.
What I'd like to do is break up the big control macro into smaller
bits and set an autoexec macro to resume execution at the
appropriate control step before each compact fires. Then I can set
the whole thing in motion on a second machine and come back two
hours later to enjoy the finished product. Seem reasonable?

FWIW I saw one idea to load a macro from a text file but I'm not
clear on how to do this in code.

You should take the step from macros to vba code. In vba you can
use variables for all kind if commands andthis gives you much more
flexibility on what to execute.
Compacting a database (through code ore the UI) requires that the
database to compact is closed so it can be opened exclusively be the
compacting process. Therefore, you can't compact the current db.
Trying to do so via dao will fail, because the database is already
in use. Using the DoCmd.Runcommend acCmdCompactDatabase command will
also fail. Therefore, I'd like to know the code used for a database
to compact itself (it would have to close itself terminating the
procedure that does the compact...)
Your code should create a blank database and then create the tables
in that external database.

 

[Smartin]

Hopefully the question is self-explanatory.

I've seen very little on the topic, and what I have seen poo-poo's the
notion so an FAQ is in order...

Why? I have a database that uses a macro that executes about a dozen
queries. Several of these queries create tables that are used
downstream to create other tables.

The problem is the database exceeds the 2GB limit (this is A2003 BTW)
in mid-process. When execution breaks I export the temp tables to a
new MDB, link them in, compact, and resume where execution left off.
It's rather tedious as I have to do this several times, and each
export and compact is both time and resource consuming. Unfortunately
the path to my files changes every month (else I would hard code a
path to an external MDB in the make-table queries).

So... I just learned I can compact in code. Fantastic! I can automate
exporting and relinking tables, and running a compact. What I'd like
to do is break up the big control macro into smaller bits and set an
autoexec macro to resume execution at the appropriate control step
before each compact fires. Then I can set the whole thing in motion on
a second machine and come back two hours later to enjoy the finished
product. Seem reasonable?

FWIW I saw one idea to load a macro from a text file but I'm not clear
on how to do this in code.

Replying to self... I thought of a way to accomplish this without
editing the macro. I will just maintain a table that keeps track of
what steps are done and use the autoexec macro to call a VBA function
that figures out where to resume after compacting.

Any other suggestions are welcome!

 

[Smartin]

Hallo "Smartin".










You should take the step from macros to vba code. In vba you can
use variables for all kind if commands andthis gives you much more
flexibility on what to execute.
Compacting a database (through code ore the UI) requires that the
database to compact is closed so it can be opened exclusively be the
compacting process. Therefore, you can't compact the current db.
Trying to do so via dao will fail, because the database is already
in use. Using the DoCmd.Runcommend acCmdCompactDatabase command will
also fail. Therefore, I'd like to know the code used for a database
to compact itself (it would have to close itself terminating the
procedure that does the compact...)
Your code should create a blank database and then create the tables
in that external database.

And you are so right. The code I found on mvps.org only works on the
first call. When the db subsequently reopens the code fails "Method
'accDoDefaultAction' ... failed".

Creating a blank database and creating tables there is a challenge
because the path changes, but I suppose it's possible to rewrite the
make table queries on the fly. Or I could change the whole suite of
queries to appends, but then I'm faced with compacting not only the
local db but the remote ones as well.

Back to the drawing board I guess...

Oh, here's the code I was trying to leverage:

http://www.mvps.org/access/general/gen0041.htm

' ***** Code Start *****
Public Sub CompactDB()

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

End Sub
' ***** Code End *****

 

[Pete D.]

First step is to split your database into front end and back end then the
tables created won't affect your code running at all and you can close and
open the backend as needed without interupting your code. Second off (and I
have little experience with it) you should really think about upsizing to a
SQL back end if you keep hitting the mdb limit of 2gb. It is just a matter
of time before you will have a database that is corupt and not recoverable.
If your going to continue with the MDB then you should look at using multi
back ends for the data and creating only enough tables in them that will not
hit any limits or required you to compact during your process. Path changes
can be handled by having your code use a variable for the path which you can
store in a table or call for it when you start the code. This is a common
probldem especially if you will use code at different sites. I use a small
one record table to hold my specific installation variables such as paths
and server names. This also helps with the portablity of the code for other
uses. Just some thoughts for your considerations. Pete D.

 

[Wolfgang Kais]

Hello "Smartin".

And you are so right. The code I found on mvps.org only works on the
first call. When the db subsequently reopens the code fails "Method
'accDoDefaultAction' ... failed".

Creating a blank database and creating tables there is a challenge
because the path changes, but I suppose it's possible to rewrite the
make table queries on the fly. Or I could change the whole suite of
queries to appends, but then I'm faced with compacting not only the
local db but the remote ones as well.

Back to the drawing board I guess...

Oh, here's the code I was trying to leverage:

http://www.mvps.org/access/general/gen0041.htm

' ***** Code Start *****
Public Sub CompactDB()

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction

End Sub
' ***** Code End *****

Compacting the current database is the problem, other databases can be
easily compacted using the DBEngine.CompactDatabase method.
Creating an other database also should be easy using the CreateDatabase
method of a workspace object, which will create a new blank database.
Also: Dynamically generating a SQL string and running it using the
Execute method of a database object (CurrentDB or other database) is
quite easy. Alternatively, you could us a QueryDef object in the
current or other database to change it's SQL property or run it's
execute mathod.
An other option could be not using make table queries but ordinary
select queries. In a "target database template", create the structure
of tables and everything you need, for example append queries that
access the select queries in the source database (I guess that the
path and name of that database does not change).
To do the transfer process:
Use DBEngine's CompactDatabase method to create a copy of the template
database, the use the OpenDatabase method of Workspace number 0 to get
a reference to the new database, then "Execute" the append queries
that now already exist in the new database. Don't forget to close the
database.

 

[Guest]

The temp tables should not be in the primary database.
Create a new temp database, create the temp tables
in the new temp database, link to the temp tables if required*
If the temp database gets too big, create a second temp
database. Use as many temp databases as required,
delete when completed.

Don't bother compacting temp tables.

*You don't need to link tables if you don't want to.
Any normal query can work on any database like this:

select * from [database full path and name].[table name]

Queries can even work across multiple databases.

(david)