Listing Modules for current and remote database

[tani1978]

The code given below lists the modules in the current opened database. I have a textfield where i can chosse any mdb file from my computer. I want that now the selected mdb file should also be treated and should result in listing of the modules.At the end of the post I have posted the code for selecting a mdb file.

Private Sub Command27_Click()

On Error GoTo Err_Command27
    Dim lngCounterA As Long, lngCounterB As Long, lngCounterC As Long
    Dim modModule As Module
    Dim zahl    ' das ist Dein Zähler
    Dim zahl1
    Dim zahl2

    For lngCounterA = 0 To Modules.count - 1
        Set modModule = Modules.Item(lngCounterA)
        zahl = 0
        With modModule
            For lngCounterB = 1 To .CountOfLines
                If Trim(.Lines(lngCounterB, 1)) = "EOF" Then
                   ' .ReplaceLine lngCounterB, "Washington"
                    zahl = zahl + 1
                End If
            Next lngCounterB
            Debug.Print "EOF kam im Modul " & modModule & "   " & zahl & " mal vor."
            zahl1 = 0
            For lngCounterC = 1 To .CountOfLines
                If Trim(.Lines(lngCounterC, 1)) = "Recordset" Then
                   ' .ReplaceLine lngCounterC, "Washington"
                    zahl1 = zahl1 + 1
                End If
            Next lngCounterC
        End With
        Debug.Print "Recordset kam im Modul " & modModule & "   " & zahl1 & " mal vor."
      
    Next lngCounterA
    
Exit_Command27:
    Exit Sub
Err_Command27:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_Command27
End Sub

Private Sub ListModules_Click()

On Error Resume Next
    Dim obj As Object
    Dim I As Integer
    Dim j As Long
    Dim RetVar As Variant
    
    'For Each obj In CurrentProject.allforms
    '   DoCmd.OpenForm obj.Name, acDesign
    '   If Forms(obj.Name).HasModule = True Then
    '       AllProcs ("Form_" & obj.Name)
    '   End If
    'Next
    
    'For Each obj In CurrentProject.AllReports
    '   DoCmd.OpenReport obj.Name, acDesign
    '   If Reports(obj.Name).HasModule = True Then
    '       AllProcs ("Report_" & obj.Name)
    '   End If
    'Next
    
    For I = 0 To CodeDb.Containers("Modules").Documents.count - 1
        RetVar = AllProcs(CodeDb.Containers("Modules").Documents(I).Name)
    Next I

End Sub

Sub UpdateList()
  Dim strPath As String
  Dim strX As String
  Dim strFList As String
  
  On Error Resume Next
  strPath = AddSlash(Me.ImpZIPEingang)
  If Err <> 0 Then Exit Sub   
  strX = Dir$(strPath & "*.mdb")
  While strX <> ""
    strFList = strFList & strX & ";"
    strX = Dir$()
  Wend
  Me.lstImpZIPs.RowSource = strFList
  
End Sub

 

[scking]

Your main issue will be instantiating an object for the remote database. I've done this previously to verify versions between the front-end and back-end. Since I am unaware of your configuration or database versions I'll simply post the important aspect of the code and you can then connect. It uses DAO which could probably co-exist with ADO as long as you dimensioned objects with the DAO and ADODB prefixes.

' Return reference to default workspace.
Set Wsp = DBEngine.Workspaces(0)
For i = 0 To CodeDb.TableDefs.Count - 1
If CodeDb.TableDefs(i).Connect <> "" Then
pstrSAppName = CodeDb.TableDefs(i).Connect
pstrSAppName = Mid$(pstrSAppName, InStr(1, pstrSAppName, "DATABASE=") + 9)
If InStr(1, pstrSAppName, ".mdb") Or _
InStr(1, pstrSAppName, ".mde") Or _
InStr(1, pstrSAppName, ".mda") Or _
InStr(1, pstrSAppName, ".mdw") Then
If Len(pstrSAppName) > 0 Then
Set pdbServer = Wsp.OpenDatabase(pstrSAppName)
'Set pdbServer = wsp.OpenDatabase(pstrSAppName)
pstrSAppVersion = pdbServer.Properties("AppVersion")
pstrSAppRelease = pdbServer.Properties("AppRelease")
GoTo Continue
End If
Else
pstrSAppName = ""
End If
End If
Next i

---------------------
scking@arinc.com
---------------------

 

[tani1978]

Hello can you tell me where i should insert your piece of code. I am not able to understand the logic of your code as I am not a very good in VBA.

 

[MichaelRed]

look inton the use of the MSys* tables. Lik=nk the MSysObjects (T%able) from any/all databases you desire. Create Querie(s) in your favorite media to extract the info you desire. The various object types are easily deciphered from the context of a (robust) database's MSysObjects table. Code to this is tedious, while the quer(y | ies) can easily be built by most anyone.



MichaelRed