Use ADO to read the data from Access database to Excel

 

Public Sub SavedQuery()
    
  Dim Field As ADODB.Field
  Dim Recordset As ADODB.Recordset
  Dim Offset As Long
    
  Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydb.mdb;Persist Security Info=False"
    
  Set Recordset = New ADODB.Recordset
  Call Recordset.Open("[Sales By Category]", ConnectionString, _
    CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
    CommandTypeEnum.adCmdTable)

  If Not Recordset.EOF Then
    With Sheet1.Range("A1")
      For Each Field In Recordset.Fields
        .Offset(0, Offset).Value = Field.Name
        Offset = Offset + 1
      Next Field
      .Resize(1, Recordset.Fields.Count).Font.Bold = True
    End With
    Call Sheet1.Range("A2").CopyFromRecordset(Recordset)
    Sheet1.UsedRange.EntireColumn.AutoFit
  Else
    Debug.Print "Error: No records returned."
  End If
  Recordset.Close
  Set Recordset = Nothing
End Sub

Related examples in the same category

1.	Opening an Excel Spreadsheet with ADO
2.	Insert a row to a worksheet by using the SQL statement
3.	open the Excel worksheet, create a recordset with the data in the sheet, and then print it in the Immediate window.
4.	Excel based database

Use ADO to read the data from Access database to Excel : Excel ADO « Excel « VBA / Excel / Access / Word

Related examples in the same category