Run a Series of mdbs automatically 5

[alr0]

Happy Friday All,

Using Access 03 with Win XP Pro:

I have several mdbs that each perform a function. Now that these work I would like to have a master mdb that starts one of these mdbs and when it is finished starts the next one until they have all run. I presume that I will have a form where users can enter the full path of each mdb to run.

I am interested in my VBA options to open each listed file as soon as the last one is completed until all listed files have been run.

Thanks for any insights,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[PHV]

What is your meaning of "running a mdb" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mp9]

Would the Handy Access Launcher (

http://www22.brinkster.com/accessory/resources/001.shtml)

meet your requirements?

 

[TheAceMan1]

How are ya alr0 . . .

The following is an API you can use. I'm not sure [blue]who gets the credit[/blue] but my library indicates Wait for a Process to Terminate w/ WaitForSingleObject aka [purple]thescarms.com[/purple]. Ive modified it slightly so the Path & File can be passed in.

So in the Declarations section of a new module in the modules window, copy/paste the following:

[blue]Const SYNCHRONIZE = &H100000
Const INFINITE = &HFFFF
Const WAIT_OBJECT_0 = 0
Const WAIT_TIMEOUT = &H102

Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, _
            ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, _
            ByVal dwMilliseconds As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long[/blue]

In the same module, copy/paste the following routine:

[blue]Public Sub ShellWait(FilePath As String)
   Dim lPid As Long, lHnd As Long, lRet As Long
   
   If FilePatth = "" Then Exit Sub
   lPid = Shell(FilePath, vbNormalFocus)
   
   If lPid <> 0 Then
      [green]'Get a handle to the shelled process.[/green]
      lHnd = OpenProcess(SYNCHRONIZE, 0, lPid)
      [green]'If successful, wait for the application to end and close handle.[/green]
      If lHnd <> 0 Then
              lRet = WaitForSingleObject(lHnd, INFINITE)
              CloseHandle (lHnd)
      End If
      
      [green]'MsgBox "Just terminated.", vbInformation, "Shelled Application"[/green]
   End If

End Sub[/blue]

Example of running 3 DB's:

[blue]Call ShellWait("C:\DB1Name")
Call ShellWait("D:\FolderName\DB2Name")
Call ShellWait("C:\FolderName\FolderName\DB3Name")[/blue]

[blue]Cheers! . . .[/blue]

See Ya! . . . . . .

 

[alr0]

Hi All,

Sorry for the delay, I was away all weekend.

First of all...
What is your meaning of "running a mdb" ?

I should have offered a bit more detail. I have an app that has one button to run some code that can be attached to an autoexec macro so it will run as soon as the mdb is opened. I need to run them sequentially because of resource utilization.

The other two posts are likely solutions, although I would like to set things up so that the user will enter the full paths to run in a form and start the process rather than have it be in code. It will need regular minor modifications of which mdbs will run.

Thanks to all, I will keep you apprised of my progress.

Regards,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[ItIsHardToProgram]

the user will enter the full paths to run in a form and start the process rather than have it be in code.

Are you sure that is a user friendly way to go???

Have you tried simply setting a form onload and putting the required fields in it... (use theAceman1's code....)

 

[alr0]

IIHTP,

I thought it should be on a form as opposed to in VBA code like Aceman's example. I prefer not to have users modifying code.

Were we referring to the same thing?

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[TheAceMan1]

alr0 . . .

I agree with [blue]ItIsHardToProgram[/blue]. If the locations of the DB's are static (as they should be), whats the point of user intervention as far as paths are concerned? [blue]You sure can't afford any typo's here![/blue]

Each DB opened by the master should perform the following:
[ol][li]Start code running thru AutoExec.[/li]
[li]When processes are complete, code closes the DB (automatic) or user closes DB (semiautomatic).[/li][/ol]

If the above is true the code I provided opens a DB and stops executing until that DB is closed, whereby the next DB is opened and so on.

[blue]I have an app that has one button to [purple]run some code that can be attached to an autoexec macro . . .[/purple] [/blue]

You need to be more specific about this! . . .

See Ya! . . . . . .

 

[alr0]

The selection of mdbs will change daily depending on the work that has been completed. Each mdb is used for between one week and a few months and then new ones begin. I assume that the program comes to a halt if a bad path is entered but nothing serious?

This has never been run as an autoexec or to end the application upon completion but it sounds easy. Is there something tricky that I should watch for?

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[TheAceMan1]

[blue]This has never been run as an autoexec or to end the application upon completion but it sounds easy. [purple]Is there something tricky that I should watch for?[/purple][/blue]

Not at all!

[ol][li]AutoExec automatically starts when the DB is opened.[/li]
[li]AutoExec is set to start your main controlling function . . . example:

[blue]Public Function [purple][b]FunctionName[/b][/purple]()
   [green]'Run 1st Routine
   'Run 2nd Routine
   '    .
   '    .
   'Run Nth Routine
   'Whatever!
   '[/green][purple]Application.Quit acQuitSaveAll[/purple] (Closes the DB)
End Function[/blue]

[/li][/ol]
If all is well, when you open this DB from windows, it should run code as you have it setup then quit. If this is so, then the DB is ready to be included in any sequence you like.

For Actual testing to see what its like, just set the paths to other applications (NotePad, Calculator and such)

See Ya! . . . . . .

 

[ItIsHardToProgram]

The selection of mdbs will change daily depending on the work that has been completed. Each mdb is used for between one week and a few months and then new ones begin.

I would suggest having the mdb selected by the work done completed... less possible errors, more convivial...

At some extant, you could even create the new mdbs through code when one is filled.

 

[alr0]

Hi All and TheAceMan1

I set up the code from TheAceMan1 in the 4th response of this thread and it is exactly what is needed. Thanks again!

I can open an instance of MSAccess.exe but when I try to open an MDB file, I get the following error.

Run-time error '5':
Invalid procedure call or argument

on the line:"lPid = Shell(FilePath, vbNormalFocus)"

I checked that double clicking on each MDB does what is required but I need a way to open selected Acess files.

Any thoughts?

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[SuryaF]

What's the value of your FilePath variable when the error comes up?

 

[alr0]

Just the full path and file name with a mapped drive, as entered in the Call ShellWait statement

T:\Mmfn1\GtoMmfn1V92eAuto.mdb

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[SuryaF]

Yep, you need the name of the executable. You can't run a file, you need to have a full command line for the Shell as far as I know.
Try:
lPid = Shell("msaccess.exe """ & FilePath & """", vbNormalFocus)

 

[alr0]

Hi SuryaF,

I had a compile error so I dropped one of the quotes in the last sequence. Then on running, I got the error "file not found".

Do I need the full path for msaccess? Which file can't be found?

Thanks,

alr


PS I will be going into a meeting so if I do not repond, I will be back at this tomorrow.

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[SuryaF]

Hi alr,
I'm not sure why you had to drop one of the quotes there. Anyway, the command line for Shell needs to include the database file path between quotes especially if you have a space in the path. I'm sure you can figure it out.

If it gives you File Not Found then you probably need to specify the full path for msaccess.exe. If you misspell the database file name and path it will still open Access and Access will give you an error like Database file not found.
I hope this helps.

 

[alr0]

I'll never know how you knew but you were right, I figured it out. Below is the line that works. Note the space after the full path of Access, it is required.

Thanks again to all,

alr

lPid = Shell("C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE " & FilePath, vbNormalFocus)

Where the variable FilePath is the full path of an mdb.

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[dpimental]

Are you running macros, queries, vba code, outputting files, what?

The reason I ask is because I work with a master db that processes data from many external db's. I sometimes need to run processes on the external db's from the master database.

Are you still considering options or did you already decide on one?

David Pimental
(US, Oh)

 

[alr0]

Hi dpimental,

I am running vba code, utilizing the FileSystemObject to read text files into multiple Access tables with one-to-many relationships. Files that have been read without error are moved to a different location. If an error was encoutered, files are not moved and an error table lists custom error messages with the last line read in the text file. There are unique cases that each have their own mdb with talbes and code for that case only.

I now have a master mdb that opens each case mdb, waits until it closes, and starts the next case listed. This portion is complete.

Now my challange is to create a gui that is user freindly. I am picturing 2 tables: tblCasesAvailable and tblCasesToRun, each displayed with a list box and the ability to add cases from those available to those to run. This seems rather straight forward but I am not certain how to reference an item selected in a list box. (either double click or highlight and clidk command button) Perhaps that will be my next thread as I have yet to find the answer.

If I can be of assistance, just let me know.

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.