Loop through Query Names?

[Jay]

Hi,

I'm trying to code a combo box and want to define a String variable and have
a For..Each..Next loop that goes through all the Queries in my database
(actually an .adp Access project) and add each query name to the string.

Then I can make my Combo's Row Source the string (after concatenating with
char(34) and ";"). This will enable the Combo to always show all the
queries in the database.

However, when I try and set my For Next statement there isn't a 'AllQueries'
option for me to add to the end of the following statement:

For Each AccessObject In CurrentProject.

Can anyone help me. Is there an 'AllQueries' collection that I can use? Any
help greatly appreciated..

Many thanks.....Jason

 

[Douglas J. Steele]

I know the following works in an MDB, but am not 100% positive for ADPs.
Simply set the RowSource to the following SQL:

SELECT Name
FROM MSysObjects
WHERE Type = 5
ORDER BY Name

 

[Brendan Reynolds]

Hi,

I'm trying to code a combo box and want to define a String variable and
have a For..Each..Next loop that goes through all the Queries in my
database (actually an .adp Access project) and add each query name to the
string.

Then I can make my Combo's Row Source the string (after concatenating with
char(34) and ";"). This will enable the Combo to always show all the
queries in the database.

However, when I try and set my For Next statement there isn't a
'AllQueries' option for me to add to the end of the following statement:

For Each AccessObject In CurrentProject.

Can anyone help me. Is there an 'AllQueries' collection that I can use?
Any help greatly appreciated..

Many thanks.....Jason

You need to use CurrentData rather than CurrentProject to access server-side
objects. The CurrentData object has an AllQueries collection as well as
AllStoredProcedures and AllViews collections. My memory is that the
AllQueries collection will include both sprocs and views, but it's been a
while, I could be wrong.

 

[Jay]

Thanks everyone for the responses.

I've tried 'For Each AccessObject In CurrentData.AllViews' and .AllQueries.
I've just tried it in immediate window and used Debug.Print...Name to check
the list returned.

The funny thing is, I have 1102 queries in the project, but AllQueries gives
me approx 200 and AllViews gives me approx 100.

So, for some reason there are ?700 names that I can't get at? Does anyone
know why this might be?

Thanks......Jason

 

[Jay]

Hi all,

I've managed to get my combo to return all my query names, but they're
not in alphabetical order. Does anyone know how I can sort it. my vba is:

Private Sub Form_Load()

'Populate Combo Box with all Query Names
Dim QueryAndTableList As String
QueryAndTableList = ""
'Store quotation mark as variable
Dim QM As String
QM = Chr(34)
'Loop through all Query Names
For Each AccessObject In CurrentData.AllQueries
QueryAndTableList = QueryAndTableList + QM + AccessObject.Name + ";"
Next

'Make ComboBox Row Source the Query List
Me!cboChooseQuery.RowSourceType = "Value List"
Me!cboChooseQuery.RowSource = QueryAndTableList

'Show first item as selected item in combo box
Me!cboChooseQuery.Value = Me!cboChooseQuery.ItemData(0)

End Sub

 

[Douglas J. Steele]

Did you try the suggestion I made?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi all,

I've managed to get my combo to return all my query names, but they're not
in alphabetical order. Does anyone know how I can sort it. my vba is:

Private Sub Form_Load()

'Populate Combo Box with all Query Names
Dim QueryAndTableList As String
QueryAndTableList = ""
'Store quotation mark as variable
Dim QM As String
QM = Chr(34)
'Loop through all Query Names
For Each AccessObject In CurrentData.AllQueries
QueryAndTableList = QueryAndTableList + QM + AccessObject.Name + ";"
Next

'Make ComboBox Row Source the Query List
Me!cboChooseQuery.RowSourceType = "Value List"
Me!cboChooseQuery.RowSource = QueryAndTableList

'Show first item as selected item in combo box
Me!cboChooseQuery.Value = Me!cboChooseQuery.ItemData(0)

End Sub

 

[Jay]

Hi Doug,

Yes I did, as you say, it workd in .mdb files, but for some reason the
Project doesn't like it. I get the erreor message:

'The record source 'SELECT........' specified on this form or report does
not exist'

I've also tried replacing MySysObjects with dbo.sysobjects (which works when
run on the fly in sql view) but this doesn't work either?

 

