How to refer to objects in referenced db?

[burma]

In db1 I add a reference to db2. db2 has a sub named Test1 and a form named Form1. How do I refer to Test1 and Form1 from VBA in db1? Thx!

 

[The_Doc_Man]

If DB2 is an open application object, then you can call DB2.Test1 and can try to open DB2.Form1 from your DB1. All "external object" references follow that rule in VBA.

 

[burma]

So you're saying that db2 has to be open in order to refer to anything in it? No way to refer to its objects after simply adding a reference to it? Thx!

 

[The_Doc_Man]

For references to something that is passive in a back-end file, you can use the SQL ... FROM xxx IN path clause to specify a file and location. That ONLY works for tables as far as I know, because they are accessed using Server Message Block protocol, which is a file-sharing protocol. The only syntax for that location that doesn't require opening the file first is via SQL. (Which WILL implicitly open and later close the file for you.)

IN clause (Microsoft Access SQL)

Office VBA reference topic

learn.microsoft.com

If an item is potentially active (queries, forms, reports, code) then you have to "persuade" it to become instantiated, which involves opening the file that contains that stuff. I believe that you need to open the database for anything else. Otherwise there is no visibility of content.

Adding a "reference" to an Access file might be trickier than you think because the VBA >> Tools >> References control form only shows you files of a specific subset of file types, and I don't recall that .ACCDx or .MDx qualifies for that kind of reference. If my other colleagues know of such a case, they will jump in to correct me, but I think what I said is more or less right.

 

[burma]

For references to something that is passive in a back-end file, you can use the SQL ... FROM xxx IN path clause to specify a file and location. That ONLY works for tables as far as I know, because they are accessed using Server Message Block protocol, which is a file-sharing protocol. The only syntax for that location that doesn't require opening the file first is via SQL. (Which WILL implicitly open and later close the file for you.)

If an item is potentially active (queries, forms, reports, code) then you have to "persuade" it to become instantiated, which involves opening the file that contains that stuff. I believe that you need to open the database for anything else. Otherwise there is no visibility of content.

Adding a "reference" to an Access file might be trickier than you think because the VBA >> Tools >> References control form only shows you files of a specific subset of file types, and I don't recall that .ACCDx or .MDx qualifies for that kind of reference. If my other colleagues know of such a case, they will jump in to correct me, but I think what I said is more or less right.

Thanks for that Doc_Man. I've used the IN clause before but I'm trying to see if I a referenced code library would be useful to me. Anyone know of an example showing how it could be used? My version of Access, 2021 MSO (Version 2409 Build 16.0.18025.20030) 32-bit, does allow me to add an external accdb as a reference. Thx

 

[MarkK]

Yes, you can absolutely set a reference to another access database without the referenced file being open. Any public sub in any standard module is then visible in the application that holds the reference.

Forms in the referenced file are not directly visible, and cannot be opened using DoCmd.OpenForm from the file that holds the reference, but because you can run any public method in any standard module, your referenced file can expose a method that opens a form, or returns a reference to a form. In this way you can indirectly open a form in a referenced database. Any form opened using this technique is added to the Application.Forms collection in the host application, just like normal.

You can expose classes from a referenced database, but you need to open the properties window, select the class, and change it's Instancing property from "Private" to "Public Not Creatable." Then, similar to the case with forms, you need to expose a public method that returns a new instance of the class.

You can define and expose an interface from a referenced file. Change it's instancing from "Private" to "Public Not Creatable," and then you can freely use it with the Implements statement in the host application. Because you don't instantiate an interface, you don't need a function to return an instance.

 

[MarkK]

My version of Access 2021 (Build 16.0.18025.20030) does allow me to add an external accdb as a reference.

How does it not allow you? I assume you mean not.

In a code window main menu, click Tools->References to open the references dialog. Click the Browse button to open a Windows file selector. By default, this selector filters for Type Libraries *.olb, *.tlb, and *.dll, but you can change this filter to *.accdb, *.accde, *.mdb, *.mde, *.mda, etc... Change the filter to the MS Access file type you want to reference, and select a file. Click the Open button, and you will have set a reference to another MS Access application.

Now, open the object browser, find the file you just referenced, and explore what is visible.

I make extensive use of this feature. Any code I write that is even slightly generic, that I might want to use at a different site, I put in a library file. Selector forms, progress bars, popup menu tools, any infrastructure not directly related to customer data, I reference from a library.

 

[burma]

How does it not allow you? I assume you mean not.

Doc_Man mentioned it might not be possible but I myself am able to add an accdb.

I just discovered this which is interesting:

In the db1 Immediate window I can go db2.Form_Form1.Visible = True and it opens the form. But if I put that in a module, it doesn't

 

[sonic8]

Adding a "reference" to an Access file might be trickier than you think because the VBA >> Tools >> References control form only shows you files of a specific subset of file types, and I don't recall that .ACCDx or .MDx qualifies for that kind of reference.

Other than .accda not being available as an explicit file extension, there are all common Access file types listed in the Add Reference file dialog.

 

[sonic8]

In the db1 Immediate window I can go db2.Form_Form1.Visible = True and it opens the form. But if I put that in a module, it doesn't

