Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

ADO for Access Objects

written by Will Riley - Last updated Feb 2005

This example code shows how we can display a list of Access tables in an Excel userform to use as a parameter in a SQL Query. Imagine that you need to return data that is stored in similarly structured tables in an Access database, but you do not want to create a query for each table. This code would be useful. I created a userform with a simple combobox and a button entitled "Get Data" - The initialise event goes like this. 

 
Private Sub UserForm_Initialize() 
    Dim cnt As ADODB.Connection 
    Dim stDB As String, stConn As String, stSQL As String 
    Dim TablesSchema As ADODB.Recordset 
     
    'clear combobox 
    Me.ComboBox1.Clear 
    'Instantiate the Connection object. 
    Set cnt = New ADODB.Connection 
     
    'Path To And the name of the database. 
    stDB = ThisWorkbook.Path & "" & "TestExcel.mdb" 
     
    'Create the connection string. 
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & stDB & ";" 
     
    With cnt 
        .Open stConn 
        'Get all database tables. 
        Set TablesSchema = cnt.OpenSchema(adSchemaTables, _ 
        Array(Empty, Empty, Empty, "Table")) 
        Do While Not TablesSchema.EOF 
            'add them To combobox 
            With Me.ComboBox1 
                .AddItem TablesSchema("TABLE_NAME") 
            End With 
            TablesSchema.MoveNext 
        Loop 
    End With 
     
    Set cnt = Nothing 
End Sub
So now we have a list of available tables (obviously this could be filtered if you like) The "Get Data" just runs the same query on the table selected via the combobox. 
 
Private Sub cmdGetData_Click() 
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim stDB As String, stConn As String, stSQL As String 
    Dim wbBook As Workbook 
    Dim wsSheet1 As Worksheet 
    Dim Lrow As Long 
     
    'Instantiate the Connection objects. 
    Set cnt = New ADODB.Connection 
    Set wbBook = ThisWorkbook 
    Set wsSheet1 = wbBook.Sheets("sheet1") 
    'set the row For returning the recordset 
    Lrow = wsSheet1.Cells(65536, 1).End(xlUp).Row + 1 
     
    'Path To And the name of the database. 
    stDB = ThisWorkbook.Path & "" & wsSheet1.Range("C3").Value 
     
    'Create the connectionstring. 
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & stDB & ";" 
     
    'Create the SQL-statement. 
    stSQL = "SELECT * FROM " & Me.ComboBox1.Value 
    stSQL = stSQL & " WHERE CustomerID = 2" 
     
    With cnt 
        .CursorLocation = adUseClient 'Necesary For creating disconnected recordset. 
        .Open stConn 'Open connection. 
        'Instantiate the Recordset Object And execute the SQL-statement. 
        Set rst = .Execute(stSQL) 
    End With 
    'Copy the recordset To specified excel sheet & range. 
    With wsSheet1 
        .Cells(Lrow, 1).CopyFromRecordset rst 
    End With 
     
    cnt.Close 
    'Release objects from memory. 
    Set rst = Nothing 
    Set cnt = Nothing 
End Sub
Hope this helps - even if it's just another ADO/VBA example.