need tips-on using variables and queries

[cancer2006]

I have to correct the ID field in 19 tables and there are 265 records affected in just one table . The id field is populated on the values from EInfo.ID, and EInfo.ID is based on the values from EInfo.Clock. Somebody entered the wrong ids in the clock field and and it got poulated with the wrong info, so I have to undo the change.

EInfo.Clock is read from the other filed in the same table callled EInfo.SSN

This is how the IDs should look like.
EInfo.SSN = 123-45-6789
EInfo.Clock = 6789, EInfo.ID = bb6789, where bb = blank space

Once I figure out how to do take care of this then i will change the IDs in other tables.

This is how I can do this.

Read the EInfo.SSN field in a temp variable and then copy the last 4digits and put 2 blank spaces and copy that filed in another variable. I am sure there is a string function for that. and then write that variable into the table.


Once I figure the above part then I need to update the values in other tables.

Then I will take one table e.g; EPayHist, compare the EPayhist.ID from the old values in EInfo.ID and then replace it with EInfo.clock.


My limitations are that I am not very clear in ADO.NET. I have read lots of articles so far. All I know that I would need Data set, command object and data reader class.

But I need some more help than that.

I am not asking to solve my problem but if you can give me a sample code on hw to perform upate query that is similar to what i need, that will be much appreciated. I will be using VB EXPRESS and ACCESS 97 to accomplish this.

Thanks.

 

[rjoubert]

So you need to replace EPayHist.ID with 'bb6789'? If so, this update query should do the trick...

UPDATE EPayHist SET EPayHist.ID = " " & Right([EInfo]![SSN], 4)

 

[Golom]

You don't really need all the temp fields, stray variables, command objects, data reader classes, etc. ... just an SQL UPDATE statement and an ADO Connection

Dim cn As New ADODB.Connection
cn.Open [blue]<Some ADO Connection String>[/blue]
cn.Execute "UPDATE EInfo " & _
           "   SET [Clock] = Right([SSN],4), " & _
           "   [ID]    = '  ' & Right([SSN],4) "