The VBA development environment has a higher level view on all the opened projects, including referenced ones. You can even open and view(*) the code in the modules of the referenced file, including form class modules.
So, the context of the Immediate Window is not the same as in a module in your primary database project.

*= You can even edit code in the referenced library file, but you cannot save your changes.

 

[GPGeorge]

For years, I've run an external accdb as a code repository for common procedures, e.g. my error handler. It's set as a reference to a shared copy of the library accdb. I recently added a second accdb for functions related to handling json, which contains the modules from Tim Hall's GitHub

This method avoids importing all those modules into a new accdb each time I create one. I set the references and have all of the functions and subs available, as needed.

As Philipp pointed out, I can even edit code in one of the referenced libraries for trouble-shooting without having to close the source accdb and open the library accdb. Once the issue is resolved, I can then go modify to the library, as needed, or abandon the changes without bothering the library.

As long as there is not overlap of names, in fact, I can call functions without qualifying where they are. GlblErrMsg resides in the external referenced accdb:

errHandler:

    Call GlblErrMsg( _
        sFrm:="ModDuplicateMeal", _
        sCtl:="cboSelectaFood_AfterUpdate " _
        )
    Resume Cleanup
    Resume

I'm sure there would be additional considerations were I to deploy an approach like this to a production environment, such as making sure that all user Front Ends referenced the same common library accdb appropriately. For my purposes, on a small workgroup, it works just fine.

 

[MsAccessNL]

Public Sub OpenOtherDbForm(sFormName As String)


    'sFormName = "Form_frmMain" 'de name as shown in vba code Explorer
    'Database6 is the name of database shown in vba code explorer
    'need te set a reference to the other database, Database6 in this case
    'need reference to Microsoft Visual Basic for...
    
    'Debug.Print VBE.ActiveVBProject.Name
    VBE.VBProjects.Item("Database6").VBComponents(sFormName).Activate
    'Debug.Print VBE.ActiveVBProject.Name
    RunCommand (acCmdFormView) 'from design view to formview
 
End Sub

On other option is the make a public function in the referenced db, to open a form. You can call this function from the other db.

Public Sub OpenOtherDbForm(sFormName As String)
    'module M_OpenForm with function OpenFormDb'
    Database6.M_OpenForm.OpenFormDb (sFormName)
End Sub

''------------in Database6--------------
'module M_OpenForm
Public Sub OpenFormDb(sFormToOpen As String)
    DoCmd.OpenForm sFormToOpen
End Sub

 

[DickyP]

Yes, you can absolutely set a reference to another access database without the referenced file being open. Any public sub in any standard module is then visible in the application that holds the reference.
...
You can expose classes from a referenced database

....

The simple way to expose classes in referenced databases - basically what I do with those in my Library database is in the database to be references add the code module below and run fClassMultiUse before before use.

Can't remeber wherte the code came from but I've used it for years. I certainly didn't invent clver stuff like this.

Option Compare Database
Option Explicit

Public Function fClassMultiUse() As Boolean
On Error GoTo Err_fClassMultiUse
    Dim blRet As Boolean, i As Integer
    
    With VBE.ActiveVBProject.VBComponents
        For i = 30 To .Count
            If .Item(i).Type = 2 Then               ' Class module (vbext_ct_ClassModule)
                With .Item(i).Properties.Item(2)    ' "Instancing"
                    If .Value <> 5 Then .Value = 5
                End With
            End If
        Next i
    End With
    blRet = True
Exit_fClassMultiUse:
    fClassMultiUse = blRet
    Exit Function
Err_fClassMultiUse:
    Select Case Err.Number
    Case Else
        MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
               "Description: " & Err.Description & vbNewLine & vbNewLine & _
               "Procedure: fClassMultiUse" & vbNewLine & _
               "Module: basUtility", , "Error: " & Err.Number
        Resume Exit_fClassMultiUse
    End Select
End Function

 

[MarkK]

I'm sure there would be additional considerations were I to deploy an approach like this to a production environment, such as making sure that all user Front Ends referenced the same common library accdb appropriately.

I distribute my Library file as a companion to the FE, so each user has his own copy.

 

[The_Doc_Man]

Well, I have learned something today. Thanks, colleagues, for pointing out that this can be done. Just a side question... has that ability been present for a long time? I didn't think it was available in earlier versions of Access.

 

[MarkK]

has that ability been present for a long time?

The oldest code I can find in my Library file was written in 2004, so it's not super new.

 

[GPGeorge]

Well, I have learned something today. Thanks, colleagues, for pointing out that this can be done. Just a side question... has that ability been present for a long time? I didn't think it was available in earlier versions of Access.

I don't recall when I first started doing so. It has code in it from 2010, so that may be close to the beginning for my use. I never tried with an mdb. I suspect it would be possible. Maybe it's a good time to start up an old VM and find out.

 

[burma]

Thanks y'all for what you've shared - it gives me a lot to work with

 

[xavier.batlle]

Perhaps this old thread on this forum can help you with some examples. Search and look up this threat:
"Opening a form in a library and retrieving a returned value"

 

[burma]

Perhaps this old thread on this forum can help you with some examples. Search and look up this threat:
"Opening a form in a library and retrieving a returned value"