excel form populating in access 02-03 but not 07

[sam]

Hi All,

I have designed an excel form which is used by multiple users to input data
into access database.
MY ISSUE: when user inputs the data in the excel form and clicks submit, The
data is populated BUT only when I use Access 2002-2003 version and NOT Access
2007.

Here is my code to connect to the database.

Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\My Documents\Demo21")
' open the database
Set rs = db.OpenRecordset("Demo_Table", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
'.Fields("FieldName1") = Range("A" & r).Value
.Fields("Officer Name") = Range("A" & r).Value
.Fields("Cost Center") = Range("B" & r).Value
.Fields("Account #") = Range("C" & r).Value
.Fields("Officer Phone #") = Range("D" & r).Value
.Fields("Contact Person Name") = Range("E" & r).Value
.Fields("Contact Person Phone #") = Range("F" & r).Value

.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


Please Help.

Thanks in Advance.