Run queries that are named in a table.

[ashley25]

Basically I want to execute every query within my database that exists in one of my tables. It should fill a recordset with the query names and loop through the database and execute each one.

When I try and run access keeps saying run-time error 3061 too few parameters (yellow line). I know the issues is with the sql, but not sure how to get around it or if this type of query is do-able.

My code below:

Public Sub C38()
Dim rst As DAO.Recordset
Dim DB As Database
Set DB = CurrentDb()
Set rst = DB.OpenRecordset("mTable")
On Error Resume Next
Do While Not rst.EOF
'MsgBox rst!myQuery
DB.Execute rst!myQuery, dbFailOnError
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

 

[boerbende]

Replace
DoCmd.OpenQuery "myQuery"
with
DoCmd.OpenQuery myQuery
?'
When you read myquery from the table, this should be a string already

 

[Mile-O]

If you're getting the Too Few Parameters error then it's likely you're going to have to look into the QueryDef object and its Parameters collection, as it sounds like the queries you are trying to open need their parameters defined.

 

[ashley25]

I've updated my code, but it would appear the table with queries is not being picked up. The code doesn't do anything when I run it.

 

[boerbende]

Is it the difference between docmd.runqls and db.execute?

https://social.msdn.microsoft.com/F...aea8/docmdrunsql-vs-dbexecute?forum=accessdev

 

[Mile-O]

Or even:

DoCmd.OpenQuery "QueryName"

 

[ashley25]

I have sorted this. The loop was being skipped over so I replaced Do While Not rst.BOF And rst.EOF with Do While Not rst.EOF.