A2K: Error when opening remote DB to read controls.

[ncjed]

First off, my apologies if this topic has previously been covered.

I am in the process of creating an A2K DB (Win XP) that will open other DBs (either on local drive, mapped drives, or UNC network path). My current goal is to open all forms from the target DB, read through the controls, and if the length of the control's property is > 0 (eliminate nulls and blanks), write the value out to a table on my local DB.

I am using the DBEngine.Workspaces(0).OpenDatabase(DBName) method to do this, as the Access.Application is not exactly what I am looking for (DBs to be catalogued are quite large and have long startup scripts associated. Besides, I just want to peek inside, not run it).

I am able to successfully read through the forms collection, however when attempting to DoCmd.OpenForm(strFormName), it is attempting to open that element on the DB that is running the script (not the target).

My code:

Function ScanObjects(strDBToScan As String)
 
On Error Resume Next
 
    Dim objdb As Database, objDBToScan As Database
    Dim doc As Document
    Dim frm As Form
    Dim strForm As String, strSQL As String
    Dim ctl As Control
 
    'This database
    Set objdb = CurrentDb
 
    'Target database
    Set objDBToScan = DBEngine.Workspaces(0).OpenDatabase(strDBToScan)
 
    'Purge current output table
    objdb.Execute ("Delete * from t_output")
 
    ' Each doc printed is from remote DB, connection is successful.
    For Each doc In objdb.Containers("Forms").Documents
        strForm = doc.Name
        Debug.Print "Processing " & strForm
 
        ' Line below opens the form in the calling database, not the target, as intended.
        DoCmd.OpenForm strForm, acDesign, , , , acHidden
 
        Set frm = Forms(strForm)
 
        For Each ctl In frm.Controls
 
            For Each prp In ctl.Properties
 
                If Len(prp.Value) > 0 Then
                    Debug.Print objdb.Name & "," & strForm & "," & ctl.Name & "," & prp.Name & "," & prp.Value
                    strSQL = "Insert Into t_output (db_name, element_name, ctl_name, prop_name, prop_value, run_date) Values (" & _
                             "'" & Right(objDBToScan.Name, 255) & _
                             "','" & Left(strForm, 255) & _
                             "','" & Left(ctl.Name, 255) & _
                             "','" & Left(prp.Name, 255) & _
                             "','" & Left(prp.Value, 255) & _
                             "'," & Date & _
                             ")"
                    objdb.Execute strSQL
                Else
                    Debug.Print "--> Skipping " & ctl.Name & ", " & prp.Name & ", value of '" & prp.Value & "'"
                End If
 
            Next
 
        Next
 
        DoCmd.Close acForm, strForm, acSaveNo
 
    Next
    Debug.Print "Done."
 
    Set ctl = Nothing
    Set frm = Nothing
    Set doc = Nothing
 
    objDBToScan.Close
    Set objDBToScan = Nothing
 
    objdb.Close
    Set objdb = Nothing
 
End Function
 

Thanks for any assistance that can be provided on this.

[ADezii]

First off, my apologies if this topic has previously been covered.

I am in the process of creating an A2K DB (Win XP) that will open other DBs (either on local drive, mapped drives, or UNC network path). My current goal is to open all forms from the target DB, read through the controls, and if the length of the control's property is > 0 (eliminate nulls and blanks), write the value out to a table on my local DB.

I am using the DBEngine.Workspaces(0).OpenDatabase(DBName) method to do this, as the Access.Application is not exactly what I am looking for (DBs to be catalogued are quite large and have long startup scripts associated. Besides, I just want to peek inside, not run it).

I am able to successfully read through the forms collection, however when attempting to DoCmd.OpenForm(strFormName), it is attempting to open that element on the DB that is running the script (not the target).

My code:

Function ScanObjects(strDBToScan As String)
 
On Error Resume Next
 
    Dim objdb As Database, objDBToScan As Database
    Dim doc As Document
    Dim frm As Form
    Dim strForm As String, strSQL As String
    Dim ctl As Control
 
    'This database
    Set objdb = CurrentDb
 
    'Target database
    Set objDBToScan = DBEngine.Workspaces(0).OpenDatabase(strDBToScan)
 
    'Purge current output table
    objdb.Execute ("Delete * from t_output")
 
    ' Each doc printed is from remote DB, connection is successful.
    For Each doc In objdb.Containers("Forms").Documents
        strForm = doc.Name
        Debug.Print "Processing " & strForm
 
        ' Line below opens the form in the calling database, not the target, as intended.
        DoCmd.OpenForm strForm, acDesign, , , , acHidden
 
        Set frm = Forms(strForm)
 
        For Each ctl In frm.Controls
 
            For Each prp In ctl.Properties
 
                If Len(prp.Value) > 0 Then
                    Debug.Print objdb.Name & "," & strForm & "," & ctl.Name & "," & prp.Name & "," & prp.Value
                    strSQL = "Insert Into t_output (db_name, element_name, ctl_name, prop_name, prop_value, run_date) Values (" & _
                             "'" & Right(objDBToScan.Name, 255) & _
                             "','" & Left(strForm, 255) & _
                             "','" & Left(ctl.Name, 255) & _
                             "','" & Left(prp.Name, 255) & _
                             "','" & Left(prp.Value, 255) & _
                             "'," & Date & _
                             ")"
                    objdb.Execute strSQL
                Else
                    Debug.Print "--> Skipping " & ctl.Name & ", " & prp.Name & ", value of '" & prp.Value & "'"
                End If
 
            Next
 
        Next
 
        DoCmd.Close acForm, strForm, acSaveNo
 
    Next
    Debug.Print "Done."
 
    Set ctl = Nothing
    Set frm = Nothing
    Set doc = Nothing
 
    objDBToScan.Close
    Set objDBToScan = Nothing
 
    objdb.Close
    Set objdb = Nothing
 
End Function
 

Thanks for any assistance that can be provided on this.

I think it would be advisable to go the Automation route - it may be the only way to go. The following code example opens a Microsoft Access database from another application through Automation and then opens a form in that database. This is just a subset of the code you will need, but it should point you in the right direction:

' Include the following in Declarations section of module.
Dim appAccess As Access.Application
 
Dim strDB as String
 
' Initialize string to database path.
Const strConPathToExtDB = "C:\Test\"
 
strDB = strConPathToExtDB & "TestDatabase.mdb"
 
' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application")
 
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strDB
 
' Open Test form.
appAccess.DoCmd.OpenForm "frmTest"