Can different databases share a common module?

[haggis999]

I have been away from Access programming for the best part of a year and my brain has gone a little rusty. My primary Access 2003 application has three separate MDB files: a user oriented front end, a back end data repository and another data store that handles the transfer of data from an online SQL Server database. It has just become clear to me that it would be very useful if all three MDB files could access the same public function that returns a integer value of significant relevance to my application. At present, this public function is duplicated in a module within each MDB file and each of them requires the same annual update.

Hence my question: is there a way for all three MDB files to reference a common module?



David

[June7]

From Access VBA Help:

You can use public procedures, variables, and constants defined in standard modules or class modules from referencing projects. However, you must first set a reference to the project in which they are defined.

Google: access vba call public sub in another database
Review http://msgroups.net/microsoft.public...ba-code/121477

[haggis999]

Hi June7,
Thanks for the suggestion. I had a go at using Application.Run but with mixed success.

I have a database CompDataFE2.mdb that contains a module called MyModule. Within that module there is the following public function,

Public Function intCurrentCompID() As Integer
intCurrentCompID = 6

End Function

In another Access database, I set up a test form with a single textbox. The Form Open event contains the following code,

Private Sub Form_Open(Cancel As Integer)
Me![txtCompID] = Application.Run("...\MS Access\CompData\CompDataFE2.intCurrentCompID()")

End Sub


The first time I tried to open my test form, I hit a few errors as the path to my database was not typed correctly but I eventually got it to work on one occasion, i.e. the textbox displayed the number 6, just as I had hoped. As far as I am aware the working version was as shown above. Unfortunately, all future attempts to open the form generate the error "Microsoft Office Access can't find the procedure '...\MS Access\CompData\CompDataFE2.intCurrentCompID()'". I assume that I am suffering from some form of finger trouble that has messed things up but I'm struggling to find where I have gone wrong. The path I am now using looks fine.

Any suggestions?

David

[June7]

I can get the Application.Run method to work if it calls procedure in the active database. Using the example in the thread, I have: Application.Run("test"). That works everytime. Can't make it find the external function directly with the path reference.

[haggis999]

I think the time I got it to work must have been when I used the other method mentioned in your link, using the following code in my form Open event,

Dim objAccess As New Access.Application
objAccess.OpenCurrentDatabase "...\MS Access\CompData\CompDataFE2.mdb"
Me![txtCompID] = objAccess.Eval("intCurrentCompID()")

The only problem with this method is that it opens CompDataFE2 in a second blank Access window (CompDataFE2 was already open) and then prevents me making any changes in that database. I get errors as I no longer have exclusive access. What is the best way of handling this situation?

[June7]

With the example the file closes after responding to the message box popup.

So maybe close the second instance of db:
Set objAccess = Nothing

[ssanfu]

I've been wondering this also. I have duplicated code in a main FE and an import FE (common BE). In Excel, I have used an add-in to add custom menus/processing code. So maybe Access has add-ins also.

I found three links (so far):

1) Microsoft: http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx
There is info about Access add-ins and a COM add-in.

2) Tony Toews: http://www.granite.ab.ca/access/addins.htm

3) databasedev.co.uk : http://www.databasedev.co.uk/access-add-ins.html
"Creating a Simple Microsoft Access Add-In"


Lots more reading to do.....

[haggis999]

With the example the file closes after responding to the message box popup.

So maybe close the second instance of db:
Set objAccess = Nothing

I had already tried using objAccess.CloseCurrentDatabase but that had no effect. However, Set objAccess = Nothing does the job of releasing the exclusive access to the referenced external database. Thanks for that.

That leaves me with one problem. Every time I open my test form, I get a blank Access window flashing up on my desktop for a fraction of a second. Is there a way to make this external reference operate in a 'quieter' fashion?

[haggis999]

I've been wondering this also.....

Lots more reading to do.....

Hi Steve,
Please post back here if you ever find an alternative solution in that mass of techie documentation! I looked briefly at all your links but a lot of it was either above my head or not obviously related to my search for a slick way to reference a function located in another database.

David

[orange]

This is how it's done (at least I have mine set up) in 2003. Here's a link I found that sort of describes it.
I set it up a long time ago.

http://www.perfectparadigm.com/tip009.html

[haggis999]

I appear to have stumbled across a simple solution to my problem. If I just add a reference to the external MDB file in Tools/References then I can access any public function in that MDB file (obviously, you have to be careful to ensure that the external function name is not the same as a local function). The only issue found so far is that you will get a warning if you try to edit any code when both databases are open at the same time.

I'm not sure if June7's original response was intended to convey such a solution but, if so, it passed me by at the time as I just focused on the link provided in that reply.

[haggis999]

This is how it's done (at least I have mine set up) in 2003. Here's a link I found that sort of describes it.
I set it up a long time ago.

http://www.perfectparadigm.com/tip009.html

Our posts crossed in cyberspace. I have yet to try your MDA approach but would appreciate your comments on how it differs from my proposed solution.

[haggis999]

Hi Orange,
I have now tried storing my common function in an MDA file, but the instructions in your link about adding a reference to this MDA file in msaccess.ini don't work for me as I cannot find an msaccess.ini file anywhere on my PC. However, I am able to add a reference via Tools/References, in which case it works just my previous reference to an MDB file.

[orange]

Ok, I just went back through my old emails. Seems I did this back in Acc97 and got some advice when moving to A2K.
But I have just removed my mda Library from my References. And will include some jpg of what I do to add it.
First, I have the .mda file in my
c:\Windows\system32 folder. The database is named jGold01_A2K.mda

I deleted the reference to the file. Then in jpgs 1-5 added the library back.
In the 4 and 5 jpgs, you will see the reference in the project window on the left, and then I clicked the Project1 + and expanded to show modules in the Library mda.

Good luck.

PS -- I don't have an msaccess.ini either, so I guess there are several ways to accomplish this.

[haggis999]

As far as I can see from your images, you used 'Tools/References...' in the VBA window to select your MDA file as a new reference. That is the same method I used.

However, I am still unsure what benefit I have gained by changing my new library database to use an MDA extension instead of MDB.