S
Sebastian
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
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