Data Not populating in Access database from excel form

[sam]

Hi All,

I am having an issue with my database. I connected my excel form to access
database using DAO which is not populating the access database.

Here is my code to connect to the access database.

Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\Documents and Settings\My Documents\DemoDB")

Set rs = db.OpenRecordset("Demo_Table", dbOpenTable)

r = 3
Do While Len(Range("A" & r).Formula) > 0

With rs
.AddNew

.Fields("Officer Name") = Range("A" & 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
.Fields("Address") = Range("K" & r).Value
.Fields("City") = Range("L" & r).Value
.Fields("State") = Range("M" & r).Value
.Fields("Zip Code") = Range("N" & r).Value
.Fields("Special Instructions/Comments") = Range("P" & r).Value

.Update
End With
r = r + 1
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

This is my code to connect to the access database, Its not displaying any
error. Once I click "Submit" button on excel user form it just does nothing.
It does populate in excel sheet that I have, But not the Access database.

PLEASE HELP.

Thanks in Advance.

 

[Mike]

Im not sure with DAO but this will work for ADODB
Option Explicit
Private Sub saveDataToAccess()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim r As Integer
r = 6
'access database 2003 or less
'C:\PathToYourMdb\Ilsa.mdb (Change)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& "C:\PathToYourMdb\Ilsa.mdb;Persist Security Info=False"
'Use for Access (Ace No Security)
'strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccess2007file.accdb;" _
'& "Persist Security Info=False;"
'Use for Access (Ace With Security)
'strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\myFolder\myAccess2007file.accdb;" _
'& "Jet OLEDBatabase Password=MyDbPassword;"

'sSQL = Name Of Your Access table Change to your
'Table Name
sSQL = "TableName"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open strConn

rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTable
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("Field1") = Range("A" & r).Value
.Fields("Field2") = Range("E" & r).Value
.Fields("Field3") = Range("F" & r).Value
.Fields("Field4") = Range("G" & r).Value

' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
cnn.Close
End Sub