Run Delete Query from list in a table

[ccondran08]

Just wondering if this is possible. If I have the list of several delete query names in a table along with a sort order field, are you able to execute the queries in Oder of the names in the table one after the other ???

 

[JHB]

.. are you able to execute the queries in Oder of the names in the table one after the other ???

Yes, use a recordset and a loop.

 

[ccondran08]

Can you please show me an example ?

 

[JHB]

An code example below:

Private Sub DeleteBut_Click()
  Dim rst As DAO.Recordset
  
  Set rst = CurrentDb.OpenRecordset("YourTableName")
  If Not rst.EOF Then
    Do
      CurrentDb.Execute (rst![YourFieldNameWithTheQueryName])
      rst.MoveNext
    Loop Until rst.EOF
  End If
  
End Sub

 

[ccondran08]

I'm getting a few errors here. Can you please point me in the right direction ;

The table with the query names is called "tbl_Query_Order" and there is an ID field and a Query_Name field which has two records in there called "qry_Delete1" and "qry_Delete2". Both of these query names are delete queries.

Private Sub Command11_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tbl_Query_Order")
If Not rst.EOF Then
Do
CurrentDb.Execute (rst![Query_Name])
rst.MoveNext
Loop Until rst.EOF
End If
End Sub

Thanks in advance, appreciate your help.

 

[ccondran08]

The error I get is "The expression On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control."

 

[JHB]

Sound like your database is corrupt, try a "Compact & Repair", or create a new database and copy all into it.
I've attached an example database how to delete tables.

 

[Galaxiom]

Set rst = CurrentDb.OpenRecordset("tbl_Query_Order")

Note that list of queries in the table will be returned to the recordset in no particular order. You must order them using a query.

 

[ccondran08]

I've attached the file that I'm trying to complete.

 

[JHB]

As I wrote before, your database is corrupt, create a new one and import all into it.
Second, your code is with errors, you've declared a variable rs, but in some places you use rst.

Private Sub Command11_Click()
    Dim [B][COLOR=Red]rs[/COLOR][/B] As DAO.Recordset
    Dim db As Database
    
    Set db = CurrentDb
    Set [B][COLOR=Red]rs[/COLOR][/B] = CurrentDb.OpenRecordset("tbl_Query_Order")
    If Not [B][COLOR=Red]rst[/COLOR][/B].EOF Then
        Do
            CurrentDb.Execute [COLOR=Red][B]rst[/B][/COLOR]([Query_Name])
            [B][COLOR=Red]rst[/COLOR][/B].MoveNext
        Loop Until [B][COLOR=Red]rst[/COLOR][/B].EOF
    End If
End Sub

 

[ccondran08]

For some reason, it still didn't work...however, I have found a work around which will do the same job, which is to give the user the option of which priority the query should run in. Appreciate your help JHB.

http://www.access-programmers.co.uk/forums/showthread.php?t=287721

 

[Galaxiom]

For some reason, it still didn't work

It is probably because the form itself was corrupted. Importing a corrupted object into a new database will still leave it corrupted.

And do be careful with the Project name as I said in the other thread. Avoid reserved words and be sure not to have the name of the projects the same as any procedure or object.

 

[JHB]

For some reason, it still didn't work...

By me it works (after importing it into a new database and correcting the errors in the code - didn't mention that code line before CurrentDb.Execute rs!([Query_Name]) should be CurrentDb.Execute rs![Query_Name] )!
Good you found another solution.