Missing Queries in VBA command result

[Winfried]

Hi,

I use Windows XP SP2 with Office 2003 SP2. I just wanted to get a list
of all queries I use in my MDB-file in order to use it in an Excel file
for documenation purposes.

The command I used was the following

Sub SHOW_QUERIES()

Dim catalog As New ADOX.catalog
Dim tb As ADOX.Table

catalog.ActiveConnection = Application.CurrentProject.Connection
For Each tb In catalog.Tables
If tb.Type = "VIEW" Then
Debug.Print tb.Name
End If
Next tb

End Sub

I was very astonished that I got not all queries. I found out that I
got only those queries which name has a lenght of 50 or less
characters.

Has anyone of you an idea why this happen or another idea how I get a
complete list?

Regards,
Winfried

 

[Douglas J. Steele]

Are they Action queries (INSERT INTO, DELETE, UPDATE)? I don't believe ADOX
shows them in that case, since they aren't views.

Easiest is simply to use DAO.

Sub SHOW_QUERIES()

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

dbCurr = CurrentDb()
For Each qdfCurr In dbCurr.QueryDefs
Debug.Print qdfCurr.Name
Next qdfCurr

End Sub

DAO is better for working with Jet databases (i.e. MDB or MDE files) than
ADO and ADOX anyhow.

 

[Winfried]

Hi Doug,

Thank you for your quick answer.

It's mixed. I have all kind of queries. I'll check your proposal.

But you as MVP should also like ADO repectively ADOX

Regards
Winfried

 

[Guest]

Hi Winfried,

Try downloading and installing Access MVP Jeff Conrad's "CSD Documentor"
tool. This free add-in will document all of your queries. It includes the
capability to export the results to Excel.

http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Winfried]

Hi Tom,

Thank you very much for yor tip!! I just downloaded the tool and I will
check it whether it fit my purposes.

Best regards
Winfried

 

[Douglas J. Steele]

But you as MVP should also like ADO repectively ADOX

Why do you think this? Microsoft no longer works on ADO: it's been replaced
by ADO.Net, which, despite the similar name is nothing like ADO. DAO, on the
other hand, is the recommended approach for dealing with Jet databases.

I use ADO when I'm connecting to other DBMS (especially SQL Server), but DAO
for Jet. There's no problem using both in the same application.

 

[Guest]

You're welcome. I think you'll like this tool. I was one of the beta testers
for version 2 of CSD Tools. This tool is so handy that I'm trying to get it
approved for use at my place of work, which is The Boeing Company.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________