CODEWITHSUNDEEP
vbaapims-accessreference
Jiminy Cricket
Jiminy Cricket

Reputation: 1377

Reference VBA Application without knowing its name / path

We have a legacy Access Database where we can access the front end and reports. We're in a position where we'd like to export, and manipulate the data in the reports via Excel. Our problem, though, is that the Export to Excel feature has been disabled.

I know that if I knew the location of the back-end of the database, I'd be able to set a reference via ADO or DAO, but due to having no contact with the original developer, this isn't possible.

The only way I can think to get around this would be if there was some way to loop through currently open Application objects, perhaps via a Windows API? The set a reference from there.

Is this possible?

Edit

I've since managed to retrieve the Access hWnd from Excel, but I cannot seem to pass the hWnd to the FindWindowEx API as I can with Excel applications.

Upvotes: 0

Views: 123

Answers (1)

Jiminy Cricket
Jiminy Cricket

Reputation: 1377

After a bit of work, I've come up with the below, working code. The actual code for referencing the Database is much smaller than my attempts using APIs and hWnd.

I've included the printing of tables and fields to prove it works. I'm very surprised how straightforward this was...! 

'Requires References To:
    'Access Object Library
    'DAO Object Library
    Sub GetTables()
    Dim obj As Object 'Access Application
    Dim db As Database 'Database
    Dim tbl As TableDef 'Table
    Dim rs As Recordset 'Recordset
    Dim strTable As String 'Table Name
    Dim strSQL As String 'SQL for Recordset
    Dim fld As Field 'Field

    'Get The Currently Open Access Application Object
    'Ensure There Is Only One Open To Return The Desired Result
    Set obj = GetObject(, "Access.Application")
    'Get The Database Of The Application
    Set db = obj.CurrentDb
    'Loop Tables In Database
    For Each tbl In db.TableDefs
        'Get Table Name
        strTable = tbl.Name
        'Ignore Hidden Access Tables
        If Left(strTable, 4) <> "MSys" Then
            'Print Name Of Table
            Debug.Print strTable
            'Create SQL For Recordset Of Table
            strSQL = "SELECT " & strTable & ".* FROM " & strTable & ";"
            'Create Recordset
            Set rs = db.OpenRecordset(strSQL)
            'Loop Fields In Recordset
            For Each fld In rs.Fields
                'Print Name Of Field
                Debug.Print "          " & fld.Name
            Next fld
        End If
    Next tbl

End Sub

Upvotes: 1

Related Questions