Help! My Macro is Freezing my Database!

  • Thread starter Thread starter ssignore
  • Start date Start date
S

ssignore

Hello, MS Online Community.
I wrote what I thought (well, for a very green Access user) was a very cool
macro, but it's causing my database to freeze.
Firstly, the macro is attached to a command button on a 'switchboard' form.
The form currently has seven command buttons. Four of them are simply set up
to run queries. One opens a data entry form and another closes the database.

The command button running the macro is the one that is giving me problems.
Here is the macro:
Echo (set to No)
MsgBox - Informational; just says "Running the Report! Check your folder
when hourglass disappears."
Hourglass (set to Yes)
OpenTable - Tbl_Case Number Index Card Data; Datasheet view; Read only
OutputTo - Table (for type) Tbl_Case Number Index Card Data; format - MS
Excel 97-2003; Output File X:\Folder\Subfolder\Total Cases Entered.xls
Autostart - Yes

What am I missing that is freezing the dbase?
Thanks in advance for the guidance on a solution!
Regards,
Simone
 
well, you can get rid of the OpenTable action. there's absolutely no value
in opening a table before outputting it.

a couple other things to check: make sure the filepath is correct. make
sure the total records in the table don't exceed the maximum rows allowed in
an Excel spreadsheet. if no problems found re either of the above, you might
try setting Autostart to No - just a WAG.

hth
 
Simone,

I agree with Tina - the OpenTable is a bad idea, and should be removed from
the macro.

However, assuming you gave us the full details of your macro, you have
forgotten to turn the Hourglass off, and the Echo back on. This is called a
database-freezing recipe.
 
Tina and Steve,
Just to confirm, the macro SHOULD then be the following, correct?

Echo (set to YES) [Steve, you said 'turn the Hourglass off and the Echo back
on']
MsgBox - Informational; just says "Running the Report! Check your folder
when hourglass disappears."
Hourglass (set to NO)
OutputTo - Table (for type) Tbl_Case Number Index Card Data; format - MS
Excel 97-2003; Output File X:\Folder\Subfolder\Total Cases Entered.xls
Autostart - Yes

I will give this a shot!
Thanks very much for the help.
Regards,
Simone
 
Tina and Steve,
Just to confirm, the macro SHOULD then be the following, correct?

Echo (set to YES) [Steve, you said 'turn the Hourglass off and the Echo back
on']
MsgBox - Informational; just says "Running the Report! Check your folder
when hourglass disappears."
Hourglass (set to NO)
OutputTo - Table (for type) Tbl_Case Number Index Card Data; format - MS
Excel 97-2003; Output File X:\Folder\Subfolder\Total Cases Entered.xls
Autostart - Yes

I will give this a shot!
Thanks very much for the help.
Regards,
Simone
 
i think what Steve meant was that you should turn them *back* off and on,
AFTER the output action runs. like turning on a light when you walk into a
room, and turning it back off when you walk out of the room (let's be
green-conscious here! <g>). so try

Echo No
Msgbox
Hourglass Yes
OutputTo
Hourglass No
Echo Yes

hth


ssignore said:
Tina and Steve,
Just to confirm, the macro SHOULD then be the following, correct?

Echo (set to YES) [Steve, you said 'turn the Hourglass off and the Echo back
on']
MsgBox - Informational; just says "Running the Report! Check your folder
when hourglass disappears."
Hourglass (set to NO)
OutputTo - Table (for type) Tbl_Case Number Index Card Data; format - MS
Excel 97-2003; Output File X:\Folder\Subfolder\Total Cases Entered.xls
Autostart - Yes

I will give this a shot!
Thanks very much for the help.
Regards,
Simone






Steve Schapel said:
Simone,

I agree with Tina - the OpenTable is a bad idea, and should be removed from
the macro.

However, assuming you gave us the full details of your macro, you have
forgotten to turn the Hourglass off, and the Echo back on. This is called a
database-freezing recipe.
Click to expand...
Click to expand...
 
Correct, Tina.

Then again, Simone, unless the table is extremely large, or your computer is
very old and slow, I would expect the amount of time to perform an OutputTo
to an Excel file would be miniscule, and therefore the hourglass would not
even be seen, so really there's probably not much point in using the
hourglass action at all. And the purpose of turning off the Echo while a
macro runs is to hide multiple screen updates looking ugly. And since your
OutputTo is not likely to affect the screen display, that's probably useless
as well. So if it was me, I'd change the macro to 2 actions only:
OutputTo ...
MsgBox "Done. Check for new Excel file."
 
Back
Top