excel form populating in access 2002-03 but not 2007
 

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.

excel form populating in access 2002-03 but not 2007
 

Any error, or just nothing happens ?

Tim

"sam" wrote in message
...
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.

excel form populating in access 2002-03 but not 2007
 

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

"Tim Williams" wrote:

Any error, or just nothing happens ?

Tim

"sam" wrote in message
...
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.

excel form populating in access 2002-03 but not 2007
 

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



"Tim Williams" wrote:

Any error, or just nothing happens ?

Tim

"sam" wrote in message
...
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.

excel form populating in access 2002-03 but not 2007
 

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

Do you mean:

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

?

Tim




On Jul 8, 7:29*am, sam wrote:
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



"Tim Williams" wrote:
Any error, or just nothing happens ?

Tim

"sam" wrote in message
...
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.- Hide quoted text -

- Show quoted text -

excel form populating in access 2002-03 but not 2007
 

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.


"Tim Williams" wrote:

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

Do you mean:

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

?

Tim




On Jul 8, 7:29 am, sam wrote:
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



"Tim Williams" wrote:
Any error, or just nothing happens ?

Tim

"sam" wrote in message
...
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.- Hide quoted text -

- Show quoted text -

excel form populating in access 2002-03 but not 2007
 

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

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

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



On Jul 8, 12:05*pm, sam wrote:
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.



"Tim Williams" wrote:
Your path (C:\My Documents\DemoDB) looks incomplete.

Do you mean:

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

?

Tim

On Jul 8, 7:29 am, sam wrote:
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

"Tim Williams" wrote:
Any error, or just nothing happens ?

Tim

"sam" wrote in message
...
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

excel form populating in access 2002-03 but not 2007
 

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





"Tim Williams" wrote:

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

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

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



On Jul 8, 12:05 pm, sam wrote:
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.



"Tim Williams" wrote:
Your path (C:\My Documents\DemoDB) looks incomplete.

Do you mean:

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

?

Tim

On Jul 8, 7:29 am, sam wrote:
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

"Tim Williams" wrote:
Any error, or just nothing happens ?

Tim

"sam" wrote in message
...
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

excel form populating in access 2002-03 but not 2007
 

Are you getting the same error ?

Tim

"sam" wrote in message
...
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





"Tim Williams" wrote:

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

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

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



On Jul 8, 12:05 pm, sam wrote:
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.



"Tim Williams" wrote:
Your path (C:\My Documents\DemoDB) looks incomplete.

Do you mean:

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

?

Tim

On Jul 8, 7:29 am, sam wrote:
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

"Tim Williams" wrote:
Any error, or just nothing happens ?

Tim

"sam" wrote in message
...
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -