Try Microsoft ActiveX Data Objects 2.8 Library

"Sebastian" wrote:

I have:
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLA Automation
Microsoft Access 12.0 Object Library
Microsoft Office 12.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ADO Ext. 2.8 for DDL and Security

"Mike" wrote:

What Reference do you have

"Sebastian" wrote:

no luck
It has a problem with ADODB


"Mike" wrote:

Not sure but give this a wirl using ADO

Private Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\0\Access\Production\" _
& "ProductionDB.mdb;Persist Security Info=False"

' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Gateway", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 1 ' the start row in the worksheet
Do While Len(Range("D" & r).Formula) 0
If Range("M" & r).Value = "Filled" Then

' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Symbol") = Range("D" & r).Value
.Fields("Type") = Range("F" & r).Value
.Fields("Account#") = Range("R" & r).Value
.Fields("CreationDate") = Now()
' add more fields if necessary...
.Update ' stores the new record
End With
End If

r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub