Me And Microsoft Access Autoexec Problem

[ispaydeu]

Ok, so it took me many hours to figure this out and I have been scrathcing my head for weeks.

 

So, I had a really comples macro that was running and was calling lots of different macros through out it.

 

One part in particular is setup so that the macro calls up access to run. Then, within Access I have an autoexec macro that runs and processes a file on my computer and sends out the results.

 

Here is the problem...

 

Sometimes when the autoexec program runs, one of the queries returns a null error. Now I am VERY experienced with Access and know how to fix the error within the query.

 

However, here is the problem. If I use any command in ME to activate the Access program that has the Autoexec in it, ME will NOT move on to the next step of the macro (hit enter, compare controls, compare pixel, ANYTHING) until the Access autoexec macro has completed.

 

So, as the MOST basic example in the world. If I setup a macro, and the only line in it is to call up my access database, ME will not complete until the Autoexec macro within Access is complete. Which creates a problem, I can't tell my macro to hit ENTER or anything on that Null prompt because it is still on the previous launch command.

 

So, if my macro is...

 

Program Launch: "Access database that has an autoexec macro inside of it"

Program Launch: "notepad.exe"

 

It will NEVER launch the notepad.exe or do ANYTHING at all until the access's autoexec is done. So I can't hit enter on errors. Or do anything.

 

Now, If I open an access database that does not have an autoexec inside of it, and just plain out open it, the next command is almost instantaneous.

Also, when the prompt comes up about the null error, and my PC is not doing a darn lick of a thing, not thinking, nothing, it still will not move on until the macro within access is complete, so my macro just gets stuck if an error ever comes up.

 

Also, I already have all the "do not display error" messages in Access turned off, but apparently you can't turn off the Null message.

 

Has ANYONE ever experienced this? Did you find a work around? What do you recommend? Anyone have any ideas why this is occuring (maybe macros within access use similar features to macro express and they cannot coeixit so one waits for the other to complete?)

[ispaydeu]

