------ Register to get rid of these "In Post" ads! ------


Using VBA and recordset's or queries, I need to update 3 related master tables from 3 unrelated temp tables. Can someone please show me how.
I 've been away from Access for some time and appreciate your input. I have 3 tables a main with (CAMERAID), 2nd table TESTS with (TESTID, and foreign with CAMERAID) and 3rd table TESTS_RESULTS with(TEST_RESULTSID, and foreign with TESTID for multiple records in the TEST_RESULTS table.

I created the three temp tables from DoCmd Spreadsheet for the Excel workbook containing the original data.

Below are the 3 table structures:

Cameras Table:
CameraID primary/autonumber
Barcode
Vendor
Model
Type
Video Format
Imager
Band
Voltage
Comments
PicturePath
Seq

Tests Table:
TestID primary/autonumber
CameraID
other field
other field
other field
other field

Tests_Results Table:
Tests_ResultsID primary/autonumber
TestID
other field
other field
other field

I have tried an append query for one of the three tables (Cameras) and get the following error:

Camera database can't append all records in the append query.
Camera database set 0 field(s) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violations, 0 record(s) due to lock viloations, and 0 record(s) due to validataion rule violations.

------ Register to get rid of these "In Post" ads! ------


I have overcome the error and successfully append data into the main table (Cameras). Can someone show me, using ADO/recordset to to automate the append process and apply the Camera's primary/key to the second table Tests as a foreign key. Please help someone. Thank you.