Determine if query is action query from code

[Kerry]

Hi all,

How can I tell if a querry is an action query from VB code?

 

[Douglas J. Steele]

Take a look at the Type property of the QueryDef object:

CurrentDb().QueryDefs("NameOfQuery").Type

Valid values are:

For a QueryDef object, the possible settings and return values are shown in
the following table.

Constant Query type
dbQAction Action
dbQAppend Append
dbQCompound Compound
dbQCrosstab Crosstab
dbQDDL Data-definition
dbQDelete Delete
dbQMakeTable Make-table
dbQProcedure Procedure (ODBCDirect workspaces only)
dbQSelect Select
dbQSetOperation Union
dbQSPTBulk Used with dbQSQLPassThrough to specify a query
that doesn't return records
dbQSQLPassThrough Pass-through
dbQUpdate Update

You can also get this from the MSysObjects table (it's the Flags field):

DLookup("Flags", "MSysObjects", "[Name]='NameOfQuery'")

 

[Kerry]

Hi Doug,

Thanks, next related question.

When I loop through all the queries, it also looks at deleted queries
since the database was last compacted. How can I tell if a query has
been deleted?

Code:
Rem count the queries in the database
intQueryCount = CurrentDb.QueryDefs.Count
Rem Now loop through all of the queries
For intQuery = 0 To intQueryCount - 1
Rem first get the query name and type
strQuery = CurrentDb.QueryDefs(intQuery).Name
Select Case CurrentDb.QueryDefs(intQuery).Type
Case dbQAction, dbQAppend, dbQDelete, dbQMakeTable,
dbQUpdate
GoTo ActionQuery
Case Else
GoTo NonActionQuery
End Select

ActionQuery:
strSQL = "Insert into ActionQueries(Query_Name) values "
strSQL = strSQL & "(""" & strQuery & """);"
DoCmd.RunSQL (strSQL)
NonActionQuery:
Next intQuery

Take a look at the Type property of the QueryDef object:

CurrentDb().QueryDefs("NameOfQuery").Type

Valid values are:

For a QueryDef object, the possible settings and return values are shown in
the following table.

Constant Query type
dbQAction Action
dbQAppend Append
dbQCompound Compound
dbQCrosstab Crosstab
dbQDDL Data-definition
dbQDelete Delete
dbQMakeTable Make-table
dbQProcedure Procedure (ODBCDirect workspaces only)
dbQSelect Select
dbQSetOperation Union
dbQSPTBulk Used with dbQSQLPassThrough to specify a query
that doesn't return records
dbQSQLPassThrough Pass-through
dbQUpdate Update

You can also get this from the MSysObjects table (it's the Flags field):

DLookup("Flags", "MSysObjects", "[Name]='NameOfQuery'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,

How can I tell if a querry is an action query from VB code?

 

[Douglas J. Steele]

You sure it's looking at deleted queries, or are you seeing the QueryDef
objects that get created when you put a SQL string as the RecordSource for a
form, or the RowSource for a combo box or list box? What do the names of the
queries in question look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

Thanks, next related question.

When I loop through all the queries, it also looks at deleted queries
since the database was last compacted. How can I tell if a query has
been deleted?

Code:
Rem count the queries in the database
intQueryCount = CurrentDb.QueryDefs.Count
Rem Now loop through all of the queries
For intQuery = 0 To intQueryCount - 1
Rem first get the query name and type
strQuery = CurrentDb.QueryDefs(intQuery).Name
Select Case CurrentDb.QueryDefs(intQuery).Type
Case dbQAction, dbQAppend, dbQDelete, dbQMakeTable,
dbQUpdate
GoTo ActionQuery
Case Else
GoTo NonActionQuery
End Select

ActionQuery:
strSQL = "Insert into ActionQueries(Query_Name) values "
strSQL = strSQL & "(""" & strQuery & """);"
DoCmd.RunSQL (strSQL)
NonActionQuery:
Next intQuery

Take a look at the Type property of the QueryDef object:

CurrentDb().QueryDefs("NameOfQuery").Type

Valid values are:

For a QueryDef object, the possible settings and return values are shown in
the following table.

Constant Query type
dbQAction Action
dbQAppend Append
dbQCompound Compound
dbQCrosstab Crosstab
dbQDDL Data-definition
dbQDelete Delete
dbQMakeTable Make-table
dbQProcedure Procedure (ODBCDirect workspaces only)
dbQSelect Select
dbQSetOperation Union
dbQSPTBulk Used with dbQSQLPassThrough to specify a query
that doesn't return records
dbQSQLPassThrough Pass-through
dbQUpdate Update

You can also get this from the MSysObjects table (it's the Flags field):

DLookup("Flags", "MSysObjects", "[Name]='NameOfQuery'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,

How can I tell if a querry is an action query from VB code?

 

[Kerry]

Hi Doug,

The particular database I was using the code for has no forms. I am
sure it is a deleted query. The name of the query in question is
something like:
~Q...
It doesn't look at deleted queries if I use DLookup("Flags",
"MSysObjects", "[Name]='" & CurrentDb.QueryDefs(intQuery).Name & "'),
but it does when I use CurrentDb.QueryDefs(intQuery).Type

It is not vital, since the flags works, but I am curious.

You sure it's looking at deleted queries, or are you seeing the QueryDef
objects that get created when you put a SQL string as the RecordSource for a
form, or the RowSource for a combo box or list box? What do the names of the
queries in question look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

Thanks, next related question.

When I loop through all the queries, it also looks at deleted queries
since the database was last compacted. How can I tell if a query has
been deleted?

Code:
Rem count the queries in the database
intQueryCount = CurrentDb.QueryDefs.Count
Rem Now loop through all of the queries
For intQuery = 0 To intQueryCount - 1
Rem first get the query name and type
strQuery = CurrentDb.QueryDefs(intQuery).Name
Select Case CurrentDb.QueryDefs(intQuery).Type
Case dbQAction, dbQAppend, dbQDelete, dbQMakeTable,
dbQUpdate
GoTo ActionQuery
Case Else
GoTo NonActionQuery
End Select

ActionQuery:
strSQL = "Insert into ActionQueries(Query_Name) values "
strSQL = strSQL & "(""" & strQuery & """);"
DoCmd.RunSQL (strSQL)
NonActionQuery:
Next intQuery

Take a look at the Type property of the QueryDef object:

CurrentDb().QueryDefs("NameOfQuery").Type

Valid values are:

For a QueryDef object, the possible settings and return values are shown in
the following table.

Constant Query type
dbQAction Action
dbQAppend Append
dbQCompound Compound
dbQCrosstab Crosstab
dbQDDL Data-definition
dbQDelete Delete
dbQMakeTable Make-table
dbQProcedure Procedure (ODBCDirect workspaces only)
dbQSelect Select
dbQSetOperation Union
dbQSPTBulk Used with dbQSQLPassThrough to specify a query
that doesn't return records
dbQSQLPassThrough Pass-through
dbQUpdate Update

You can also get this from the MSysObjects table (it's the Flags field):

DLookup("Flags", "MSysObjects", "[Name]='NameOfQuery'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all,

How can I tell if a querry is an action query from VB code?

 

[Douglas J. Steele]

Not sure what you mean that it doesn't look at deleted queries one way, but
it doesn't the other, given you're looping through the QueryDefs collection
in both cases.

I've not heard of this before, so I have no suggestions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

The particular database I was using the code for has no forms. I am
sure it is a deleted query. The name of the query in question is
something like:
~Q...
It doesn't look at deleted queries if I use DLookup("Flags",
"MSysObjects", "[Name]='" & CurrentDb.QueryDefs(intQuery).Name & "'),
but it does when I use CurrentDb.QueryDefs(intQuery).Type

It is not vital, since the flags works, but I am curious.

You sure it's looking at deleted queries, or are you seeing the QueryDef
objects that get created when you put a SQL string as the RecordSource for a
form, or the RowSource for a combo box or list box? What do the names of the
queries in question look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

Thanks, next related question.

When I loop through all the queries, it also looks at deleted queries
since the database was last compacted. How can I tell if a query has
been deleted?

Code:
Rem count the queries in the database
intQueryCount = CurrentDb.QueryDefs.Count
Rem Now loop through all of the queries
For intQuery = 0 To intQueryCount - 1
Rem first get the query name and type
strQuery = CurrentDb.QueryDefs(intQuery).Name
Select Case CurrentDb.QueryDefs(intQuery).Type
Case dbQAction, dbQAppend, dbQDelete, dbQMakeTable,
dbQUpdate
GoTo ActionQuery
Case Else
GoTo NonActionQuery
End Select

ActionQuery:
strSQL = "Insert into ActionQueries(Query_Name) values "
strSQL = strSQL & "(""" & strQuery & """);"
DoCmd.RunSQL (strSQL)
NonActionQuery:
Next intQuery

Douglas J. Steele wrote:
Take a look at the Type property of the QueryDef object:

CurrentDb().QueryDefs("NameOfQuery").Type

Valid values are:

For a QueryDef object, the possible settings and return values are

shown
in

the following table.

Constant Query type
dbQAction Action
dbQAppend Append
dbQCompound Compound
dbQCrosstab Crosstab
dbQDDL Data-definition
dbQDelete Delete
dbQMakeTable Make-table
dbQProcedure Procedure (ODBCDirect workspaces only)
dbQSelect Select
dbQSetOperation Union
dbQSPTBulk Used with dbQSQLPassThrough to specify a query
that doesn't return records
dbQSQLPassThrough Pass-through
dbQUpdate Update

You can also get this from the MSysObjects table (it's the Flags field):

DLookup("Flags", "MSysObjects", "[Name]='NameOfQuery'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all,

How can I tell if a querry is an action query from VB code?

 

[Kerry]

Hi Doug,

It does look at deleted queries both ways. It has different values if
the query is deleted in the different ways. If the query is not
deleted, the flag and the type have the same value, but if the query is
deleted, for a select query, the flag is 3 and the type is 0. So if I
only look at the type, I can not tell if a query is deleted.

Not sure what you mean that it doesn't look at deleted queries one way, but
it doesn't the other, given you're looping through the QueryDefs collection
in both cases.

I've not heard of this before, so I have no suggestions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

The particular database I was using the code for has no forms. I am
sure it is a deleted query. The name of the query in question is
something like:
~Q...
It doesn't look at deleted queries if I use DLookup("Flags",
"MSysObjects", "[Name]='" & CurrentDb.QueryDefs(intQuery).Name & "'),
but it does when I use CurrentDb.QueryDefs(intQuery).Type

It is not vital, since the flags works, but I am curious.

You sure it's looking at deleted queries, or are you seeing the QueryDef
objects that get created when you put a SQL string as the RecordSource for a
form, or the RowSource for a combo box or list box? What do the names of the
queries in question look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug,

Thanks, next related question.

When I loop through all the queries, it also looks at deleted queries
since the database was last compacted. How can I tell if a query has
been deleted?

Code:
Rem count the queries in the database
intQueryCount = CurrentDb.QueryDefs.Count
Rem Now loop through all of the queries
For intQuery = 0 To intQueryCount - 1
Rem first get the query name and type
strQuery = CurrentDb.QueryDefs(intQuery).Name
Select Case CurrentDb.QueryDefs(intQuery).Type
Case dbQAction, dbQAppend, dbQDelete, dbQMakeTable,
dbQUpdate
GoTo ActionQuery
Case Else
GoTo NonActionQuery
End Select

ActionQuery:
strSQL = "Insert into ActionQueries(Query_Name) values "
strSQL = strSQL & "(""" & strQuery & """);"
DoCmd.RunSQL (strSQL)
NonActionQuery:
Next intQuery

Douglas J. Steele wrote:
Take a look at the Type property of the QueryDef object:

CurrentDb().QueryDefs("NameOfQuery").Type

Valid values are:

For a QueryDef object, the possible settings and return values are shown
in
the following table.

Constant Query type
dbQAction Action
dbQAppend Append
dbQCompound Compound
dbQCrosstab Crosstab
dbQDDL Data-definition
dbQDelete Delete
dbQMakeTable Make-table
dbQProcedure Procedure (ODBCDirect workspaces only)
dbQSelect Select
dbQSetOperation Union
dbQSPTBulk Used with dbQSQLPassThrough to specify a
query
that doesn't return records
dbQSQLPassThrough Pass-through
dbQUpdate Update

You can also get this from the MSysObjects table (it's the Flags field):

DLookup("Flags", "MSysObjects", "[Name]='NameOfQuery'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all,

How can I tell if a querry is an action query from VB code?

 

[Douglas J. Steele]

Check both values, then.

Alternatively, only run your routine directly after having compacted. <g>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,

It does look at deleted queries both ways. It has different values if
the query is deleted in the different ways. If the query is not
deleted, the flag and the type have the same value, but if the query is
deleted, for a select query, the flag is 3 and the type is 0. So if I
only look at the type, I can not tell if a query is deleted.

Not sure what you mean that it doesn't look at deleted queries one way,
but
it doesn't the other, given you're looping through the QueryDefs
collection
in both cases.

I've not heard of this before, so I have no suggestions.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

The particular database I was using the code for has no forms. I am
sure it is a deleted query. The name of the query in question is
something like:
~Q...
It doesn't look at deleted queries if I use DLookup("Flags",
"MSysObjects", "[Name]='" & CurrentDb.QueryDefs(intQuery).Name & "'),
but it does when I use CurrentDb.QueryDefs(intQuery).Type

It is not vital, since the flags works, but I am curious.

Douglas J. Steele wrote:
You sure it's looking at deleted queries, or are you seeing the
QueryDef
objects that get created when you put a SQL string as the
RecordSource for a
form, or the RowSource for a combo box or list box? What do the names
of the
queries in question look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug,

Thanks, next related question.

When I loop through all the queries, it also looks at deleted
queries
since the database was last compacted. How can I tell if a query
has
been deleted?

Code:
Rem count the queries in the database
intQueryCount = CurrentDb.QueryDefs.Count
Rem Now loop through all of the queries
For intQuery = 0 To intQueryCount - 1
Rem first get the query name and type
strQuery = CurrentDb.QueryDefs(intQuery).Name
Select Case CurrentDb.QueryDefs(intQuery).Type
Case dbQAction, dbQAppend, dbQDelete, dbQMakeTable,
dbQUpdate
GoTo ActionQuery
Case Else
GoTo NonActionQuery
End Select

ActionQuery:
strSQL = "Insert into ActionQueries(Query_Name) values "
strSQL = strSQL & "(""" & strQuery & """);"
DoCmd.RunSQL (strSQL)
NonActionQuery:
Next intQuery

Douglas J. Steele wrote:
Take a look at the Type property of the QueryDef object:

CurrentDb().QueryDefs("NameOfQuery").Type

Valid values are:

For a QueryDef object, the possible settings and return values
are shown
in
the following table.

Constant Query type
dbQAction Action
dbQAppend Append
dbQCompound Compound
dbQCrosstab Crosstab
dbQDDL Data-definition
dbQDelete Delete
dbQMakeTable Make-table
dbQProcedure Procedure (ODBCDirect workspaces only)
dbQSelect Select
dbQSetOperation Union
dbQSPTBulk Used with dbQSQLPassThrough to
specify a
query
that doesn't return records
dbQSQLPassThrough Pass-through
dbQUpdate Update

You can also get this from the MSysObjects table (it's the Flags field):

DLookup("Flags", "MSysObjects", "[Name]='NameOfQuery'")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all,

How can I tell if a querry is an action query from VB code?