Append data to 3 master tables from 3 temp tables using

[JoeVegas]

Using VBA and ADO/recordset's and/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 successfully appended data into the main table (Cameras). Can someone show me, using ADO/recordset how to automate the append process and apply the Camera's primary/key to the second table Tests as a foreign key after I append data to the Tests table. Please help someone. Thank you.

 

[newguy86]

What version of MS Access are you using?

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[JoeVegas]

Sorry, MS/Office Access 2003

 

[GKChesterton]

Here is a typical VBA approach:

    Dim dbHOMER as DAO.Database
    Set dbHOMER = CurrentDB       
    Dim rsJoin As DAO.Recordset
    Set rsJoin = dbHOMER.OpenRecordset("cJoinProjCompCtactRole", dbOpenDynaset)
    rsJoin.AddNew
        rsJoin!FID_Proj = lngProj
        rsJoin!FID_Ctact = lngContact
        rsJoin!FID_Role = byteRole
    rsJoin.Update
    rsJoin.Close
    Set rsJoin = Nothing
    Set dbHOMER = Nothing

Lots of tasks better done with SQL. While "cJoinProjCompCtactRole" happens to be a table (and very awkwardly named), I commonly would use a query to answer preliminary data-handling needs.

Is that helpful?

 

[newguy86]

You may just want to use a series of Update or Append queries to accomplish this task and if you want to automate this process you can write a macro to run all of these queries on a specified event (OnClick, OnOpen, et cetera).

Hope this helps,

Travis

http://www.enterprise.com

"Why would I ever want to learn about programming when Micorsoft products would be able to handle even the simplest of tasks. Oh...wait a minute...something's wrong with that statement?!

 

[GKChesterton]

Note following newguy86: The interesting aspect of this on JV's last post was that inserting one record required "INSERT INTO ... VALUES ..." instead of "INSERT INTO ... SELECT ..." -- which isn't a syntax I was used to.

Hmm ... but if you know your SELECT query will only issue one record, then I guess "INSERT INTO ... SELECT ..." ought to be fine. Hey JV! Maybe "key violations" from your last post was due to the SELECT query issuing multiple records.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[JoeVegas]

I will give it a try. Thanks

 

[JoeVegas]

Do I have to use three connection statements, to have three table open at the same time in DAO or ADO ?

 

[GKChesterton]

You need to declare one Database and then one Recordset for each table/query. Does that answer your question? Also: There are compelling reasons to use ADO (e.g., linking to an alien mothership), but if you don't have any, I recommend you use DAO.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​