By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

Library Databases

PhilOfWalton
Expert 100+
P: 1,430
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
Dec 29 '17 #1
Share this Question
Share on Google+
4 Replies


gnawoncents
100+
P: 214
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCountForms_Click()
  2.  
  3. Dim db As Database
  4. Dim cnt As Container
  5. Dim doc As Document
  6. Dim intMain As Integer
  7. Dim intLib1 As Integer
  8. Dim intLib2 As Integer
  9.  
  10. 'Count the number of forms in the main/current database and save as intMain
  11. intMain = CurrentProject.AllForms.Count
  12.  
  13. 'Count the number of forms in Lib1Db database and save as intLib1
  14. Set db = OpenDatabase("YOUR Lib1Db DATABASE PATH HERE")
  15.  
  16. For Each cnt In db.Containers
  17.     If cnt.Name = "Forms" Then
  18.         For Each doc In cnt.Documents
  19.             intLib1 = intLib1 + 1
  20.         Next doc
  21.     End If
  22. Next cnt
  23.  
  24. 'Count the number of forms in Lib2Db database and save as intLib2
  25. Set db = OpenDatabase("YOUR Lib2Db DATABASE PATH HERE")
  26.  
  27. For Each cnt In db.Containers
  28.     If cnt.Name = "Forms" Then
  29.         For Each doc In cnt.Documents
  30.             intLib2 = intLib2 + 1
  31.         Next doc
  32.     End If
  33. Next cnt
  34.  
  35. Set db = Nothing
  36.  
  37. End Sub
  38.  
Dec 29 '17 #2

NeoPa
Expert Mod 15k+
P: 31,487
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.
Expand|Select|Wrap|Line Numbers
  1. Private intCount As Integer
  2.  
  3. Private Sub btnCountForms_Click()
  4.     'Count the number of forms in the main/current database.
  5.     intCount = CurrentProject.AllForms.Count
  6.     intCount = intCount + _
  7.                CountForms(strPath:="YOUR Lib1Db DATABASE PATH HERE" _
  8.                         , strEncPW:="Optional Encrypted Password")
  9.     intCount = intCount + _
  10.                CountForms(strPath:="YOUR Lib2Db DATABASE PATH HERE" _
  11.                         , strEncPW:="Optional Encrypted Password")
  12. End Sub
  13.  
  14. 'CountForms() returns the number of forms in the database of strPath.
  15. '             If an encrypted password is passed then it's used.
  16. Private Function CountForms(strPath As String _
  17.                           , Optional ByVal strEncPW As String="") As Integer
  18.     Dim strConnect As String
  19.  
  20.     strConnect = Replace("MS Access;PWD=%PW;DATABASE=%DB" _
  21.                        , "%PW", Decrypt(strEncPW))
  22.     strConnect = Replace(strConnect, "%DB", strPath)
  23.     strConnect = Replace(strConnect, ";PWD=;", ";")
  24.     'To open a database with a password the 'optional' parameters are mandatory.
  25.     With OpenDatabase(dbName:=strPath _
  26.                     , Options:=False _
  27.                     , Read-Only:=True _
  28.                     , Connect:=strConnect)
  29.         CountForms = .Containers("Forms").Documents.Count
  30.         Call .Close()
  31.     End With
  32. 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 :-)
Dec 29 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1. Function OpenFrm(FrmName, Optional Vew, Optional Filtr, Optional Whre, Optional Datmode, Optional WindMode, Optional Args)
  2.  
  3.     On Error GoTo OpenFrm_Error
  4.  
  5.     If IsMissing(Args) Then
  6.         Args = vbNull
  7.     End If
  8.  
  9.     DoCmd.OpenForm FrmName, Vew
  10.  
  11.     Exit Function
  12.  
  13. OpenFrm_Error:
  14.     If Nz(Args) = "Application Quit" Then
  15.         Resume Next
  16.     Else
  17.         Msgbox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenFrm of form " & FrmName
  18.     End If
  19.  
  20. End Function
  21.  
In the main Database debug window, typing OpenFrm "MyForm", AcDesign works fine, but what happens if both library databases have a "MyForm"

Phil
Dec 29 '17 #4

NeoPa
Expert Mod 15k+
P: 31,487
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.
Dec 29 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.