import data from spreadsheet to access

[thomasks]

Hi all,
I would like to put a command button on a master spreadsheet stored on our network drive that everyone uses to complete quotes, that would export certain cells from different sheets into a database. I tried the transferspreadsheet command. But was getting an access error telling me i couldnt update a record because a corresponding record had to be updated. (relationships between tables?) Does anyone have some example code that I could put into excel for the command button click event that would open the database and append to the recordset?

 

[thomasks]

I forgot to include the code that I had tried before and it wouldn't work. So here it is for reference to what I was trying to do.

Private Sub CmdImport_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


Set cn = New ADODB.Connection
cn.Open "Provider = Microsoft.jet.OLEDB.4.0;" & "Data Source = T:\Quot\CONTROLS\Formulas\Controls Estimating Project Database.mdb;"

'Open Record Set tbl_customer for customer information
Set rs = New ADODB.Recordset
rs.Open "tbl_scope", cn, adOpenKeyset, adLockOptimistic, adCmdTable


With rs
.AddNew
.Fields("Location") = Sheet2.Cells(5, 12).Value
.Fields("Proj_num") = Sheet2.Cells(5, 2).Value
.Fields("Customer") = Sheet2.Cells(5, 1).Value
.Fields("ELM_Version") = Sheet2.Cells(5, 3).Value
.Fields("Requested_By") = Sheet2.Cells(5, 4).Value
.Fields("Estimator") = Sheet2.Cells(5, 5).Value
.Fields("Description") = Sheet2.Cells(5, 6).Value
.Fields("Ctrls_Hdw") = Sheet2.Cells(5, 7).Value
.Fields("Tranships") = Sheet2.Cells(5, 8).Value
.Fields("Resale") = Sheet2.Cells(5, 9).Value
.Fields("Travel") = Sheet2.Cells(5, 10).Value
.Fields("Ctrls_hours") = Sheet2.Cells(5, 11).Value

.Update

End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing






End Sub

 

[SkipVought]

Hi,

Does your Access database enforce referential integrity rules?

You may need to Add a row to another table that has a key to this table before adding rows to this table.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[thomasks]

Could you elaborate a little more? I can't figure out why it doesnt relate the tables since keys are in all of them and all the query's work fine.
When I try to add to one table it tells me I need a record in another and cant add to the table.

 

[SkipVought]

If, for instance, you have an Employee Master table that has EmpID as a key and as a foreign key to Employee Labor where weekly labor is stored.

Then if referential integrity is enforced, you cannot add detail labor records to that table for and EmpID UNTIL the EmpID exists in the Employee Master table.

Conversely, you cannot DELETE a row from the Employee Master until all the row for the associated EmpID have been deleted in the Employee Labor table.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}