Hopefully someone can help me this (and hopefully the solution isn't staring
me in the face)
I am trying to export data from a cell in a worksheet to a field in an
Access database that I am building. The database has two tables,
tblCompanies and tblQuotes. It works through a macro accessed by a command
button, which should export the total of a quote into the field Quote_Price
in the tblQuotes table, which the users will access through a form. The
problem is that if I export the data from Excel, I get an error message
saying, "You cannot add or change a record because a related record is
required in table 'tblCompanies'. The code looks like this:
Sub NewQuote()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\DJEdwards\My Documents\Test
Folder\marketing.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblQuotes", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 55 ' the start row in the worksheet
Do While Len(Range("I" & r).Formula) > 0
With rs
.AddNew
.Fields("Quote_Price") = Range("I" & r).Value
.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
I can import the cell contents from Access with no problems, but I would
like the user to be able to do it from Excel to simplify things.
I'd be grateful for any help!
Cheers
Dave, England
me in the face)
I am trying to export data from a cell in a worksheet to a field in an
Access database that I am building. The database has two tables,
tblCompanies and tblQuotes. It works through a macro accessed by a command
button, which should export the total of a quote into the field Quote_Price
in the tblQuotes table, which the users will access through a form. The
problem is that if I export the data from Excel, I get an error message
saying, "You cannot add or change a record because a related record is
required in table 'tblCompanies'. The code looks like this:
Sub NewQuote()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\DJEdwards\My Documents\Test
Folder\marketing.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblQuotes", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 55 ' the start row in the worksheet
Do While Len(Range("I" & r).Formula) > 0
With rs
.AddNew
.Fields("Quote_Price") = Range("I" & r).Value
.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
I can import the cell contents from Access with no problems, but I would
like the user to be able to do it from Excel to simplify things.
I'd be grateful for any help!
Cheers
Dave, England
