Library Databases

[PhilOfWalton]

Suppose I have a main database and several referenced library databases. Lets's call them MainDb, Lib1Db and Lib2Db.

Assuming I am in the main Db and I want a count of the forms in both Lib1Db & Lib2Db, how do I "address" them.

I can't see that CodeProject.AllForms.Count will work as Access won't know which CodeProject to use.

Thanks

Phil

[gnawoncents]

Phil,

There is likely a more elegant way to do this, but I couldn't find it. Try the code below and please ignore any typos as I tested the code against a database with a different name then modified the code per your naming conventions.

Private Sub btnCountForms_Click()
 
Dim db As Database
Dim cnt As Container
Dim doc As Document
Dim intMain As Integer
Dim intLib1 As Integer
Dim intLib2 As Integer
 
'Count the number of forms in the main/current database and save as intMain
intMain = CurrentProject.AllForms.Count
 
'Count the number of forms in Lib1Db database and save as intLib1
Set db = OpenDatabase("YOUR Lib1Db DATABASE PATH HERE")
 
For Each cnt In db.Containers
    If cnt.Name = "Forms" Then
        For Each doc In cnt.Documents
            intLib1 = intLib1 + 1
        Next doc
    End If
Next cnt
 
'Count the number of forms in Lib2Db database and save as intLib2
Set db = OpenDatabase("YOUR Lib2Db DATABASE PATH HERE")
 
For Each cnt In db.Containers
    If cnt.Name = "Forms" Then
        For Each doc In cnt.Documents
            intLib2 = intLib2 + 1
        Next doc
    End If
Next cnt
 
Set db = Nothing
 
End Sub
 

[NeoPa]

Hi Phil.

Gnawoncents pointed the way but there is a more straightforward approach to getting at the numbers they've found. My version includes databases that have a password just in case (Probably unlikely with a library database but can't hurt). I've worked on the basis that you have a routine to decrypt passwords called Decrypt(). I've also set intCount as a Form-level variable so it can be used by other code.

Private intCount As Integer
 
Private Sub btnCountForms_Click()
    'Count the number of forms in the main/current database.
    intCount = CurrentProject.AllForms.Count
    intCount = intCount + _
               CountForms(strPath:="YOUR Lib1Db DATABASE PATH HERE" _
                        , strEncPW:="Optional Encrypted Password")
    intCount = intCount + _
               CountForms(strPath:="YOUR Lib2Db DATABASE PATH HERE" _
                        , strEncPW:="Optional Encrypted Password")
End Sub
 
'CountForms() returns the number of forms in the database of strPath.
'             If an encrypted password is passed then it's used.
Private Function CountForms(strPath As String _
                          , Optional ByVal strEncPW As String="") As Integer
    Dim strConnect As String
 
    strConnect = Replace("MS Access;PWD=%PW;DATABASE=%DB" _
                       , "%PW", Decrypt(strEncPW))
    strConnect = Replace(strConnect, "%DB", strPath)
    strConnect = Replace(strConnect, ";PWD=;", ";")
    'To open a database with a password the 'optional' parameters are mandatory.
    With OpenDatabase(dbName:=strPath _
                    , Options:=False _
                    , Read-Only:=True _
                    , Connect:=strConnect)
        CountForms = .Containers("Forms").Documents.Count
        Call .Close()
    End With
End Function

Bear in mind, though this should work, it's really for illustrative purposes to show what can be done. You'll probably want to do things a little differently depending on your actual requirement for that value but this should point you in the right direction. Just as Gnawoncents' code did for me :-)

[PhilOfWalton]

Thanks for you very quick response. I tried to keep it simple- obviously a mistake.

The ultimate aim is to open all the Forms & Reports in Design View in Lib1Db & Lib2Db to get information on all their controls. I then need to build a table with database name, form name & information about the controls.

I have used the OpenDatabase "YOUR Lib2Db DATABASE PATH HERE") in the past, ans seem to end up with multiple copies of Access running and the only way to close them is with TaskManager.

I have a feeling that as I can open a library database Form using a function within the library database from the main database that the main database is "aware" of the library database.

So in Lib1Db = have this function

Function OpenFrm(FrmName, Optional Vew, Optional Filtr, Optional Whre, Optional Datmode, Optional WindMode, Optional Args)
 
    On Error GoTo OpenFrm_Error
 
    If IsMissing(Args) Then
        Args = vbNull
    End If
 
    DoCmd.OpenForm FrmName, Vew
 
    Exit Function
 
OpenFrm_Error:
    If Nz(Args) = "Application Quit" Then
        Resume Next
    Else
        Msgbox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenFrm of form " & FrmName
    End If
 
End Function
 

In the main Database debug window, typing OpenFrm "MyForm", AcDesign works fine, but what happens if both library databases have a "MyForm"

Phil

[NeoPa]

In that case I don't know the answer. I don't even have such a database to play with (You could email me one if you like Phil).

OTOH if you go to the Object Browser in the VBAIDE (From Access press Alt-F11 then F2.) and drop down the list which normally says <All Libraries> then you should see a list of references which should include your library databases. If you select that in the Object Browser then you'll see below a list of all its classes and their properties. I didn't see a Forms collection when I looked :-(

Phil:
I tried to keep it simple- obviously a mistake.

That wasn't the mistake. The inaccuracy was the mistake. Simple is always good. It's not always easy to keep the focus in the right place while trying to simplify though.