execute public function in another database 3

[vandaliarental]

Can you execute a function located in another database?

Public Function Daily()
Dim dbExt As DAO.Database
Dim loc As String
Dim Pfunc As String
loc = "C:\MSAccess\inventory\BalanceReplCharges_fe.mdb"
Pfunc = "DailyRpt()"
Set dbExt = OpenDatabase(loc)
dbExt.Execute (Pfunc)
dbExt.Close
End Function

When I run this, I get an error on the dbExt.Execute line saying that Microsoft Jet can't find the query or input table "DailyRpt". What command would you use to run a public function that's located in another database?

Thanks,
Connie

 

[RickSpr]

The name "test" is the VBA project name in the other database. If it's Access 97, you can change that on the Tools|Options dialog Advanced page. For later versions you change it in the VBA editor's Tools|Project Properties dialog.

You still get the same error because the Database object's Execute method is for executing queries, not code.

Once you have a Reference set to the other database, you can call its public procedures directly, as if they were in your own database. You don't need the Daily() function at all.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[vandaliarental]

Thanks, RickSpr!!! That was it! I was driving myself crazy trying to figure it out.

Now, just one more challenge...

I was thinking about building a table to list the procedures I wanted to run & how often (e.g. daily, weekly), that way I could set it up with MS scheduler to run the daily function, have it read the table & print each report listed. The main reason I want to do this, is my scheduler is getting to be a mess with everything I want to run & I'd just as soon have a list that once it's done with one it can go on to the next. I've got the loop set up, I'm just not sure how to do the execute. Here's what I've got to run all the reports/code in the 'Daily' table:

Public Function Daily()
Dim rst As DAO.Recordset
Dim dbExt As DAO.Database
Dim loc As String
Dim Pfunc As String
Set rst = CurrentDb.OpenRecordset("Daily&quot
rst.MoveFirst
Do While Not rst.EOF
With rst
Pfunc = rst!

 '[Code] is the public function
        Pfunc
        .MoveNext
    End With
Loop
rst.Close
Set rst = Nothing
End Function

I get a compile error, Expected Sub, Function or Property on the Pfunc line.
 
Or would it just be better to hard code each function?

Thanks,
Connie

 

[RickSpr]

If all the procedures you list in the Daily table are Public procedures with no parameters in standard modules within your VBA project or in a library in your References list, you can call them this way:

    Run PFunc

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[vandaliarental]

Works great! Thanks a lot!

Connie

 

[hwkranger]

This is helpful information indeed

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[vandaliarental]

OK, just one more! In some of the databases, public function executed modifies a TempTable, such as "DELETE TempTable.* FROM TempTable;" then "INSERT INTO TempTable...etc".

If I just do the Run Pfunc I get an run-time error 3078; The Microsoft Jet database engine cannot find the input table or query 'TempTable'. Make sure it exists and that it's name is spelled correctly.

I think I actually need to have the database open that contains the public function I'm trying to execute??

Thanks,
Connie

 

[RickSpr]

The problem is that all SQL runs against the CurrentDb(), which is the one you opened in the Access UI. Period. Can't change it. So what's happening is that Jet is looking for TempTable in the database that calls PFunc, but the table is actually in the database that contains PFunc.

You can get around it, however. There are two ways.

The easy way: Link TempTable in the main database. Easy, like I said.

The slightly harder way: There is a not-very-well documented clause in Jet SQL that lets you access/modify a table in another database without having a link to it. Let's say your PFunc and TempTable reside in C:\My Documents\BckEnd.mdb. You can change the SQL in PFunc to this:
DELETE TempTable.* FROM TempTable IN "C:\My Documents\BckEnd.mdb"

The IN clause just follows a table name in the FROM clause.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[vandaliarental]

Thanks, Rick! I tried the second method & it does work! I don't think I'm going to continue along this line however. My original thought was to develop a list to run overnight, but I've got so many interlocking databases, I think it's going to be too much of a mess to maintain, with adding all the databases in vb, Tools, Reference & trying to link the tables that need be linked. I think I'll just stick to my original method by scheduling it with Microsoft Scheduler.

Thanks again for all your help! I truly learned a lot!

Connie