Viaual Basic Access Modules

Print all queries in a database to a text file.

Sub QueriesToTextFile()

  Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim fileName As String
    Dim outputFile As Integer

    ' Specify the path and filename for the output text file
    fileName = "C:\Project\Orsanco\ORSANCO_DB_Project\ORSANCO_Databases1\ORSANCO_Bimonthly_queries.txt"

    ' Initialize the database object
    Set db = CurrentDb

    ' Create or overwrite the output text file
    outputFile = FreeFile
    Open fileName For Output As outputFile

    ' Loop through all querydefs in the database
    For Each qdf In db.QueryDefs
        ' Check if the query is not a system query
        If Not qdf.Name Like "~*" Then
            ' Write the query name and SQL to the text file
            Print #outputFile, "Query Name: " & qdf.Name
            Print #outputFile, "Query SQL:"
            Print #outputFile, qdf.SQL
            Print #outputFile, "----------------------------------"
        End If
    Next qdf

    ' Close the output text file
    Close outputFile

    ' Release the objects
    Set qdf = Nothing
    Set db = Nothing

    MsgBox "Queries have been exported to " & fileName, vbInformation, "Export Complete"
End Sub

Print all Modules in a database to a text file.

Sub ExportModulesToTextFile()
    Dim vbComponent As Object
    Dim vbModule As Object
    Dim fileName As String
    Dim outputFile As Integer

    ' Specify the path and filename for the output text file
    fileName = "C:\Project\Orsanco\ORSANCO_DB_Project\ORSANCO_Databases1\ORSANCO_Bimonthly_modules.txt"

    ' Create or overwrite the output text file
    outputFile = FreeFile
    Open fileName For Output As outputFile

    ' Loop through all modules in the database
    For Each vbComponent In Application.VBE.VBProjects(1).VBComponents
        If vbComponent.Type = 1 Then ' Check if it's a standard module
            Set vbModule = vbComponent.CodeModule
            ' Write the module name and code to the text file
            Print #outputFile, "Module Name: " & vbComponent.Name
            Print #outputFile, "Module Code:"
            Print #outputFile, vbModule.Lines(1, vbModule.CountOfLines)
            Print #outputFile, "----------------------------------"
        End If
    Next vbComponent

    ' Close the output text file
    Close outputFile

    ' Release the objects
    Set vbComponent = Nothing
    Set vbModule = Nothing

    MsgBox "Modules have been exported to " & fileName, vbInformation, "Export Complete"
End Sub

Print all Forms in a database to a text file.

Sub ExportFormsToTextFile()
    Dim db As DAO.Database
    Dim form As DAO.Container
    Dim fileName As String
    Dim outputFile As Integer

    ' Specify the path and filename for the output text file
    fileName = "C:\Project\Orsanco\ORSANCO_DB_Project\ORSANCO_Databases1\ORSANCO_Bimonthly_forms.txt"

    ' Initialize the database object
    Set db = CurrentDb

    ' Check if a specific form exists (replace "YourFormName" with the form name you want to check)
    If DoesFormExist("YourFormName") Then
        ' Create or overwrite the output text file
        outputFile = FreeFile
        Open fileName For Output As outputFile

        ' Loop through all forms in the database
        For Each form In db.Containers("Forms").Documents
            ' Write the form name to the text file
            Print #outputFile, "Form Name: " & form.Name
            Print #outputFile, "----------------------------------"
        Next form

        ' Close the output text file
        Close outputFile

        ' Release the objects
        Set form = Nothing
        Set db = Nothing

        MsgBox "Forms have been exported to " & fileName, vbInformation, "Export Complete"
    Else
        MsgBox "The specified form does not exist in the database."
    End If
End Sub

Print all Macros in a database to a text file.

Sub ExportMacrosToTextFile()
    Dim db As DAO.Database
    Dim Macro As DAO.Macro
    Dim fileName As String
    Dim outputFile As Integer

    ' Specify the path and filename for the output text file
    fileName = "C:\Project\Orsanco\ORSANCO_DB_Project\ORSANCO_Databases1\ORSANCO_Bimonthly_macros.txt"

    ' Initialize the database object
    Set db = CurrentDb

    ' Create or overwrite the output text file
    outputFile = FreeFile
    Open fileName For Output As outputFile

    ' Loop through all macros in the database
    For Each Macro In db.Containers("Scripts").Documents
        ' Write the macro name and content to the text file
        Print #outputFile, "Macro Name: " & Macro.Name
        Print #outputFile, "Macro Content:"
        Print #outputFile, Macro.ModuleText
        Print #outputFile, "----------------------------------"
    Next Macro

    ' Close the output text file
    Close outputFile

    ' Release the objects
    Set Macro = Nothing
    Set db = Nothing

    MsgBox "Macros have been exported to " & fileName, vbInformation, "Export Complete"
End Sub

Print all Reports in a database to a text file.

