excel form populating in access 2002-03 but not 2007

S

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

sam

Thanks for the reply Tim,
I do get an error msg, it says "c:\mydocuments\DemoDB not found" something
like that. I dont have the exact error msg, I will post it soon

Thanks in Advance
 
S

sam

Hey Tim, It displays "Run-time error 3024:" "Could not find file C:\My
Documents\DemoDB"

Again, The database having problem is access 2007

Thanks in Advance
 
T

Tim Williams

Your path (C:\My Documents\DemoDB) looks incomplete.

Do you mean:

Set db = OpenDatabase("C:\My Documents\Demo21.mdb")

?

Tim
 
S

sam

Yes, That is the code I have. The one I showed you is the error message.
Here is my code: Also Does IT depend on where I place this code? I have this
code in the Submit_Click() section at the bottom of this section. (There is
other code in Submit_Click() section.

' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\Documents and Settings\My Documents\DemoDB.mdb")
' open the database
Set rs = db.OpenRecordset("Demo_Table", dbOpenTable)
' get all records in a table
r = 3 ' 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("Officer Name") = Range("A" & 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
.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
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


Thanks in Advance.
 
T

Tim Williams

Your original post had the wrong path also, not just the error you
posted.

You had:
now you have:
Set db = OpenDatabase("C:\Documents and Settings\My Documents\DemoDB.mdb")
Click to expand...

Do you still get the same error message now you've corrected it?

Try this is the Immediate window in the VBE
? Dir("C:\Documents and Settings\My Documents\DemoDB.mdb")

What do you get ?

Tim
 
S

sam

It still doesnt populate the access DB from excel form. Here is my code for
the Submit_Click() section.

Here is my Code for Submit_Click() event:

Private Sub Submit_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for Officer Name
If Trim(Me.OfficerNm.Value) = "" Then
Me.OfficerNm.SetFocus
MsgBox "Please enter your Name"
Exit Sub
End If

'check for Address
If Trim(Me.Add.Value) = "" Then
Me.Add.SetFocus
MsgBox "Please enter Address"
Exit Sub
End If

'check for City Name
If Trim(Me.Cty.Value) = "" Then
Me.Cty.SetFocus
MsgBox "Please enter City Name"
Exit Sub
End If

'check for State Name
If Trim(Me.St.Value) = "" Then
Me.St.SetFocus
MsgBox "Please enter State Name"
Exit Sub
End If

'check for Zip Code
If Trim(Me.Zip.Value) = "" Then
Me.Zip.SetFocus
MsgBox "Please enter Zip Code"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.OfficerNm.Value
ws.Cells(iRow, 3).Value = Me.AcctNo.Value
ws.Cells(iRow, 4).Value = Me.OffcPhNo.Value

ws.Cells(iRow, 5).Value = Me.CntcPersonName.Value
ws.Cells(iRow, 6).Value = Me.ContactPersonPhNo.Value


ws.Cells(iRow, 11).Value = Me.Add.Value
ws.Cells(iRow, 12).Value = Me.Cty.Value
ws.Cells(iRow, 13).Value = Me.St.Value
ws.Cells(iRow, 14).Value = Me.Zip.Value

' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\Documents and Settings\My Documents\DemoDB")
' open the database
Set rs = db.OpenRecordset("Demo_Table", dbOpenTable)
' get all records in a table
r = 3 ' 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("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

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

'clear the data from form
Me.OfficerNm.Value = ""
Me.OffcPhNo.Value = ""
Me.CntcPersonName.Value = ""
Me.ContactPersonPhNo.Value = ""

Me.Add.Value = ""
Me.Cty.Value = ""
Me.St.Value = ""
Me.Zip.Value = ""

Me.OfficerNm.SetFocus

End Sub

Thanks in Advance
 
T

Tim Williams

Are you getting the same error ?

Tim

sam said:
It still doesnt populate the access DB from excel form. Here is my code
for
the Submit_Click() section.

Here is my Code for Submit_Click() event:

Private Sub Submit_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'check for Officer Name
If Trim(Me.OfficerNm.Value) = "" Then
Me.OfficerNm.SetFocus
MsgBox "Please enter your Name"
Exit Sub
End If

'check for Address
If Trim(Me.Add.Value) = "" Then
Me.Add.SetFocus
MsgBox "Please enter Address"
Exit Sub
End If

'check for City Name
If Trim(Me.Cty.Value) = "" Then
Me.Cty.SetFocus
MsgBox "Please enter City Name"
Exit Sub
End If

'check for State Name
If Trim(Me.St.Value) = "" Then
Me.St.SetFocus
MsgBox "Please enter State Name"
Exit Sub
End If

'check for Zip Code
If Trim(Me.Zip.Value) = "" Then
Me.Zip.SetFocus
MsgBox "Please enter Zip Code"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.OfficerNm.Value
ws.Cells(iRow, 3).Value = Me.AcctNo.Value
ws.Cells(iRow, 4).Value = Me.OffcPhNo.Value

ws.Cells(iRow, 5).Value = Me.CntcPersonName.Value
ws.Cells(iRow, 6).Value = Me.ContactPersonPhNo.Value


ws.Cells(iRow, 11).Value = Me.Add.Value
ws.Cells(iRow, 12).Value = Me.Cty.Value
ws.Cells(iRow, 13).Value = Me.St.Value
ws.Cells(iRow, 14).Value = Me.Zip.Value

' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\Documents and Settings\My Documents\DemoDB")
' open the database
Set rs = db.OpenRecordset("Demo_Table", dbOpenTable)
' get all records in a table
r = 3 ' 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("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

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

'clear the data from form
Me.OfficerNm.Value = ""
Me.OffcPhNo.Value = ""
Me.CntcPersonName.Value = ""
Me.ContactPersonPhNo.Value = ""

Me.Add.Value = ""
Me.Cty.Value = ""
Me.St.Value = ""
Me.Zip.Value = ""

Me.OfficerNm.SetFocus

End Sub

Thanks in Advance
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Not populating in Access database from excel form 1
User input from excel form not populating Access Database 3
excel form populating in access 02-03 but not 07 0
Exporting from Excel to Access DB 2
VBA Opening Access Table from Excel - Permission Error 2
type mismatch error in macro in excel 0
exporting to access 2007 7
export excel data to access table using ADO 0

Top