Hi Larry,

Your code looked to me as though it would populate the Access table, and I
gave it a quick test, and it does.

When you ask, "What do I click in the browse button to reference access?" I
assume you mean the Browse button on the References - VBAProject dialog box.
You don't need to click that. Just click OK to dismiss the dialog box, and
run your code.

Yes, the code goes in a module, but a couple of phrases in your post make me
want to check a couple of points:

- The module containing your code is an Excel VBA module, not an Access
module.
- You intend to run the code from the Excel platform, sending data to Access
from Excel, rather than running the code in Access, retrieving from Excel.
The latter is of course entirely possible, but it's not how your code is
written.

--
C^2
Conrad Carlberg

Excel Sales Forecasting for Dummies, Wiley, 2005


"keithl816" wrote
in message ...

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