Solved: Excel Access interaction with ADO

[philfer]

Hello,

I often get confused with ADO and DAO and sometime export data to access with DAO and retrieve query results into excel with ADO. I am hopless!!!

Is there any resources on the web to give me sample code consistently using ADO to :-

1) transfer data from excel into an access table
2) retrieve query results into excel
3) import data in access from excel using ADO
4) export data to an excel file using ADO

Thanks in advance
Phil

[CreganTur]

1) transfer data from excel into an access table

Change the connection string to your Access DB. Pull the records out of Excel using a range loop and have every iteration of the loop add a new record to the recordset.

[VBA]Sub AddNewRecADO()
'Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Acc07_ByExample\Northwind.mdb"
Set rst = New ADODB.Recordset
With rst
.Open "SELECT * FROM Employees", strConn, adOpenKeyset, adLockOptimistic

'add a record and specify field values
.AddNew
!LastName = "Roberts"
!FirstName = "Paul"
!Title = "Sales Representative"

'retrieve the employee ID for current record
Debug.Print !EmployeeID.Value

'move to the first record
.MoveFirst
Debug.Print !EmployeeID.Value
.Close
End With
Set rst = Nothing
End Sub[/VBA]

The range loop could look something like:
[VBA]Dim cell As Range

For Each cell in Range("A1:A10")
'do stuff
Next[/VBA]

2) retrieve query results into excel

[VBA]Sub ConnectAndExec()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Acc07_ByExample\Northwind.mdb"
Set rst = conn.Execute("SELECT * FROM Employees")
Debug.Print rst.Source
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub[/VBA]
Open an ADO recordset using a SQL string to control what data you want to pull. Then to get it into Excel you'll need to iterate through the recordset until it reaches the end and put each record into a range. You could use a Do...Loop structure:
[VBA]rst.MoveFirst 'explicitly move to start of recordset
Do Until rst.EOF
'do stuff
Loop[/VBA]

3) import data in access from excel using ADO

This is a complex bit of code I built that uses a file dialog window to allow you to choose an excel spreadsheet. Once you choose one it uses ADO to create a recordset by querying your selected spreadsheet. This example is used for copying data from one spreadsheet to another, but you can modify it to work with Access (required reference to Microsoft Office 11.0 or 12.0 Library):
[VBA]Private Sub btnLoad_Click()
Dim strFilepath As String
Dim dlgOpen As Office.FileDialog
Dim vrtSelectedItem As Variant
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rsData As ADODB.Recordset
Dim szSheetName As String
Dim i As Integer
'select workbooks using file dialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen '<<<Opens file dialog window
.AllowMultiSelect = False '<<<User can select multiple files | False for single file
.Title = "Please select file to load" '<<<Title text for window
.Filters.Clear '<<<Removes any old File Dialog filters
.Filters.Add "Excel Files", "*.XLS" '<<<sets filter to Excel files

If .Show = 0 Then '<<< if User presses Cancel then Sub ends
Exit Sub
Else
'Will loop through all files selected by the Dialog window
For Each vrtSelectedItem In .SelectedItems
strFilepath = vrtSelectedItem '<<<Set filepath to Variable
Next
End If
End With
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFilepath & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set rsData = conn.OpenSchema(adSchemaTables)
szSheetName = rsData.Fields("TABLE_NAME").Value '<<<Get worksheet name
Set rst = New ADODB.Recordset
rst.Open "SELECT AcctNr FROM [" & szSheetName & "];", conn, adOpenStatic, adLockOptimistic
rst.MoveFirst
i = 2
Do Until rst.EOF
ActiveWorkbook.Sheets(1).Cells(i, 1).Value = Right(rst![AcctNr], 14)
i = i + 1
rst.MoveNext
Loop
MsgBox "All account numbers have been added to the worksheet."
End Sub[/VBA]

4) export data to an excel file using ADO

If it's an existing spreadsheet then create a connection similar to the code provided for #3, then just add records to the recordset.

HTH