I am an advanced Excel/VBA user with basic SQL skills and ZERO Access (2010).
But we have a database in Access with numerous tables full of bad data that no one here can change automatically so I've accepted the challenge.
I want to LOOP THRU the tables and in some of them change one field based on the beginning characters in another field.
The code below came from a couple sources online but it doesn’t work and I know not why. I've left the original sample code lines in so you'll see those and my changes.
It errors on the EXECUTE line.
Code:
Dim rs As DAO.Recordset
'*****
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
Dim Grd As String
Grd = "XYZ"
For Each tdf In db.TableDefs
' ignore system and temporary tables
If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
'Member Tables only
If tdf.Name Like "*Member" Then
'*****
'original sample code: Set rs = db.OpenRecordset("TA Deliverable")
Set rs = db.OpenRecordset(tdf.Name)
'original sample code: CurrentDb.Execute "Update [TA Deliverable] SET [Deliverable Notes] = ' " & New_Deliv_Notes & "' WHERE ID = " & Deliverable_ID
CurrentDb.Execute "Update [tdf.Name] SET [SGrd] = ' " & Grd & "' WHERE Description like 'ABC*'"
rs.Edit
'original sample code: rs("Deliverable Notes") = New_Deliv_Notes
rs("SGrd") = Grd
rs.Update
rs.Close
'*****
End If
End If
Next
'*****
End Sub
TIA