Through VBA code open objects stored in table and run these objects

[desilusc]

I have stored some action query object names in a table. I created a query
off of this table to isolate and sort the action queries that I want to call.
Through VBA I want to open this query and then run these query names that are
in the recordset in the sorted order (query sorts). I also do not want the
warnings to pop up.

I’m somewhat new to VBA and am not clear on the specific code. Can anyone
help me?

Thank you.
Desilusc

 

[John Spencer]

UNTESTED AIRCODE follows. Test it out on a COPY of your database to make sure
it runs and it does what you want it to do.

Public Sub fRunTheQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb
strSQL = "SELECT QueryNames FROM QueryTable" & _
" WHERE QueryName is not NULL ORDER BY OrderOfQueries"
Set rst = db.OpenRecordset(strSQL, dbFailOnError)

If rst.RecordCount > 0 Then
While rst.EOF = False
db.Execute rst!queryNames, dbFailOnError
rst.MoveNext
Wend

End If

Exit Sub

Proc_Error:
MsgBox Err.Number & ":" & Err.Description
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[desilusc]

I get an error on the code:

db.Execute rst!queryNames, dbFailOnError

I substituted "queryNames" for the field name in my SQL statement.

Thanks.
Desilusc

UNTESTED AIRCODE follows. Test it out on a COPY of your database to make sure
it runs and it does what you want it to do.

Public Sub fRunTheQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb
strSQL = "SELECT QueryNames FROM QueryTable" & _
" WHERE QueryName is not NULL ORDER BY OrderOfQueries"
Set rst = db.OpenRecordset(strSQL, dbFailOnError)

If rst.RecordCount > 0 Then
While rst.EOF = False
db.Execute rst!queryNames, dbFailOnError
rst.MoveNext
Wend

End If

Exit Sub

Proc_Error:
MsgBox Err.Number & ":" & Err.Description
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have stored some action query object names in a table. I created a query
off of this table to isolate and sort the action queries that I want to call.

[quoted text clipped - 7 lines]

Thank you.
Desilusc

 

[desilusc]

Here's the code I used that I'm having trouble with:

Public Sub fRunTheQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb

strSQL = "SELECT tblObjects.MyObjectName, tblObjects.RunOrder, tblObjects.
Action, tblObjects.RUN_Action_Qry FROM tblObjects" & _
"WHERE (((tblObjects.Action) = 'Yes') And ((tblObjects.RUN_Action_Qry) =
'Yes'))" & _
"ORDER BY tblObjects.RunOrder"
Set rst = db.OpenRecordset(strSQL, dbFailOnError)
If rst.RecordCount > 0 Then
While rst.EOF = False
db.Execute rst!MyObjectName, dbFailOnError
rst.MoveNext
Wend

End If

Exit Sub

Proc_Error:
MsgBox Err.Number & ":" & Err.Description
End Sub

Desilusc

UNTESTED AIRCODE follows. Test it out on a COPY of your database to make sure
it runs and it does what you want it to do.

Public Sub fRunTheQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb
strSQL = "SELECT QueryNames FROM QueryTable" & _
" WHERE QueryName is not NULL ORDER BY OrderOfQueries"
Set rst = db.OpenRecordset(strSQL, dbFailOnError)

If rst.RecordCount > 0 Then
While rst.EOF = False
db.Execute rst!queryNames, dbFailOnError
rst.MoveNext
Wend

End If

Exit Sub

Proc_Error:
MsgBox Err.Number & ":" & Err.Description
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have stored some action query object names in a table. I created a query
off of this table to isolate and sort the action queries that I want to call.

[quoted text clipped - 7 lines]

Thank you.
Desilusc

 

[desilusc]

Here's the code I used that I'm having trouble with:

Public Sub fRunTheQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb

strSQL = "SELECT tblObjects.MyObjectName, tblObjects.RunOrder, tblObjects.
Action, tblObjects.RUN_Action_Qry FROM tblObjects" & _
"WHERE (((tblObjects.Action) = 'Yes') And ((tblObjects.RUN_Action_Qry) =
'Yes'))" & _
"ORDER BY tblObjects.RunOrder"
Set rst = db.OpenRecordset(strSQL, dbFailOnError)
If rst.RecordCount > 0 Then
While rst.EOF = False
db.Execute rst!MyObjectName, dbFailOnError
rst.MoveNext
Wend

End If

Exit Sub

Proc_Error:
MsgBox Err.Number & ":" & Err.Description
End Sub

Desilusc

UNTESTED AIRCODE follows. Test it out on a COPY of your database to make sure
it runs and it does what you want it to do.