Sub ExportReportsToTextFile()
    Dim db As DAO.Database
    Dim report As DAO.Container
    Dim fileName As String
    Dim outputFile As Integer

    ' Specify the path and filename for the output text file
    fileName = "C:\Project\Orsanco\ORSANCO_DB_Project\ORSANCO_Databases1\ORSANCO_Bimonthly_reports.txt"

    ' Initialize the database object
    Set db = CurrentDb

    ' Create or overwrite the output text file
    outputFile = FreeFile
    Open fileName For Output As outputFile

    ' Loop through all reports in the database
    For Each report In db.Containers("Reports").Documents
        ' Write the report name to the text file
        Print #outputFile, "Report Name: " & report.Name
        Print #outputFile, "----------------------------------"
    Next report

    ' Close the output text file
    Close outputFile

    ' Release the objects
    Set report = Nothing
    Set db = Nothing

    MsgBox "Reports have been exported to " & fileName, vbInformation, "Export Complete"
End Sub

Visual Basic code that gets all the Table Names, Column Names, Data Type, and Description.

Sub GetTableInfoToFileWithTableName()
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As Field
    Dim desc As String
    Dim tableName As String
    Dim colName As String
    Dim colType As String
    Dim fs As Object
    Dim file As Object

    ' Create a FileSystemObject
    Set fs = CreateObject("Scripting.FileSystemObject")

    ' Create a text file for output
    Set file = fs.CreateTextFile("C:\Project\Orsanco\ORSANCO_DB_Project\ORSANCO Databases\TableInfo.txt", True)

    ' Set a reference to the current database
    Set db = CurrentDb

    ' Loop through all tables in the database
    For Each tdf In db.TableDefs
        ' Ignore system tables and views
        If Left(tdf.Name, 4) <> "MSys" And Not tdf.Attributes And dbAttachedTable Then
            tableName = tdf.Name
            ' Loop through all fields (columns) in the table
            For Each fld In tdf.Fields
                colName = fld.Name
                colType = FieldTypeToString(fld.Type)

                ' Check if the field has a description
                On Error Resume Next
                desc = fld.Properties("Description")
                On Error GoTo 0

                ' Write table name, column information to the file
                file.WriteLine "Table Name: " & tableName
                file.WriteLine "  Column Name: " & colName
                file.WriteLine "  Data Type: " & colType
                If Not IsNull(desc) Then
                    file.WriteLine "  Description: " & desc
                End If
                file.WriteLine
            Next fld
        End If
    Next tdf

    ' Close the text file
    file.Close

    ' Clean up
    Set file = Nothing
    Set fs = Nothing
    Set db = Nothing
End Sub

Function FieldTypeToString(fieldType As Long) As String
    ' Converts field type constants to readable strings
    Select Case fieldType
        Case dbText
            FieldTypeToString = "Text"
        Case dbMemo
            FieldTypeToString = "Memo"
        Case dbNumeric
            FieldTypeToString = "Numeric"
        Case dbLong
            FieldTypeToString = "Long Integer"
        Case dbInteger
            FieldTypeToString = "Integer"
        Case dbDouble
            FieldTypeToString = "Double"
        Case dbCurrency
            FieldTypeToString = "Currency"
        Case dbDate
            FieldTypeToString = "Date/Time"
        Case dbYesNo
            FieldTypeToString = "Yes/No"
        Case Else
            FieldTypeToString = "Unknown"
    End Select
End Function

List the Number of Queries that do not run. There names and the error in running them.

Function ExecuteSelectQuery(queryName As String) As Boolean
    On Error Resume Next
    CurrentDb.QueryDefs(queryName).Execute
    If Err.Number = 0 Then
        ExecuteSelectQuery = True ' Query executed successfully
    Else
        ExecuteSelectQuery = False ' Error executing the query
    End If
    Err.Clear ' Clear any error
    On Error GoTo 0
End Function

Sub RunAllQueriesAndLogErrorsToFile()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim errorCount As Integer
    Dim logFile As Integer
    Dim isError As Boolean ' Flag to check if an error occurred

    ' Initialize error count
    errorCount = 0

    ' Set the database object
    Set db = CurrentDb

    ' Open a text file for logging errors
    logFile = FreeFile
    Open "C:\Project\Orsanco\ORSANCO_DB_Project\ORSANCO Databases\Log_file_OF.txt" For Output As logFile

    ' Loop through all queries
    For Each qdf In db.QueryDefs
        isError = False ' Initialize the error flag
        On Error Resume Next
        ' Check if it's a SELECT query
        If Left(qdf.SQL, 6) = "SELECT" Then
            ' Execute SELECT query using custom function
            If Not ExecuteSelectQuery(qdf.Name) Then
                isError = True ' Set the error flag
            End If
        Else
            ' Execute non-SELECT query
            qdf.Execute
            ' Check for errors
            If Err.Number <> 0 Then
                isError = True ' Set the error flag
                errorCount = errorCount + 1
                Print #logFile, "Query Name: " & qdf.Name
                Print #logFile, "Error Message: " & Err.Description
                Print #logFile, "-------------------------"
            End If
        End If
        On Error GoTo 0

        ' Log the error only if isError is True
        If isError Then
            If Err.Description <> "" Then
                ' Log the error to the text file
                Print #logFile, "Query Name: " & qdf.Name
                Print #logFile, "Error Message: " & Err.Description
                Print #logFile, "-------------------------"
            End If
            Err.Clear
        End If
    Next qdf

    ' Close the text file
    Close logFile

    ' Clean up
    Set qdf = Nothing
    Set db = Nothing

    ' Display the number of errors
    MsgBox errorCount & " queries had errors. Error details are logged to the text file.", vbInformation
End Sub