davecocks
asked on
Variables in an Update query
I've adapted this piece of code below, I think its working, but the NewID and OldID strings are not operating in the update query SQL i.e. Access comes up with 'enter parameter values' for both NewID and OldID
Any ideas? I've experimented with a few & and " in places but can't get it to work
Any ideas? I've experimented with a few & and " in places but can't get it to work
Public Sub XXXCheckFieldsXXX()
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer, j As Integer, fldArr()
Set rs = CurrentDb.OpenRecordset("Determinants") ' change the name of the source table
Set rs1 = CurrentDb.OpenRecordset("Data_Intermediate") 'change name of destination table
Dim NewID As String
Dim OldID As String
Dim strDataUpdate As String
strDataUpdate = "UPDATE Data_Intermediate " & _
"SET DeterminantID = NewID " & _
"WHERE DeterminantID = OldID;"
If rs.EOF Or rs.BOF Then
MsgBox "no records"
Exit Sub
End If
'For each Data_intermediate value
Do While Not rs1.EOF
OldID = rs1.Fields("DeterminantID").Value
'For each Determinant tbl value
Do While Not rs.EOF
NewID = rs.Fields("DeterminantID").Value
If rs1.Fields("DeterminantID").Value = rs.Fields("EA Code").Value Then
'rs1.Fields("DeterminantID").Value = rs.Fields("DeterminantID").Value
DoCmd.RunSQL strDataUpdate
rs.MoveNext
Else
rs.MoveNext
End If
Loop
rs1.MoveNext
Loop
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is there any particular reason you're not just using a single UPDATE query (with a JOIN)?
Please don't take offense, but your approach (as well as the design flaw I've just highlighted) isn't very efficient.
Please don't take offense, but your approach (as well as the design flaw I've just highlighted) isn't very efficient.
I'm still trying to get my head round what you're trying to achieve, but won't this do what you want?
Public Sub XXXCheckFieldsXXX()
DoCmd.RunSQL "UPDATE Data_Intermediate INNER JOIN Determinants " & _
"ON Determinants.DeterminantID = Data_Intermediate.DeterminantID " & _
"SET Data_Intermediate.DeterminantID = Determinants.DeterminantID " & _
"WHERE Determinants.[EA Code] = Data_Intermediate.DeterminantID"
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Jez no offense taken, its good to know where my design is going wrong. Thanks for the second update on the correction of the code!!! It worked brilliantly.
Hi matthewspatrick, I think your solution worked for my original question, but it was my code that wasn't upto scratch - thanks for the numeric and text difference. And apologies as Jez's adaptation fixed my code so had to have most of the points.
Thanks guys!
Hi matthewspatrick, I think your solution worked for my original question, but it was my code that wasn't upto scratch - thanks for the numeric and text difference. And apologies as Jez's adaptation fixed my code so had to have most of the points.
Thanks guys!
If you want to go the record set route, you should have something like this:
Public Sub XXXCheckFieldsXXX()
Dim rstDeterminants As Recordset
Dim rstDataIntermediate As Recordset
Set rstDeterminants = CurrentDb.OpenRecordset("Determinants", dbOpenSnapshot, dbReadOnly) ' Change name of source table
If rstDeterminants.EOF Then
MsgBox "No records!"
Exit Sub
End If
Set rstDataIntermediate = CurrentDb.OpenRecordset("Data_Intermediate", dbOpenTable) ' Change name of destination table
' For each Data_Intermediate record
Do Until rstDataIntermediate.EOF
' For each Determinants record
rstDeterminants.MoveFirst
Do Until rstDeterminants.EOF
If rstDeterminants![EA Code] = rstDataIntermediate!DeterminantID Then
rstDataIntermediate.Edit
rstDataIntermediate!DeterminantID = rstDeterminants!DeterminantID
rstDataIntermediate.Update
End If
rstDeterminants.MoveNext
Loop
rstDataIntermediate.MoveNext
Loop
End Sub
The problem with the code in your question (apart from it not working!) is that you've got the overhead of compiling an SQL statement for EACH combination of records in your Determinants and Data_Intermediate tables.
Since you're looping through the Data_Intermediate table records anyway, it makes sense to edit each record as you go by (so to speak).
Since you're looping through the Data_Intermediate table records anyway, it makes sense to edit each record as you go by (so to speak).
Once you add in the missing MoveFirst invocation (see ID 21324051 above), you can see that even the corrected record set strategy is rather inefficient, since it repeatedly goes back to the first record of the Determinants table for each record of the Data_Intermediate table.
By paring down the entire algorithm so a single UPDATE query Access only has to read each table once, which not only makes the whole process much faster but also gives you less code to maintain.
Less is more, as they say! :-)
By paring down the entire algorithm so a single UPDATE query Access only has to read each table once, which not only makes the whole process much faster but also gives you less code to maintain.
Less is more, as they say! :-)
Just for future reference, you might like to bear in mind the other little tweaks I've made as well:
1. Removed unused variables
2. Reordered code to ONLY open Determinants record set when no records
3. Opened record sets as read-only where appropriate
4. Removed repeated MoveNext code
5. Relaced variables with direct record set field references
6. Used more consise field referencing
Hope that helps explain what I changed! :-)
1. Removed unused variables
2. Reordered code to ONLY open Determinants record set when no records
3. Opened record sets as read-only where appropriate
4. Removed repeated MoveNext code
5. Relaced variables with direct record set field references
6. Used more consise field referencing
Hope that helps explain what I changed! :-)
ASKER
Jez that is fantastic!!
Thanks for taking both the time to correct my code and explain it. I've been punching above my weight with VBA code trying to learn it so this really helps a great deal.
Its all much appreciated, keep up the good work!!
Thanks for taking both the time to correct my code and explain it. I've been punching above my weight with VBA code trying to learn it so this really helps a great deal.
Its all much appreciated, keep up the good work!!
Have a knockout time! :-)
This means that the update will only be performed for the FIRST record of your Determinats table!