How to run code in one database from another database

Basically, create a Macro in the BE that calls the code Function you want to run.

Then, in the FE ... use the OpenDatabase method to open the BE, then use DoCmd.RunMacro "MacroName" to run that macro in the BE.

mx

Or
In the Visual Basic Editor, go to Tools > References. Click on browse, select the other DB. The modules of the other DB are now available in this DB. Publicity declared procedures and variables will be available. You will not see the forms and reports it the main database container window so you use DoCmd.OpenForm and DoCmd.OpenReport to open the forms and reports.  You can reference the objects through VB as if they are in this database.

Hi mx,
<use DoCmd.RunMacro "MacroName" to run that macro in the BE>
This won't work - Access will look for 'MacroName' in the FE (?)

Hi nelson,
I'd prefer not to set References; I think there is another way, just can't remember it.

See my post here @ http:#a25855412

I tested this and it works.

mx

(ps ...  there is a way to do it with a macro, but I can remember how off hand. And this directly runs code.  Check the Help file also on the Run command to see how to pass arguments if necessary)

Maybe I am missing something here, but why would you want to run BE code from the FE?

Won't the code actually run on the machine running the FE anyway, not on the machine running the BE? ...so there won't be any stored-procedure-like performance advantage to calling the code remotely.

That is, since any table or query references in the code will still require the table to be linked and a local copy of any queries, wouldn't it be simpler to just copy the code to the FE?

I cannot think of a reason to do this other than using the BE as a central repository for code without having to deploy that code with the FE; but in this case, the developer needs to go to all the work to reference the code in the BE--requiring a redistribution of the FE anyway.

"Won't the code actually run on the machine running the FE anyway,"
No.

"so there won't be any stored-procedure-like performance advantage to calling the code remotely"
You can for example, run an action query (eg an Update query) in the external mdb.

"That is, since any table or query references in the code will still require the table to be linked and a local copy of any queries"
That is not the case.

mx

For example ... lets say in the remote mdb you have this:

An update query named  qryUpdA:

UPDATE tblA SET tblA.FIELD = "joe"
WHERE (((tblA.FIELD)="jammer"));

and a table named tblA

and a Function like so:

Public Function mCalledFromAnotherMDB() As Boolean

    CurrentDb.Execute "qryUpdA", dbFailOnError

End Function

Meanwhile back in the 'local' mdb ... you can run this code, and the update query will run in the remote mdb and update the table.

Public Function aaaTest2() As Boolean

Dim appAccess As New Access.Application
appAccess.OpenCurrentDatabase ("SomeFolder\SomeMdbName.mdb")
appAccess.Run "mCalledFromAnotherMDB"
       
End Function


mx

Now I am confused. I understand that the effect of a particular procedure may be in a remote database, but I was unaware that Access could actually make use of the host (BE) server's RAM & processor to process the request.

I was under the impression that the actual processing used the RAM & processor of the FE machine originating the call of the code at runtime, not the resources of the remote machine, as would be the case in a client-server application. Often, I store my BEs on servers not even having Access installed, so how does the processing of any code occur using the processor and RAM of the BE host machine and not on the FE machine that is calling the code?

Sorry ... you are correct on that note.

mx

So, I return to my original comment. I was just trying to see if there was another way to look at, or possibly redefine, the underlying question: while it may be possible to reference code in the BE, the reference to this code would require a redistribution of the FE anyway, so wouldn't it just be simpler to deploy that portion of BE code as part of the FE.

I did not intend to confuse this thread. I was just hoping to cut back into the question one layer in an attempt to make the user's FE distribution process possibly one step simpler.

Guess we will have to see what the intent is here ...

mx

Hi HartCraft,
<Maybe I am missing something here, but why would you want to run BE code from the FE?>
Understandable question; this is an unusual application. I use a procedure to clean up database object properties (eg delete filter and sort properties from queries and tables, change SubDataSheet properties to [None]). I can't access these properties for tables in the BE (since they're linked tables), so the procedure has to run in the BE.

Hi Mx,
Your second solution is exactly what I was after. Thanks!

So, we're good then ?

mx

As an FYI, you can access properties of tables in other databases; you just have to create an Object reference to the object in the other DB (BE). I distribute patches to back ends where I do not have access to the back end to make changes directly. These patches make structural changes such as adding tables, adding fields, creationg relationships, etc.

Rather than building all the patch code into the users' front end, I treat my patch as a custom FE that is deployed as an upgrade patch and create/remove table links programmatically as needed to make changes to table structures, etc. See attached code snippet for an example of how to rename a field in a remote DB.

If you need more, I can post the complete code set that can do all of this in a remote DB:
Create & rename tables
Create & rename fields
Add field properties
Create & delete indexes
Copy tables from the patch to the BE
Run queries against tables in the BE

Private Sub FieldRename()
'dim variables
    Dim db As DAO.Database
    Dim FilePath as String
    Private tblName As String
    Private fldName As String
    Private fldName2 As String
'set variable values
    FilePath = "C:\My File Place\MyAccessDB.mdb"
    tblName = "Table1"
    fldName = "Field1"
    fldName2 = "Field1NewName"
'rename field
    Set db = DBEngine.Workspaces(0).OpenDatabase(FilePath, True)
    db.TableDefs(tblName).Fields(fldName).Name = fldName2
    db.TableDefs.Refresh
End Sub

Select allOpen in new window

Hi HartCraft,
<I do not have access to the back end to make changes directly. >
If you don't have access to the BE, how can your code (running under your login, I assume) access the BE?

In cases where I have deployed a BE to a customer where I do not have remote access to their system, or in cases where I have remote access but the systems have only Access runtime (which does not allow access to the database window, table & query design view, etc), I can still make changes to the database structure without having to download a copy of their BE, taking it out of production while I, for example, add a field to a table.

I just send them a copy of the patch, which allows them to browse for & select the BE, then applies updates to the DB structure, or I FTP a copy to a server, then run the patch myself in Access runtime . The most common scenario is the adding of tables, fields, and relationships as the growth in database logic requires.

I can, in this way, deploy applications, including updates that affect the structure of the BE, without having to purchase a single copy of Access for the client, since the runtime is free. If you have unrestricted access to the entire environment, you may never need these tools, but this scenario can become required in the deployment of updates to multiple clients outside a local network.

Hi Hartcraft,
Interesting tip. Thanks.