Using VBA and recordset's I need to update 3 master tables from 3 temp 1

[JoeVegas]

Using VBA and recordset's I need to update 3 relational master tables from 3 unrelated temp tables. Can someone please show me how. TIA

 

[MazeWorX]

Try using update or append queries and running them from code

Docmd.OpenQuery "QueryName"

I use this method to normalize it to my tables and either update existing records or add new ones
Assuming you are confident with the queries results you may want to turn the warnings off when the queries are running otherwise access will warn you to death

Docmd.SetWarnings = False
Set it to True when the code has finished

HTH M

Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[JoeVegas]

Thanks for your response. I 've been away from Access for some time and appreciate your input. I will try your suggestion. 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 create three temp tables from DoCmd Spreadsheet for the Excel workbook containing the original data.

I think I can use an append query to first establish the next CAMERAID in the CAMERA table, I think I should run an update query to apply the CAMERAID to the TESTS table to add the foreign CAMERAID. then append the data to the TESTS table. Lastly, I think I can then update the TEST_RESULTS table using IS NULL to add the the TESTID value to each record in the table. Will this be a good approach or am I in left field. TIA

 

[GKChesterton]

Your description looks sound, but it's merely that. Your next step is sure enough to try it out. Even left field is a field.

[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 have tried the first query to append the data from temp table to the main CameraID. Keep getting errors. I checked all of the field properties on both tables, and they match.
Obviously, I don't have a clue at this point. Thanks for you reply.

 

[JoeVegas]

Can anyone please help !!

 

[GKChesterton]

We'd need details! Post your SQL code. Specify the error messages.

[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]

Here is the SQL from the query:
INSERT INTO tbCameras ( Barcode, Vendor, Model, Type, [Video Format], Imager, [Band], Voltage, Comments, PicturePath, SEQ )

SELECT tbTCamDat.BARCODE, tbTCamDat.VENDOR, tbTCamDat.MODEL, tbTCamDat.TYPE, tbTCamDat.[VIDEO FORMAT], tbTCamDat.IMAGER, tbTCamDat.[BAND], tbTCamDat.VOLTAGE, tbTCamDat.COMMENTS, tbTCamDat.PICTUREPATH, tbTCamDat.SEQ

FROM tbTCamDat;

The error message:
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.

 

[GKChesterton]

So a conflict to do with table structure is indicated. Look for the table's built-in "rules".

Trouble-shoot. Open the table tbCameras, hand-enter a new record.

[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]​

 

[kjv1611]

I think that at least part of your problem is that you're using an INSERT query when you should be using an UPDATE query - for at least some of your records. Basically, if the ID value for a particular record already exists, and you try to insert that record again, instead of UPDATING the record, then you'll get the error that you're getting.

Notice exactly what the message is telling you:

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, [HIGHLIGHT]and it didn't add 1 record(s) to the table due to key violations,[/HIGHLIGHT] 0 record(s) due to lock viloations, and 0 record(s) due to validataion rule violations.

You may find that's your only problem. If so, then the following might take care of it:

Is there a way for you to differentiate from beforehand which records are brand new records, and which are only updates?

If it's all in one big table, then you'll need to do one of 2 things - whichever is just your choice. SQL will always or almost always be faster than using a Recordset (which is a separate option from within VBA).

So you may need 2 separate queries....

One UPDATE

Then one INSERT...

Each one will use at least a WHERE clause or a JOIN... whatever it takes to determine whether the record exists... if I had to guess, I'd say a JOIN clause and WHERE clause combination will get it for the INSERT query, and just the JOIN will take care of the UPDATE query....

Something like this maybe:

UPDATE:

[tt]
UPDATE tbCameras
SET (
   Vendor = d.VENDOR,
   Model = d.MODEL,
   Type = d.TYPE,
   [Video Format] = d.[VIDEO FORMAT],
   Imager = d.IMAGER,
   [Band] = d.[BAND],
   Voltage = d.VOLTAGE,
   Comments = d.COMMENTS,
   PicturePath = d.PICTUREPATH,
   SEQ = d.SEQ
)
FROM tbTCamDat d
       INNER JOIN
     tbCameras c
       ON c.Barcode = d.BARCODE
[/tt]

INSERT:

[tt]
INSERT INTO tbCameras (
   Barcode, Vendor, Model, Type, [Video Format], Imager, [Band], Voltage, Comments, PicturePath, SEQ
)
SELECT  d.BARCODE, d.VENDOR, d.MODEL, d.TYPE, d.[VIDEO FORMAT],
        d.IMAGER, d.[BAND], d.VOLTAGE, d.COMMENTS, d.PICTUREPATH, d.SEQ
FROM    tbTCamDat d
           LEFT JOIN
        tbCameras c
           ON d.BARCODE = c.Barcode
WHERE   c.Barcode IS NULL
[/tt]

I can't guarantee that code will work flawlessly - I could have made a typo, but the basic idea should be there...

--

"If to err is human, then I must be some kind of human!" -Me

 

[JoeVegas]

Thanks for your replies. The record coming from TCamDat has all new data including barcode, no matching barcodes in the tbCamera table. CameraID is the primary key in the tbCamera table this is the reason I thought an append query would work. With the 3 tables linked, meaning Camera (CameraID primary) to Tests(CameraID foreign) and Tests(TestsID primary) to Tests_Results (Tests_ResultsID primary) with (TestID foreign), shouldn't I still be able to append a new record to the Camera table. I am trying to not make this to confusing.

 

[kjv1611]

and it didn't add 1 record(s) to the table due to key violations,

That piece of the error message is saying that you're trying to enter data into a Key field which does not belong. The most common occurrence of such is when you're trying to add a record which already exists in the table - or at least you've got a duplicate ID/Key somehow.

Another thing you could do is create recordsets of the tables you're comparing and loop through the records, and include some sort of notification for records giving you such problems, so you can possibly better sort out what is going on... and/or create an alternative method for the one (or few) records you encounter on different ocasions.

something like this:
**You may need to set a Reference to the Microsoft DAO object library under Tools->References, but I think it's set by default starting in Access 2003 - or at least 2007..

Private Sub AddSomeNewRecords()
  Dim db as DAO.Database
  Dim rsCams as DAO.Recordset [green]'Table tbCameras[/green]
  Dim rsDat as DAO.Recordset [green]'Table tbTCamDat[/green]
  
  Do Until rsDat.EOF
    'If DLookup( - Use the F1 key within VB Editor to get the correct syntax for this part)....

    'Basically
    'If DLookup( - if The rsDat record exists within tblCameras) Then
       MsgBox "Record " & rsDat.ID & " already exists!  Please verify whether you want to change the existing record!"
    

   'Then close and delete the DAO objects
      rsDat.Close
      rsCam.Close
      Set rsDat = Nothing
      Set rsCam = Nothing
      db.Close
      Set db = Nothing

End Sub

Sorry if that seems scattered and incomplete... I couldn't remember the DLookup format for sure off the top of my head, and I'd rather make sure you wanted to go that route before going into great detail/depth.

Let us know what you think about all of that, whether you think it'll work in your situation..








--

"If to err is human, then I must be some kind of human!" -Me

 

[JoeVegas]

I am able to manually enter a new record in the Camera table.

 

[GKChesterton]

Is CameraID an AutoNumber? If it isn't, that would account for it.

Check your Relationships table. Referential integrity rules may interfere. (Though I think the message would be different.)

Trouble-shoot. Open the table tbCameras, hand-enter a new record.

[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]

Yes CameraID is autonumber and primary field for the Camera table just as TestID and Tests_ResultsID.

 

[GKChesterton]

Trouble-shoot. Open the table tbCameras, hand-enter a new record.

Check your Relationships table. Referential integrity rules may interfere.

You're going to solve this eventually. I think unless you post your tables' structures in complete fashion, no one else will be able to tell you just how. But you can do it through trouble-shooting: Access is telling you what's wrong, just follow the signal.

[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]

Below are the 3 table sturctures:

Camera 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

 

[PHV]

No other unique index on Camera table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeVegas]

No, CameraID is the only one.

 

[GKChesterton]

Are these configured for referential integrity somehow in the Relationships table?

Trouble-shooting:
1. Jot down a likely value for each of these fields: Barcode, Vendor, Model, Type, Video Format, Imager, Band, Voltage, Comments, PicturePath, SEQ.

2. Open the Camera table. Go to "new record."

3. Enter each of the values in the appropriate field.

4. Now declare that record -- click on 'new record' again or click on another existing record.

Do you succeed in making your new record?

[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]​