Get the VBProject of a Database

Question

Given a database object in MS Access VBA, how can one get that database's VBProject?

Function GetVBProject(ByVal db As Database) As VBProject
    Set GetVBProject = ???
End Function

The only way I know how to get VBProjects in Access is through Application.VBE.VBProjects.Item(???). However, I won't know what order of the projects are in and what the name is. I will only know it's parent database. The equivalent in Excel would be simply

Function GetVBProject(ByVal wb As Workbook) As VBProject
    Set GetVBProject = wb.VBProject
End Function

Intoxicative

Answer 1

Look in the VBProjects collection and check each project's FileName property. If a project's FileName is the current database file (CurrentDb.Name), that is the one you want.

Public Function ThisProject() As String
    Dim objVBProject As Object
    Dim strReturn As String
    For Each objVBProject In Application.VBE.VBProjects
        If objVBProject.FileName = CurrentDb.Name Then
            strReturn = objVBProject.Name
            Exit For
        End If
    Next
    ThisProject = strReturn
End Function

That function returns the project name. You could use the name to set a reference to the VBProject object. Or you could revise the function to return the VBProject instead of a string.

I barely tested this, so I'm uncertain objVBProject.FileName = CurrentDb.Name will be the correct test condition for every situation. But I hope this answer gives you something useful to build on.

I looked into objVBProject.FileName vs. CurrentDb.Name when the db is opened from a drive letter and from a UNC path to a network share. Either way, it seems objVBProject.FileName and CurrentDb.Name both "self-adjust" and still match each other:

' db opened from a drive letter ...
? CurrentDb.Name
C:\share\Access\BigDb_secure.mdb
? application.VBE.VBProjects("BigDb_secure").FileName
C:\share\Access\BigDb_secure.mdb

' db opened from UNC path to network share ...
? CurrentDb.Name
\\HP64\share\Access\BigDb_secure.mdb
? application.VBE.VBProjects("BigDb_secure").FileName
\\HP64\share\Access\BigDb_secure.mdb

Lucubrate

Using the filename. Smart. ++ – Radiosonde

It won't be correct if the Access file is on a relative path. CurrentDb.Name will use the drive letter for a mapped drive but VBProject.FileName will return the network path. – Intoxicative

@Intoxicative Not sure what you meant by relative path. Are you talking about something different from the test cases I added to the answer? – Lucubrate

Interesting, thanks. I didn't expect it to behave differently with a samba share. In that case I suppose you could check whether Dir(...FileName) matches Dir(CurrentDB.Name) That would only break if you had another project reference to a db file whose name matches the name of the current db. – Lucubrate

Answer 2

It may be kind of late to answer this but if it is the current project you want to address then

Set vbProj = VBE.ActiveVBProject

will work.

Carport