Run multiple queries

[achick]

I want to run multiple update queries(aorund 25 statements)
Eg:
update table1 set col2=null where col3 is null;
update table1 set col4=null where col5 is null;

Is there an easy way to run all the update staments by placing all of them at the back of a command button or can I use modules?

If I want to know how many rows got updated for each statement, is there a way to set it?

 

[Remou]

You can list the queries as sql statements. Here is an example.

Sub UpdateQueries()
Dim db As Database

Set db = CurrentDb

'First query
strSQL = "update table1 set col2=null where col3 is null"

db.Execute strSQL

'Number of records updated
Debug.Print db.RecordsAffected

'<and so on>
End Sub

http://lessthandot.com

 

[Remou]

Oops. That should read:

[tt]db.Execute strSQL, dbFailOnError[/tt]

http://lessthandot.com

 

[TheAceMan1]

How are ya achick . . .

Here's an idea:

[blue]   Dim db As DAO.Database, SQL As String
   Dim xFieldName As String, yFieldName As String, idx As Integer
   
   For x = 1 To 2 [green]'change according number of items in Choose[/green]
      xFieldName = Choose(x, "col2", "col4")
      yFieldName = Choose(x, "col3", "col5") 
      
      SQL = "UPDATE table1 " & _
            "SET " & xFieldName & "=Null " & _
            "WHERE (" & yFieldName & " IS Null);"
      db.Execute SQL, dbFailOnError
      DoEvents
   Next[/blue]

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[achick]

Thankyou both, they worked great.

 

[TheAceMan1]

achick . . .

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]