By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,915 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Rerun update queries in a macro

P: 3
I am a PhD student in demography and I am working with a Microsoft Access database in which I have to use several update queries.
Amongst these there are some in which I have to update the value of a field in a row to the value of the same field from the previous row (according to some parameters). For example:
Expand|Select|Wrap|Line Numbers
  1. UPDATE [Table] INNER JOIN [Table] AS [Table_1] ON [Table].ID = [Table_1].ID SET [Table_1].Variable = [Table].[Variable]
  2. WHERE ((([Table_1].Variable) Is Null) AND (([Table_1].Order)=[Table].[Order]+1) AND (([Table].Variable) Is Not Null));
I need to run these queries many times until it tells me that I am about to update 0 rows.

Since I have a large number of these queries and I have to run each many times I would like to use a macro. Is there a way of telling the macro that the query should be run many times until it can make no more updates?

Thank you very much for your help!
Jul 30 '08 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,597
  1. You can regulate the number of times a Macro runs by setting the RepeatCount Argument of the RunMacro Method. You can also run a Macro repeatedly until a Boolean Expression evaluates to False by setting the RepeatExpression Argument Argument of the RunMacro Method. Both Arguments are Optional, but if you specify repeatExpression with RepeatCount, you must supply a comma as a place hloder for the missing Argument. The Syntax is listed below:
    Expand|Select|Wrap|Line Numbers
    1. expression.RunMacro(MacroName, RepeatCount, RepeatExpression)
    2. DoCmd.RunMacro "<Your Macro Name>", RepeatCount, RepeatExpression
    3.  
    4. expression    Required. An expression that returns one of the objects in the Applies To list.
    5.  
    6. MacroName   Required Variant. A string expression that's the valid name of a macro in the current database. If you run Visual Basic code containing the RunMacro method in a library database, Microsoft Access looks for the macro with this name in the library database and doesn't look for it in the current database.
    7.  
    8. RepeatCount   Optional Variant. A numeric expression that evaluates to an integer, which is the number of times the macro will run.
    9.  
    10. RepeatExpression   Optional Variant. A numeric expression that's evaluated each time the macro runs. When it evaluates to False (0), the macro stops running.
  2. I've never attempted it, but I assume you can run your Update Procedures within a Loop, and use the RecordsAffected Property of the Database Object after an Execute() Method. When RecordsAffected = 0, then it will be time to exit the Loop. You may wish to consider wrapping all these Updates into a Transaction, wheras all Updates succed or none do.
Jul 30 '08 #2

P: 3
Thank you very much for your answer. I am not very familiar with visual basic, so I am having a hard time setting up the code. This is what Iīve written so far:
Expand|Select|Wrap|Line Numbers
  1. Function test1()
  2. DoCmd.OpenQuery "FirstUpdateQuery"
  3. Exit Function
  4. Dim CountRecsAffected As Long
  5. Do While CountRecsAffected > 0
  6. test1.Execute CountRecsAffected
  7. Loop
  8.  
  9. End Function
It is not correct, though, since I only get the query to run once...I donīt know if the error is in how i use the DoCmd.OpenQuery, how I implement the loop or in how i try to obtain the records affected
Aug 1 '08 #3

ADezii
Expert 5K+
P: 8,597
Thank you very much for your answer. I am not very familiar with visual basic, so I am having a hard time setting up the code. This is what Iīve written so far:

Function test1()
DoCmd.OpenQuery "FirstUpdateQuery"
Exit Function
Dim CountRecsAffected As Long
Do While CountRecsAffected > 0
test1.Execute CountRecsAffected
Loop

End Function

It is not correct, though, since I only get the query to run once...I donīt know if the error is in how i use the DoCmd.OpenQuery, how I implement the loop or in how i try to obtain the records affected
Look up the RecordsAffected Property and the Execute() Method in the Help Files. If you are still stuck, let me know. BTW, why can't all the Updates be done in a single pass?
Aug 1 '08 #4

ADezii
Expert 5K+
P: 8,597
Thank you very much for your answer. I am not very familiar with visual basic, so I am having a hard time setting up the code. This is what Iīve written so far:

Function test1()
DoCmd.OpenQuery "FirstUpdateQuery"
Exit Function
Dim CountRecsAffected As Long
Do While CountRecsAffected > 0
test1.Execute CountRecsAffected
Loop

End Function

It is not correct, though, since I only get the query to run once...I donīt know if the error is in how i use the DoCmd.OpenQuery, how I implement the loop or in how i try to obtain the records affected
The general logic would be:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim MyDB As DAO.Database
  3.  
  4. Set MyDB = CurrentDb()
  5.  
  6. Do Until MyDB.RecordsAffected = 0
  7.   'Modify SQL Statement within Loop
  8.   strSQL = "<Your SQL Statement here>;"
  9.  
  10.   MyDB.Execute strSQL, dbFailOnError    'Execute Statement
  11. Loop
Aug 2 '08 #5

P: 3
The general logic would be:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim MyDB As DAO.Database
  3.  
  4. Set MyDB = CurrentDb()
  5.  
  6. Do Until MyDB.RecordsAffected = 0
  7.   'Modify SQL Statement within Loop
  8.   strSQL = "<Your SQL Statement here>;"
  9.  
  10.   MyDB.Execute strSQL, dbFailOnError    'Execute Statement
  11. Loop

I managed to get it working, using Loop While .. instead of Do until..
The code I used is :

Expand|Select|Wrap|Line Numbers
  1. Sub Code()
  2. Dim MyDB As DAO.Database
  3. Set MyDB = CurrentDb()
  4. Do
  5. MyDB.Execute ("FirstUpdateQuery"), dbFailOnError    'Execute Statement
  6. Loop While MyDB.RecordsAffected > 0
  7.  
  8. End Sub
  9.  
Thank you so much for your precious help!
Aug 4 '08 #6

ADezii
Expert 5K+
P: 8,597
I managed to get it working, using Loop While .. instead of Do until..
The code I used is :

Expand|Select|Wrap|Line Numbers
  1. Sub Code()
  2. Dim MyDB As DAO.Database
  3. Set MyDB = CurrentDb()
  4. Do
  5. MyDB.Execute ("FirstUpdateQuery"), dbFailOnError    'Execute Statement
  6. Loop While MyDB.RecordsAffected > 0
  7.  
  8. End Sub
  9.  
Thank you so much for your precious help!
You are quite welcome, and congratulations for getting it working.
Aug 4 '08 #7

NeoPa
Expert Mod 15k+
P: 31,186
Thank you very much for your answer. I am not very familiar with visual basic, so I am having a hard time setting up the code. This is what Iīve written so far:
Expand|Select|Wrap|Line Numbers
  1. Function test1()
  2. DoCmd.OpenQuery "FirstUpdateQuery"
  3. Exit Function
  4. Dim CountRecsAffected As Long
  5. Do While CountRecsAffected > 0
  6. test1.Execute CountRecsAffected
  7. Loop
  8.  
  9. End Function
It is not correct, though, since I only get the query to run once...I donīt know if the error is in how i use the DoCmd.OpenQuery, how I implement the loop or in how i try to obtain the records affected
I don't know if you managed to catch this before, but line 3 exits the function explicitly. This would explain why the looping never worked.

Welcome to Bytes!
Aug 5 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.