Retrieving data from Excel into an Access form/table

[eddie442]

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

 

[Treason]

Judging by your error message you have a relationship between your tables that you are breaking when you add the record. Try to add the record to tblCompanies first, then to tblQuotes

 

[eddie442]

Apologies for the late reply but I have been away. Yes, the relationship was the problem, and I have amended. Many Thanks for your help!

Dave