Call VBA Code from BE in FE 2

[kkitt]

Is it possilble to call a VBA routine that is in the BE from a form in the FE or does the code need to reside in both (creating a maintenance nightmare).

Little background:

I have some routines that are run at night that import data and do some data edits before the users come in. Since that have no need to access these tables/queries/VBA code, I left this in the backend (making the FE smaller). Now I have to create a form that will need some of the data edit routines that are in the backend and I want to access this code without importing into the frontend, is this possible and not create any major network issues?

 

[mp9]

Can't you just attach to the BE module from within the FE, thereby making it's contents available?

I haven't tried this, to be fair, but it seems logical.

http://www22.brinkster.com/accessory/

 

[kkitt]

True, I could probably open a DB connection to the BE, but how would you then call the routine from within the Form's Code? I do not see any DB methods that allow that.

 

[mp9]

Sorry, I've misinformed you - you can only attach to tables. Other database content, including modules, can only be imported, which kind of doesn't help your problem of not wanting the code in two places.

http://www22.brinkster.com/accessory/

 

[cmmrfrds]

I thought I saw an example a couple of months ago on the Forum on how to call a function in the back end module. Sorry, I didn't keep the thread number.

 

[PHV]

References the BE:
When in VBE, menu Tools -> References ...

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

 

[mp9]

Found this in Access help which I think is what you want:

You can set a reference from a Visual Basic project in one Microsoft Access database to a project in another Microsoft Access database, a library database, or an add-in contained in an .mde file. Once you've set a reference, you can run Visual Basic procedures in the referenced project. For example, the Northwind sample database includes a module named Utility Functions that contains a function called IsLoaded. You can set a reference to the project in the Northwind sample database from the project in the current database, and then call the IsLoaded function just as you would if it were defined within the current database.

To set a reference to the project in the Northwind sample database from another project:

1. Open the Module window.
2. On the Tools menu, click References, and click Browse in the References dialog box.
3. In the Files Of Type box, click Microsoft Access Databases (*.mdb).
4. Locate the Northwind.mdb file. If you've installed this file, it will be in the \Program Files\Microsoft Office\Office\Samples folder by default.
5. Click OK.

You should now see "Northwind.mdb" in the list of available references in the References dialog box.

Notes

- Set a reference to the project in another Microsoft Access database when you want to call a public procedure that's defined within a standard module in that database. You can't call procedures that are defined within a class module or procedures in a standard module that are preceded with the Private keyword.

- You can set a reference to the project in a Microsoft Access database only from another Microsoft Access database.

- You can set a reference to a project only in another Microsoft Access 2002 database. To set a reference to a project in a database created in a previous version of Microsoft Access, first convert that database to Microsoft Access 2002.

- If you set a reference to a project or type library from Microsoft Access and then move the file that contains that project or type library to a different folder, Microsoft Access will attempt to locate the file and reestablish the reference. If the RefLibPaths key exists in the registry, Microsoft Access will first search there. If there's no matching entry, Microsoft Access will search for the file first in the current folder, then in all the folders on the drive. You can create the RefLibPaths key by using the Registry Editor in Windows, under the registry key \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\version\Access. For more information about using the Registry Editor, see your Windows documentation.

http://www22.brinkster.com/accessory/

 

[kkitt]

Thanks to PHV and MP9 on the solutions, this is exactly what I needed and works like a charm. I have added the reference to my FE and am able to call the BE routines just like normal.

Quick question on this though, are the routines pulled into the FE at the time I compile to a .mde or accessed in the BE at the time they are called. Looking to see if there will be any negitive network impact with this solution

Stars to you both.

Thanks.

 

[kkitt]

Forgot to also ask, will i need to got to each user machine and add the reference or will it be added automatically once I make the .mde and populate out to the masses?

Thanks again

 

[StarPassing]

I use this code in FE to run procedures in BE:

Dim objAccess As Application

' open a new Access application
Set objAccess = CreateObject("Access.application")
' PathAndDBname = your path and name of BE
' True will make the application visible
objAccess.OpenCurrentDatabase PathAndDBname, True

' call the procedure to run in BE
objAccess.Run "Procedure Name"

' quit and close the new Access application
objAccess.Quit
Set objAccess = Nothing


The path to the BE should be known because it is the same as the linked tables from the BE.


Just another way to do it.

 

[kkitt]

Calling and working with the code being called from the FE that resides in the BE is working like a charm until I need to compile the code to a .MDE.

When I try to compile to a .MDE I get a get an error that this can not be done. After researching I found that if you setup a reference to another MSAccess DB that it also needs to be compiled:

If you try to create an MDE file from a Microsoft Access database (.mdb) or an add-in (.mda) (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) that references another Access database or add-in, Access displays an error message and doesn't let you complete the operation. To save a database that references another database as an MDE file, you must save all databases in the chain of references as MDE files, starting from the first database referenced. After saving the first database as an MDE file, you must then update the reference in the next database to point to the new MDE file before saving it as an MDE file, and so on.

For example, if Database1.mdb references Database2.mdb, which references Database3.mda, you would proceed as follows:

 1) Save Database3.mda as Database3.mde. 
 2)Open Database 2.mdb, and change its reference to point to the new Database3.mde. 
 3)Save Database2.mdb as Database2.mde. 
 4) Open Database1.mdb, and change its reference to point to the new Database2.mde. 
 5)Save Database1.mdb as Database1.mde.

I will look at trying to use Starpassing Solution to see if that will allow me to compile the code as need since I do not want to publish this as the "mdb

 

[kkitt]

Starpassing solution possibly will not work since some of the function defined in the BE are being used in queries unless i make a new function in the FE the opens the DB and then calls the actual function. Seems like a lot of extra codding to get this done and additional network overhead.

Am I missing something???

thinking out loud.... If i put all the function into its own mdb, compile it. I could then use it as a reference in both the BE and FE. Question is that if I make changes to the Utility MDE would i need to recomiple the other to pick up the changes or are they automatic?