Allow User Change Data Source Path

Hello everyone,

I am new to the VBA side of excel. I built a spreadsheet with minor macros that tracks information I currently export from a tracking system. Now that we have viable data at one location, there is need to go network wide. My problem I can export the data to excel then into access on my desktop or laptop but, now there will 12 other workbooks and databases out there. I do not want to be "the king' of data. I would like the users to be able to change the Data Source path on as an as needed basis.

Sub UpdateDischargeData()
    
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, i As Long, lastrow As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    'define and open a recordset from the specified database path and database table name. Change it to suit your need.
    
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & " Data Source=C:\Documents and Settings\k492812b\desktop\Discharge_Data\ORMC_DISCHARGE.ACCDB"
    
    Set rs = New ADODB.Recordset
    rs.Open "ORMC_DISCHARGE", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    lastrow = ActiveSheet.UsedRange.Rows.Count

    Dim dlg As Office.FileDialog, path as String
    Set dlg = Application.FileDialog(msoFileDialogFilePicker)
    dlg.Filters.Clear
    dlg.Filters.Add "Access Files", "*.accbd"
    dlg.FilterIndex = 0
    dlg.Show
    If dlg.SelectedItems.Count > 0 Then
        Dim cn As ADODB.Connection, rs As ADODB.Recordset, i As Long, lastrow As Long
        ' connect to the Access database
        Set cn = New ADODB.Connection
        'define and open a recordset from the specified database path and database table name. Change it to suit your need.
    
        cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & " Data Source=" & dlg.selecteditems(1)    
        Set rs = New ADODB.Recordset
        rs.Open "ORMC_DISCHARGE", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
        lastrow = ActiveSheet.UsedRange.Rows.Count
    End If

Thank you for the help. I try run the file picker but, does not see the database in the location. My other question do attach this the rest my code? Does picker fire each time I run the export macro.

Thanks Again

I figured out the file issue typo.

Thanks

Oops. I see it now. I'm glad you got it.