Mega Code Archive

 

Categories / VisualBasic Script / Excel

 

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