problems with slow dao.querydefs

[Kooshesh]

hello all,
I'm trying to populate a listbox with a list of queries in a database
located on a different drive. Here is the code.


'---------------------------------------------------------------------------------------
' Procedure : fillComboTables
' DateTime : 11/21/2005 13:44
' Author : Arian Kooshesh
' Purpose : This retrieves the list of tables the external db and
lists them in lstbox1
'---------------------------------------------------------------------------------------
'
Private Sub fillComboTables(Optional sType As String = "Table")
Dim db1 As DAO.Database
Dim qDefs As DAO.QueryDefs
Dim tDefs As DAO.tableDefs
On Error GoTo Err_fillComboTables
Call clearComboTables
Set db1 = DAO.OpenDatabase(lblLocation.Caption)
If sType = "Table" Then
Set tDefs = db1.tableDefs
For Each tDef In tDefs
If Mid(tDef.NAME, 1, 1) <> "~" Then lstBox1.AddItem
(tDef.NAME)
Next tDef
Else
Set qDefs = db1.QueryDefs
MsgBox qDefs.Count
For Each qDef In qDefs
If Mid(qDef.NAME, 1, 1) <> "~" Then lstBox1.AddItem
(qDef.NAME)
Next qDef

End If
Call db1.Close
GoTo setNothing

Exit Sub
Err_fillComboTables:
MsgBox "There was an error, Check the Debug Log"
Call addMessage("Error in fillComboTables in form selectPersonnel
err: " & Err.Number, Err.Description)
setNothing:
Set db1 = Nothing

End Sub


The problem is that there are over 1000 querydefs (I didn't make them,
I joined the job recently) and it takes over 20 minutes to add them. Is
there anyway to do this quicker?

 

[Kevin K. Sullivan]

You are currently using a Value List listbox and calling .AddItem once
per querydef in the external database's querydefs collection.

Instead, create a query named qryRemoteQueries on the external db's
MSysObjects table with this SQL (substituting the actual db path):

SELECT MSysObjects.[Name]
FROM MSysObjects IN 'X:\x\x.mdb'
WHERE [Type] = 5 AND Left([Name],1) <> "~"
ORDER BY [Name];

Change lstBox1's RowSourceType to "Table/Query".
Change lstBox1's RowSource to "qryRemoteQueries"
No more code is necessary to fill it.
This query should run much more quickly than the code.

It looks like you might want tabledefs as well. In that case, create
a query named qryLocalTables with this SQL:

SELECT MSysObjects.[Name]
FROM MSysObjects IN 'X:\x\x.mdb'
WHERE [Type] = 1 AND Left([Name],4) <> "MSys"
ORDER BY [Name];

HTH,

Kevin