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

 

[JoeVegas]

Sorry, Barcode is also.....

 

[GKChesterton]

Now we're cooking!

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

Camera table accepted the new record manually.

 

[GKChesterton]

FROM    tbTCamDat d
           LEFT JOIN
        tbCameras c
           ON d.BARCODE = c.Barcode
WHERE   c.Barcode IS NULL

I think you may find this code is not compatible with having a unique index on c.Barcode.

Well done on that trouble shooting step. Next, run code lie this:

UPDATE tbCameras
SET (
   Vendor = 'Bismuth',
   Model = 'Sasquatch",
   Type = 'myopic',
      ...
)

You're doing the same thing, using hand-entered values, but with VBA executing the action.

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

Are you running that code in the module Immediate Window?

 

[JoeVegas]

Sorry, head is spinning right now. I will try an update query.

 

[JoeVegas]

I already have 200 + records in the Cameras table and if I run that update example that will change all the records to those values, correct ?

 

[GKChesterton]

heh heh ... my head is spinning too. you asked a good question about how to run that, and a simple query does make the most sense (but i said "run code ....")

As for the SQL line I gave you, um it is kind of dynamite now that you mention it. I should be exiled probably. On the other hand, you should not be working on your production copy of the database!

Anyway I grabbed the wrong snip, and I'm sorry. Needs to start "INSERT INTO," doesn't it!

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

Well I did one thing correctly and am not using the prodution copy. I will try "Insert into

 

[JoeVegas]

Well I entered the code in the module window and not getting wnywhere. Do you mean to enter and run the code in the SQL pane ?

 

[JoeVegas]

I ran the SQL code and received the same error as before (earlier thread in this message) I wish I had the time to take the time but I am desperate to have this process working. I adopted this database and am trying to get things working. Once that is done I will have the time to learn and experiment.

 

[GKChesterton]

We're not experimenting, we're trouble-shooting, honest. I thought the problem was barcode as primary key and also as linking field, but maybe it isn't. Please post the entire SQL that gave you the last error.

Inheriting a database is always hard, but the learning is gonna bless you ...

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

 

[GKChesterton]

Post with Relevant Advice 1
Post with Relevant Advice 2

 

[JoeVegas]

Here is the SQL and error message I receive:

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]

I mean the SQL that had 'hand-entered' values (bismuth, sasquatch ....)

The goal here is to see if data types are matched. This is well described in 'Post with Relevant Advice 1' linked in my last post.

THIS WORKS: hand-entering values directly in the table.

DOESN'T WORK: "INSERT INTO " as VBA code.

INTERMEDIATE: An update query that isn't VBA-run and does have specified values. Yesterday afternoon that didn't work and we want to see why.

The key to trouble shooting is to play these intermediate steps. Simplify and reduce; find humble success; advance by steps toward the desired complexity.

[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 compared all tables, masters and temp and verified all fields are of the same data type. I have some field sizes in the temp tables that a smaller than the master and that is the only difference. I tried the append ("INSERT INTO") query again and got the same error message.

 

[GKChesterton]

I tried the append ("INSERT INTO") query again and got the same error message.

... and the complete SQL string for that was ... ?

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

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;

 

[GKChesterton]

I've never done single-record appends. I looked at the Access 2003 Help for "INSERT INTO." It says the SELECT keyword applies to appending multiple records, and the VALUES keyword for single-record appends.

'To enter a single record:  
INSERT INTO target_table ( field1, field 2, .... ) VALUES ( value, value, ... );

I think this could be the problem.

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

 

[GKChesterton]

I think that's it. Your faithful counselors aren't used to "INSERT INTO," because it's not offered in the Access wizards. Kjv1611 was on to something, but he advised an Update query; that didn't make sense as it wasn't an update.

Perhaps like kjv1611, if I'm faced with appending a single record, I resort to DAO recordset. I'd deduce you are starting into Access with some SQL experience in another setting.

Implementing the VALUES syntax ought to be interesting. I have some ideas, but if you think we have found the problem and if you have puzzles still, I'd suggest you start a new thread using that very useful focus.

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