[Brendan Reynolds]

One solution would be to write the query names to a table, then use an ORDER
BY clause in a SQL statement to read them in the order you want. I included
two fields in my table for this example, QueryName and UserName, so that
multiple users can use the table without stepping on each other's toes. This
is quick and dirty sample code, it could probably be improved by using
sprocs rather than on-the-fly SQL statements like this, but hopefully it
should serve to demonstrate the general idea ...

Public Sub QueryNames()

Dim aob As AccessObject
Dim rst As ADODB.Recordset

CurrentProject.Connection.Execute "DELETE FROM dbo.QueryNames " & _
"WHERE UserName = SYSTEM_USER"
For Each aob In CurrentData.AllQueries
CurrentProject.Connection.Execute "INSERT INTO dbo.QueryNames
(QueryName, UserName) " & _
"VALUES ('" & aob.Name & "', SYSTEM_USER)"
Next aob

Set rst = New ADODB.Recordset
With rst
.Open "SELECT QueryName FROM dbo.QueryNames " & _
"WHERE UserName = SYSTEM_USER ORDER BY QueryName",
CurrentProject.Connection
Do Until .EOF
Debug.Print .Fields("QueryName")
.MoveNext
Loop
.Close
End With

End Sub

I've a sneaking suspicion that there might be a built in system view that
would return this information, though. Maybe someone with more SQL Server
experience might be able to comment on that?

--
Brendan Reynolds

Hi all,

I've managed to get my combo to return all my query names, but they're not
in alphabetical order. Does anyone know how I can sort it. my vba is:

Private Sub Form_Load()

'Populate Combo Box with all Query Names
Dim QueryAndTableList As String
QueryAndTableList = ""
'Store quotation mark as variable
Dim QM As String
QM = Chr(34)
'Loop through all Query Names
For Each AccessObject In CurrentData.AllQueries
QueryAndTableList = QueryAndTableList + QM + AccessObject.Name + ";"
Next

'Make ComboBox Row Source the Query List
Me!cboChooseQuery.RowSourceType = "Value List"
Me!cboChooseQuery.RowSource = QueryAndTableList

'Show first item as selected item in combo box
Me!cboChooseQuery.Value = Me!cboChooseQuery.ItemData(0)

End Sub

 

[Jay]

Great Idea Brendan...this suits my purpose even better as there are

1000 queries/views in the .adp, each with different 3 character

prefixes which indicate the content. So, if I write them to a table I
can then query the table and use the 3char prefix to populate a couple
of cascading combo boxes (one combo with >1000 rows isn't particularly
elegany As some of the tables are that large I'm just knocking
together a quick form for query/view selection for export.

Thanks again for your time & help.....Regards, Jason

 

[Jay]

Hi Brendan,

I created the table QueryNames and tried running the code but I get the
error

"Run time error -2147217833 (80040e57) String or binary data would be
truncated"

And the debugger highlights the statement containing the INSERT INTO in
the For Each aob..Next loop?

Could you advise where I've gone wrong.

Many thanks.......Regards, Jason

 

[Douglas J. Steele]

How big did you make the fields QueryName and UserName?

 

[Brendan Reynolds]

Hi Brendan,

I created the table QueryNames and tried running the code but I get the
error

"Run time error -2147217833 (80040e57) String or binary data would be
truncated"

<snip>

As Doug suggests elsewhere in this thread, the first thing to check would be
the length of the fields. Make sure they are long enough to accept your
longest user name and query name.

 

[Jay]

That's fixed it....I doubled my field length and it works perfectly now.

thanks again.........Rgds, Jason

 

[dahir]

Hi,

I'm trying to code a combo box and want to define a String variable and
have a For..Each..Next loop that goes through all the Queries in my
database (actually an .adp Access project) and add each query name to the
string.

Then I can make my Combo's Row Source the string (after concatenating with
char(34) and ";"). This will enable the Combo to always show all the
queries in the database.

However, when I try and set my For Next statement there isn't a
'AllQueries' option for me to add to the end of the following statement:

For Each AccessObject In CurrentProject.

Can anyone help me. Is there an 'AllQueries' collection that I can use?
Any help greatly appreciated..

Many thanks.....Jason