Update table data with VBA

[Superock]

Hi all, wonder if you can help out with the code to update table data.

I have a table with multipule columns, what I have to do is go through each Field row by row edit the data based on the value inside. I have a rough idea of pieces of code that I have to use to achieve this, I just do not know how to piece them toghether.

I have no clue what I am doing, this is my attempt at trying to figure this out.

Dim db As DAO.Database
Dim rstCat As ADOB.Recordset 'Make Field in tableDevice

Set db = CurrentDb

rstCat.Open "Select [Make] FROM tblDevice"
Do Until rstCat.EOF
If rstCat Like "*" & "DELL" & "*" Then
rstCat="DELL"
Else If
rstCat Like "*" & "IBM "*" Then
rstCat = "IBM"
End If
Loop

End Sub

 

[FoFa]

Why not just use query/ies to do it?
You could create a macro, or use VBA to run a list of queries to do this.
Queries typically run faster than this type of code.

 

[Superock]

I allready have 23 queries that perform this function, in these queries I am using the Iff() function but I am becoming limited with the amount of Iff() I can run inside an Iff() function. I.e. Iff(Something Like Something, Something,Iff(Something Like Something, Something,Iff(Something Like Something, Something,Somethingelse)))

My criteria is growing and that is why I want to swithc the queries to VBA.

 

[jgc31]

Not quite sure why your mixing DAO and ADO.

You need to test the field values in the recordset not the recordset itself ie

If rstCat.field("field_name") Like "*" & "DELL" & "*" Then
rstCat.field("field_name")="DELL"

 

[jgc31]

Though of course the above only applies if you are using DAO

 

[john471]

Is this a one-off job, or an ongoing requirement (e.g. run every day/week/month etc to "clean up"). ?

Once you've done the initial cleanup - you should inhibit people from adding "junk" into your database.

In any case, there are at least two possible approaches you could take.

Note : the extra concatenations are superfluous, and will only serve to slow down your code
...Like "*" & "DELL" & "*" Then
is the same as (only slower)
...Like "*DELL*" Then

1) create/run multiple update queries - just be VERY careful
e.g. qryDell:-
UPDATE tblDevice SET tblDevice.Make = "DELL"
WHERE (((tblDevice.Make) Like "*DELL*"));

qryIBM:-
UPDATE tblDevice SET tblDevice.Make = "IBM"
WHERE (((tblDevice.Make) Like "*IBM*"));

etc

2) perfect & run code similar to what you've got.
jgc31 has some good points.
You also need a rstcat.Movenext, before your loop, to move to the next record.

I suspect your code should look something like (I haven't spent too much time on it, so here is just a rough cut...

Private Sub MySubName()
    Dim db As DAO.Database
    Dim rstCat As DAO.Recordset
    
    Dim szCurVal As String
    Dim szTemp As String
    Dim bContinue As Boolean
    
    Set db = CurrentDb

    Set rstCat = db.OpenRecordset("tblDevice", dbOpenTable)
    With rstCat
        If Not (.BOF And .EOF) Then
            .MoveFirst
            
            Do Until .EOF
                szCurVal = Nz(.Fields("Make").Value)  ' Can this field contin a null ?
                szTemp = szCurVal
                bContinue = True  ' this allows us to stop looking after we find a match
                szTemp = szSubstitute(szTemp, "*DELL*", "DELL", bContinue)
                szTemp = szSubstitute(szTemp, "*IBM*", "IBM", bContinue)
                szTemp = szSubstitute(szTemp, "*HP*", "HP", bContinue)
                szTemp = szSubstitute(szTemp, "*COMPAQ*", "COMPAQ", bContinue)
                
                If Not bContinue Then 'if bcontinue is false we never found a substitute
                    If szCurVal <> szTemp Then 'no point in updating if they are the same
                        .Edit
                        .Fields("Make").Value = szTemp
                        .Update
                    End If
                End If
                .MoveNext
            Loop
        End If
    End With
End Sub

Private Function szSubstitute(ByVal szCurrent As String, ByVal szLike As String, ByVal szNew As String, ByRef bContinue As Boolean) As String
    If bContinue Then
        If szCurrent Like szLike Then
            bContinue = False
            szSubstitute = szNew
        Else
            szSubstitute = szCurrent
        End If
    Else
        szSubstitute = szCurrent
    End If
End Function

 

[FoFa]

Guess I didn't understand your example, it sure looks like you could use a query like this:
UPDATE MyTable
SET rstCat="DELL"
WHERE rstCat Like "*DELL*"

If you run it frequently, you cuold create a table with the parameters (like "DELL", "IBM", etc.) and read those in a VBA script and create dynamic querydef's and run each one. That way if you need to add more later you just have to edit a table value.

 

[Superock]

Thx all for the quick replies, I am going to try the sample code that was posted, I think this is exactly what I am looking for (yayy!!!!).

Sorry for not explaining fully what this should be used for. I have made a dB that will store asset information that will be refreshed as they become to old. At first I have to populate the dB with information from spreadsheets that someone has been maintaining and have not used any form of standard hence the junk information.

In the future, the user will export the data that they want to have updated by their client to an excel spreadsheet which they will send to their client who will make their changes and then send back to me to import update or append into the dB. I feel a headache coming on. So for now I have created a horde of queries, both in vba (Using FoFa's method) and through the UI but I suspect that later on it will become a nightmare to manage.

Again thx for the updates and I will post my successes for others.

 

[Superock]

Brilliant!!!

Ok, I have tried Johns code and it works perfectly!! This was a good one cause now (I think) I can also use this code to do the import, update and append of data for future imports.

Thx very much!!