Get Access DB filename with FileOpenDialog then run query against table within it?

Question

I want to browse/select a database file through an Access form and run a query on it based on the file path of the selected database file. I have tried like this:

SELECT *
    FROM ExternalTableName IN '[Forms]![MyForm]![SelectedFilePath]'
    WHERE Condition

...but that didn't work however this SQL did work:

SELECT *
    FROM ExternalTableName IN 'C:\users\desktop\filename.mdb'
    WHERE Condition

For browsing the file, I used this VBA snippet:

Private Sub cmd1()
    Dim fd As FileDialog
    Dim oFD As Variant
    Dim fileName As String

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .ButtonName = "Select"
        .AllowMultiSelect = False
        .Filters.Add "Access Files", "*.mdb", 1
        .Title = "Choose Text File"
        .InitialView = msoFileDialogViewDetails
        .Show

        For Each oFD In .SelectedItems
            fileName = oFD
        Next oFD
        On Error GoTo 0
    End With

    '~~> Change this to the relevant TextBox
    Me.TextFieldName = fileName

    Set fd = Nothing
End Sub

Answer 1

Edit:

To query a table located in an MDB that the user selects from a File Open dialog, the simplest way (while avoiding additional References) is like this:

Option Explicit

Sub testQueryExternalTable()
'displays RecordCount from specified table, selected database
    Const tableName = "tblLog"
    Const defaultPath = "c:\users\" 'default path OR path+filename
    Dim rs As Recordset, fName As String, sql as String

    fName = getFileOpenDialog(defaultPath) 'get filename
    If fName = "" Then MsgBox "You clicked cancel!": Exit Sub

    sql = "select * from " & tableName & " in '" & fName & "'"
    Set rs = CurrentDb.OpenRecordset( sql ) 'query the table
    With rs
        .MoveLast 'count records
        MsgBox .RecordCount & " records found"
        .Close 'close recordset
    End With
    Set rs = Nothing 'always clean up objects when finished
End Sub

Function getFileOpenDialog(defaultPath As String) As String
'returns filename selected from dialog ("" if user Cancels)
    With Application.FileDialog(3)
        .Title = "Please select a database to query" 'set caption
        .InitialFileName = defaultPath 'default path OR path+filename
        .AllowMultiSelect = False 'maximum one selection
        .Filters.Clear 'set file filters for drop down
        .Filters.Add "All Files", "*.*" '(in reverse order)
        .Filters.Add "Access Databases", "*.mdb" '(last = default filter)
        If .Show = True Then getFileOpenDialog = .SelectedItems(1) 'show dialog
    End With
End Function

---

More Information:

• MSDN : Application.FileDialog Property (Access)

• MSDN : FileDialog.InitialFileName Property

• MSDN : Accessing External Data with MS Access

• MSDN : Database.OpenRecordset Method (DAO)

---

Original Answer:

It's easier (and more efficient) to use Access's built-in functionality rather than recreating it in VBA.

                                                              _{^{(Click to enlarge images)}}
   

The first option imports, and the seconds option emphasized textlinks without importing. Once the table is linked you can work with it in VBA or queries as if it's a local table.