Public Sub fRunTheQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb
strSQL = "SELECT QueryNames FROM QueryTable" & _
" WHERE QueryName is not NULL ORDER BY OrderOfQueries"
Set rst = db.OpenRecordset(strSQL, dbFailOnError)

If rst.RecordCount > 0 Then
While rst.EOF = False
db.Execute rst!queryNames, dbFailOnError
rst.MoveNext
Wend

End If

Exit Sub

Proc_Error:
MsgBox Err.Number & ":" & Err.Description
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have stored some action query object names in a table. I created a query
off of this table to isolate and sort the action queries that I want to call.

[quoted text clipped - 7 lines]

Thank you.
Desilusc

 

[desilusc]

Here's the code I used that I'm having trouble with:

Public Sub fRunTheQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb

strSQL = "SELECT tblObjects.MyObjectName, tblObjects.RunOrder, tblObjects.
Action, tblObjects.RUN_Action_Qry FROM tblObjects" & _
"WHERE (((tblObjects.Action) = 'Yes') And ((tblObjects.RUN_Action_Qry) =
'Yes'))" & _
"ORDER BY tblObjects.RunOrder"
Set rst = db.OpenRecordset(strSQL, dbFailOnError)
If rst.RecordCount > 0 Then
While rst.EOF = False
db.Execute rst!MyObjectName, dbFailOnError
rst.MoveNext
Wend

End If

Exit Sub

Proc_Error:
MsgBox Err.Number & ":" & Err.Description
End Sub

Desilusc

UNTESTED AIRCODE follows. Test it out on a COPY of your database to make sure
it runs and it does what you want it to do.

Public Sub fRunTheQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb
strSQL = "SELECT QueryNames FROM QueryTable" & _
" WHERE QueryName is not NULL ORDER BY OrderOfQueries"
Set rst = db.OpenRecordset(strSQL, dbFailOnError)

If rst.RecordCount > 0 Then
While rst.EOF = False
db.Execute rst!queryNames, dbFailOnError
rst.MoveNext
Wend

End If

Exit Sub

Proc_Error:
MsgBox Err.Number & ":" & Err.Description
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have stored some action query object names in a table. I created a query
off of this table to isolate and sort the action queries that I want to call.

[quoted text clipped - 7 lines]

Thank you.
Desilusc

 

[John Spencer]

What ERROR are you getting?

It could be he query based on strSQL is failing. What type of field are
Action and Run_Action_qry? If they are yes/no (boolean) fields then your SQL
should look like:

strSQL = "SELECT tblObjects.MyObjectName, tblObjects.RunOrder" & _
", tblObjects.Action, tblObjects.RUN_Action_Qry" & _
" FROM tblObjects" & _
" WHERE tblObjects.Action = TRUE " & _
" And tblObjects.RUN_Action_Qry = TRUE " & _
" ORDER BY tblObjects.RunOrder"

If that is not causing the failure then change the error section to read

MsgBox Rst!MyObjectName & " " & Err.Number & ":" & Err.Description

If you still can't figure out the error, then try insert a breakpoint and
stepping through the code to see where the error occurs.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Here's the code I used that I'm having trouble with:

Public Sub fRunTheQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb

strSQL = "SELECT tblObjects.MyObjectName, tblObjects.RunOrder, tblObjects.
Action, tblObjects.RUN_Action_Qry FROM tblObjects" & _
"WHERE (((tblObjects.Action) = 'Yes') And ((tblObjects.RUN_Action_Qry) =
'Yes'))" & _
"ORDER BY tblObjects.RunOrder"
Set rst = db.OpenRecordset(strSQL, dbFailOnError)
If rst.RecordCount > 0 Then
While rst.EOF = False
db.Execute rst!MyObjectName, dbFailOnError
rst.MoveNext
Wend

End If

Exit Sub

Proc_Error:
MsgBox Err.Number & ":" & Err.Description
End Sub

Desilusc

UNTESTED AIRCODE follows. Test it out on a COPY of your database to make sure
it runs and it does what you want it to do.

Public Sub fRunTheQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb
strSQL = "SELECT QueryNames FROM QueryTable" & _
" WHERE QueryName is not NULL ORDER BY OrderOfQueries"
Set rst = db.OpenRecordset(strSQL, dbFailOnError)

If rst.RecordCount > 0 Then
While rst.EOF = False
db.Execute rst!queryNames, dbFailOnError
rst.MoveNext
Wend

End If

Exit Sub

Proc_Error:
MsgBox Err.Number & ":" & Err.Description
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have stored some action query object names in a table. I created a query
off of this table to isolate and sort the action queries that I want to call.

[quoted text clipped - 7 lines]

Thank you.
Desilusc