I am aware that, in the Autoexec macro I can select "SetWarnings" and turn off all of the warnings. However, I would still like to be able to do things while access is processing. For instance, if I have a long Autoexec macro that takes more than 15 minutes to process, I need to ensure that my computer will not be getting locked up or else the rest of my macro will not run (normally I have my macro move the mouse in between events so it won't lock up. If I cannot move the mouse because access stole my macro process, then it will lock up!

 

Please help,

Matt

[ispaydeu]

Also, I can start the Access database with the autoexec in it, then start my macro express, and it will perform actions just fine. Its just something about launching a database with an autoexec in it.

Matt

[paul]

Is Access still running when the Autoexec macro has finished, or do you quit Access at the end of your autoexec macro? If the latter, then you can check whether Access is still running in your ME macro. Otherwise, perhaps your autoexec macro could create a file somewhere when it's finished. Then you can have your ME macro check for this file's existence, and continue processing once the file is ready (remembering to delete the file in the ME macro).

 

A better solution might be to anticipate and allow for NULLs in your queries, so that they work with and without NULLs!

[ispaydeu]

My problem is not knowing when Access ends or not. To answer your question, my autoexec macro does quit out of access automatically. My problem is not knowing whether or not access is closing. My problem is being able to perform actions while the autoexec is performing. So, if a null error or another error was to present itself, I can take the appropriate steps to fix it.

 

Regardless of that, I don't even need to use a "wait for terminate" command because the next action in my macro will not perform until the autoexec is completed, so if it never completes, it will never move on in my macro, which is what I need help with.

Matt

[paul]

Can you zip and send the macro in question? I know Access well, and may be able to understand your problem better when I see the actual macro code.

[ispaydeu]

There is nothing special to my macro at all, if i just set it up with 2 commands as follows...

 

Program Launch: "Access database that has an autoexec macro inside of it"

Program Launch: "notepad.exe"

 

The notepad.exe will not be launched until Access's autoexec macro completes. Try it, you will see what I mean. With anything else, if I was to launch excel then notepad, it would open both immidiately, but with access, if the access has an autoexec, it will not open immdiately.

 

I am not trying to do anything special, ideally I would like to set it up so that if an error message were to come up that I was able to press enter using the macro. But it will NEVER occur because once the error message comes up, macro express will still not move on until access's autoexec is done. And since Access autoexec is never complete until enter is pressed on the error messge, it will just halt and never finish.

Matt

[ispaydeu]

Paul, did you have a chance to look at this? It's a doosey.

Matt

[Muttin]

Hi, I have never used Access but I came up with one work-around that I think could possibly work for this. I realize it wouldn't be ideal, but you could have an if statement that would check to see if the error message popped up, and if it did, press enter (or whatever is needed to satisfy the error message) and then open notepad, or whatever you wanted it to do. I don't know exactly what you are trying to do but I am sure there are ways to check and see if you do get the error message, including possibly the "Get Pixel Color" function. Sorry if I have misunderstood the problem, this is just what I thought of first after reading your post. Good luck with your macro.

[ispaydeu]

That would work great, except that it cannot get to the point in which an if statement would read. The second I call up the access program that has an autoexec, it will never allow macro express to perform any actions at all (evaluate an if than, open a program, move the mouse) until the access finishes its autoexec.

Matt

[paul]

I've now had a chance to do some testing, and I got the results I expected, which are different to what you describe!

 

I created an Access 2003 mdb, with a single macro called Autoexec, which runs a non-existent function called TestIt. When I open the database, the macro halts with an error, which is what you'd expect.

 

Then I created an ME macro to open the above mdb database using Access 2003, and display a message "Arrived", as in:

Program Launch: "MSACCESS.EXE"

Text Box Display:

 

As I expected, running this macro does the following:

- opens Access

* Access displays the macro error message complaining about the missing function

- immediately displays the ME dialog box "Arrived" without waiting for Access to terminate.

 

I couldn't understand why ME would wait for Access to terminate, regardless of whether its Autoexec macro ran successfully or not, since that's not the way ME was designed to work. And indeed, it doesn't.

 

So you must have something else going on, though I cannot imagine what!

[ispaydeu]

Paul, I am positive this is an error.

 

My guess would be if cause a dilberate error in your autoexec macro that would halt the macro completely, then it will allow macro express to move on immidiately. I would recommend setting your database autoexec macro in a way where it has to do something and completes succesfully, however, set it up so it has to do SOME sort of modifcations or something so that it takes time to do it, that way it is more obvious to see the fact that ME can't move to the next step until access is done.

 

I tried to recreate what your talking about, where all the access database does is have a function in it that is not a valid function. First, I wasn't sure if you meant that you put that in as your line in your autoexec macro, and just made the function "testit", or if you meant you used your macro to run a query that had a bad function in it called "testit". Either way, I am not getting the results you have.

 

I would say to confirm 100% that this is a user issue on my end, first try to setup your autoexec so that it does some sort of process, but set it up so that it doesn't diliberatly crash. I think you will still see what I see.

 

I uploaded/attached a avi recording of my screen. You will see I only have 2 lines in my ME, one that opens the .mdb (access file) and the other that just puts in a pause display box immidiately after it. but you will see when I open my access file it will not pop up with the pause prompt until access has completely finished.

 

You will notice that instead of using the "launch" option I am using "Open Folder:" command with a direct link to my access file. This same error occurs regardless of using the launch option or folder option. I just use the folder option because some of my autoexec access files process so quick that macro express can't determine they are open and then errors ME off, but thats whole nother thing unrelated.

 

So, please view my avi and give me feedback. Also, if you could try it again on your end but this time without a diliberate crash.

 

Also, you will see that in the lower right corner of my screen the running man goes the entire time the access is running and that the pause prompt will NOT come up until access is done, yet I do not have any wait commands in at all.

 

Please provide feedback, this is stumping me.

Matt

[ispaydeu]

Paul, I took the avi off for now, there was some product info on there that should not have been recorded. I will try to get a reivsed avi soon. If you could look this over with a nonhalting autoexec it would be appreciated.

Matt

[paul]

Here's my ME macro:

Program Launch: "MSACCESS.EXE" 'this opens a database called TestME.mdb

Text Box Display: 'This displays "Arrived"

 

Here's my TestME.mdb database:

Table tblData

DataId Primary Key AutoNumber

DataText Text(200)

 

Macro Autoexec

RunCode CreateData ()

 

Module Module1

Option Compare Database

Option Explicit

Public Function CreateData()

Dim MyRs As DAO.Recordset

Dim lngI As Long

Dim lngNumrecs As Long

DoCmd.Hourglass True

DoCmd.RunSQL "DELETE * FROM tblData"

lngNumrecs = 100000

 

Set MyRs = CurrentDb.OpenRecordset("tblData")

With MyRs

For lngI = 0 To lngNumrecs - 1

.AddNew

.Fields("DataText") = "Text " & Format$(lngI, "000000")

.Update

Next lngI

.Close

End With

Set MyRs = Nothing

DoCmd.Hourglass False

DoCmd.Quit acQuitSaveNone

End Function

 

This function simply inserts 100,000 rows into tblData after deleting any existing rows in the table. It takes around 10 seconds to run on my machine.

 

When I run the ME macro, the dialog box containing the word "Arrived" appears within 2 seconds (and Access is still running; Access closes itself after another 8 seconds or so).

 

Here's my guess at what's happening on your machine. Whatever code or query your autoexec macro is running is CPU intensive and utilizes your CPU at 100% (this is very common with Microsoft Access - it is the greediest piece of software I've ever seen, and is very poor at releasing control to any other applications when it is occupied with a database task). The essential difference between my environment and yours is probably that I'm using dual CPUs, so I still have CPU available even when Access is doing its best to take over the machine! While your CPU is 100% occupied, ME is not going to be able to continue on with its macro, even though that's the way it is designed to operate. You can test this observation for yourself by inserting a line containing the word DoEvents after the line containing the word ".Update" in the above code - I'm pretty sure your ME macro will then be able to continue.

 

I have written a generalized VB Server module that runs various VB code to return results to ME. I always have to write specific logic to force ME to wait until my VB server has finished (I use a flag in the registry to achieve this).