I am trying to export spreadsheet data from excel to access and executed from
within excel;

below is a VBA script working with access 2003 but not with '07
I am assuming the problem is with DAO Object Library

Does anybody now what the problem is or possible work around could be?

Thanks
Sebastian



Sub ExportToAccessGateway()
' Check in VBE Tools-References-Microsoft DAO Object Library
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\0\Access\Production\ProductionDB. accdb")
Set rs = db.OpenRecordset("Gateway", dbOpenTable)
r = 1
Do While Len(Range("D" & r).Formula) 0
If Range("M" & r).Value = "Filled" Then
With rs
.AddNew
.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
db.Close
Set db = Nothing