Update records in Microsoft Access

[robtarheel]

I am long time Access user, but very new to VBA. Currently, I have a two tables, one with old "history" data, and one with newly updated, "temporary" data (this data is acually currently being imported from a text file). I want to write VBA that will:
1. Review record in Temporary table and see if that record (tied to two fields, "Defects" and "DefectConditionNumber") exists in the History table..
2. If the record exists
2.1 Read new fields
2.2 Write to update certain fields (not all)
3. If the record does not exist
3.1 Insert entire record into History table.
4. Loop through all records in Temporary table until finished.

Thanks for your help and your time!

Oh, and Merry Christmas

RobTarHeel

 

[jrbarnett]

Hello robtarheel

Welcome to Tek Tips. Can I ask how far you have got with this?

John

 

[PHV]

No VBA needed but an UPDATE query and an INSERT query.

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

 

[robtarheel]

jrbarnett,
No where since I've posted. I tried to do some research online but, can't find anything that does all I need, and works. I've tried the Update queries, but can only figure out how to change all records of a certain field, not individual records and fields. Make sense? Also, thanks for the reply!

robtarheel

 

[CaptainD]

I found this searching the site.

http://www.tek-tips.com/faqs.cfm?fid=3566

As far as Update query, you can parameratize it just like you can a select statement.

"Update tblMyTable Set SomeField = 'SomeValue' Where iID = 3"

Only the record where iID = 3 will get the update.

There are a lot of ways to do things. Post your code so we can see what you are doing, then we will be able to help.

 

[robtarheel]

I think this will work, without writing code. I tested it yesterday and it appeared to work. I created an update query. The table & field to be updated is [tblData(Test)].[Status]. I wrote in the "Update To:" section "[tblData-NewData(Test)].STATUS". It updated the Status field in the first table to that of the status field in the second table. If I have to I can write separate queries: one to import missing data and the second to update certain fields.

My preference would be to write it with code so that it will be faster and all in one shot.

robtarheel