Run code from another database

[alpder]

I generate many reports daily which are emailed to clients. To automate the task I run each from the Task Scheduler, but it is getting rather complex.
So, I have created a database and within that I have code to call each report which is required. The code for creating the reports is in a second database. I started by using the Shell command, but that does not wait for the calculation to complete before moving on to the next piece of code. What I need is to generate the report by running the code in the second database (and wait for it to finish) before proceeding with running more code.
I would be grateful for any help.

 

[p27br]

Hi, if you want to control another Access Database, use the Access.Application object

Dim myAccessDb as Access.Application
Set myAccessDb = new Access.Application
acc.OpenAccessProject (Path)

with myAccessDb
.Docmd.RunSQL "SELECT * FROM tblCustomer"

.CloseCurrentDatabase
end With

set myAccessDb = Nothing

 

[alpder]

Hi p27br. Thanks for your suggestion. I have started to set up the controlling database and here are a few lines of the code, plus the function that opens the second database. You will see I call an Excel spreadsheet to format the final report.

The problem I have is that it will generate the first report, then give an error while executing the second:
Run-time error '-2147352567 (80020009)'. Automation error. Exception occurred.

Report = "MCINEW"
XLSFile = "MCISNEW"
XLSLocation = "P:\NDAILY\"
Funct = "MCINEW"
Call calcreport(Report, XLSFile, XLSLocation, Funct, 1)
'
'Start MCIBUNUNDELIVERED
Report = "MCIBUNUNDEL"
XLSFile = "MCIBUNNINGSUNDELIVERED"
XLSLocation = "P:\NDAILY\"
Funct = "MCIBUNNINGSUNDELIVERD"
Call calcreport(Report, XLSFile, XLSLocation, Funct, 1)
.
.
.
.(continuing list)

Function calcreport(Rpt, File, Location, Functnam, db)
Dim dbs As Database
Dim calc As Recordset
Dim objEx As Excel.Application
Dim objWB As Excel.Workbook
Dim myaccessdb As Access.Application
DoCmd.SetWarnings 0
'On Error GoTo checkerror
DoCmd.SetWarnings off
Set dbs = CurrentDb
Set calc = dbs.OpenRecordset("Log", dbOpenTable)
calc.AddNew
calc.Fields(2) = Now()
calc.Fields(1) = "Start " & Rpt
calc.Update
If db = 1 Then dbase = "P:\NDAILY\COMPONENTS.MDB"
If db = 2 Then dbase = "P:\AGENTS\INVOICEREPORTS\INVREPORTS.MDB"
'
'
'trans = Shell("C:\PROGRAM FILES\MICROSOFT OFFICE\Office\msaccess.exe " & dbase & " /x" & Functnam, vbNormalFocus)
'
'Open database and run funtion
Set myaccessdb = CreateObject("Access.application")
Set myaccessdb = GetObject(dbase)
With myaccessdb
.run Functnam
.CloseCurrentDatabase
End With
Set myaccessdb = Nothing
'
'Run formatting code in XLS
Set objEx = New Excel.Application
objEx.Visible = True
Set objWB = objEx.Workbooks.Open(Location & "FORMAT" & File & ".XLS")
objWB.Sheets("Sheet1").Select
objWB.Application.ActiveWorkbook.RunAutoMacros xlAutoOpen
Set objWB = Nothing
Set objEx = Nothing
Set dbs = Nothing
End Function

I would be grateful for any suggestions you may have to offer.

 

[earthandfire]

From what you are saying Excel starts before the Access database has finished.

When Access starts it creates a .ldb file in the same location and with the same name as the .mdb file. So you could try the following (pseudo code only)

Open Access Database
'Wait for Access to open and create the .ldb file
While AccessDatabase.ldb not found
DoEvents
End While
'.ldb file now created
While AccessDatabase.ldb found
DoEvents
End While
'.ldb file has now been deleted
Run Excel

Hope this helps

 

[alpder]

Thanks earthandfire....problem solved

 

[earthandfire]

Glad to be of help. I had a similar problem trying to manage Word through Access a couple of years ago and found this to be the simplest solution (though perhaps not the most elegant ).