VBA Code help, updating data

[Poobear1929]

Hi,

I must be making this much harder than what it is, but I am having problems try to write some code to update data in a table through VBA code. Here is what I as trying to do.

I have a process where I Import data from another location. It gets dumped into a temp table, from there is gets further processed and stored in a permanent table. I have a field that holds unique data and that is what I used to make sure I don't import the same record twice in my permanent table. Below is sort of the breakdown of what I am trying to do at the end of my process before I delete the temp table data.

TempTable
PermanentTable

For Each TempTable Record
Where TempTable.uniqueKey = PermanentTable.uniquekey

If TempTable.StartDate <> PermanentTable.StartDate Then
Update PermanentTable.startdate = TempTable.StartDate
Endif

If TempTable.StopDate <> PermanentTable.StopDate then
Update PermanentTable.StopDate = TempTable.StopDate
Endif

I hope this all make sense. Bottom line, some of the data in the temp table holds the current start and stop dates and I need to make sure that gets updated in the Permanent table.


Any help would be great.

Thanks

 

[MajP]

Yes you are making it two hard. Just run two update queries where the tables are inner joined on primary key. Update the start date Where not permanentTable.startDate = tempTable.startDate
second one you update the stop date where not permanentTable.stopDate = tempTable.stopDate

 

[PHV]

One UPDATE query should suffice:
UPDATE PermanentTable AS P INNER JOIN TempTable AS T ON P.uniquekey = T.uniquekey
SET P.StartDate = T.StartDate, P.StopDate = T.StopDate
WHERE P.StartDate <> T.StartDate OR P.StopDate <> T.StopDate

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

 

[Poobear1929]

Thanks Guys for the answers, however that was actually my first game plan to have an update query I could call from my import code. However, the query didn't want to work right, it would tell me what I was doing wasn't up datable. I think so of it may had to do with the fact I was trying to update the perm table with a query that selected from the temp table and was formatting the data all at once. At least that was the only think I could come up with. So I decided to use some burte force method and used record sets. However, that is where I got stumped. Couldn't figure out how to step through the Temp Table record set and for each unique key that equals the unique key from the permanent table check to see if the start and stop dates were the same. If not then update the permanent table to equal what the start and stop dates were in the temp table. I hope this all makes sense. When I get to work, I will build the query you mention and just make sure I wasn't screwing it up to begin with. If I do get a goofy message I will post it.

Thanks for your help guys!!


Poo