Hi everybody,

I am stuck trying to get access to retrieve data from excel using ado.
I don't know what i'm doing wrong and wish for someone to help me. I
created a test folder with and excel workbook named testbook and an
access db named db1 the table i'm trying to add info to is named
table1. The code I used is below. Can someone tell me what I've done
wrong?

The three field names in the access db is as follows:
field1 = Date
field2 = Name
field3 = Address

I have the same names in the excel spreadsheet.

I went into the VBE and clicked on Tools/References and moved Microsoft
Activex data objects 2.0 library just under the last box checked and I
checked this box. What do I click in the browse button to reference
access?


Code:
--------------------

Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\testbook\db1.mdb;"
Set rs = New ADODB.Recordset
rs.Open "table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 2
Do While Len(Range("A" & r).Formula) 0
With rs
.AddNew
.Fields("Date") = Range("A" & r).Value
.Fields("Name") = Range("B" & r).Value
.Fields("Address") = Range("C" & r).Value
.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

--------------------


I put this in a module, would that be the correct place to put this
code?

Any help would be deeply appreciated.

Larry


--
keithl816
------------------------------------------------------------------------
keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287
View this thread: http://www.excelforum.com/showthread...hreadid=474422