query of MSysObjects in ADO Code 1

[gavinjb]

I have writteen a procedure to produce a list of Queries for a Combo Box, but whenever I run the code it returns no data, but if I run the same SQL in the Query Window it returns data fine, Can anyone see why the code below fails on me.

Dim strQueryList As String
Dim rstADO As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = 5 AND [Name] Like " & Chr$(34) & "qry*" & Chr$(34)
rstADO.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockReadOnly

Do While Not rstADO.EOF
If strQueryList = "" Then
strQueryList = rstADO!Name
Else
strQueryList = strQueryList & ";" & rstADO!Name
End If

rstADO.MoveNext
Loop

Debug.Print strQueryList
Debug.Print strSQL

Thanks,

Gavin,

 

[GHolden]

Hi,

Another way to do this is to use the OpenSchema in ADO

eg.

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset

cn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
           "Dbq=Z:\Sample Access DB\Test.mdb;" & _
           "Uid=admin;" & _
           "Pwd="

'Open the recordset with all Tables of type view (i.e. Queries)           
Set rst = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "VIEW"))
'Loop through all records (queries)
Do While Not rst.EOF
  'Show each query name in a MsgBox
  'Add your string code instead of MsgBox
  MsgBox rst!TABLE_NAME
  rst.MoveNext
Loop

Don't worry about the connection part, you can use the same as you use now. You can find out loads of info through the OpenSchema, eg. Table, column, key, index info.

You need to delve into the help for more assistance with all the options.

Hope this helps.



There are two ways to write error-free programs; only the third one works.

 

[PHV]

Have you tried this ?
strSQL = "SELECT [Name] FROM MSysObjects WHERE [Type] = 5 AND [Name] Like 'qry%'"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Softop]

Gavinjb, did you find a solution to this cos I'm having the same problem? Query is fine but returns EOF when in VBA.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim varRecords As Variant
Dim stSQL As String

    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    stSQL = "SELECT [Name] FROM MSysObjects " & _
            "WHERE [Type] = 1 AND [Name] Like 'tbl*';"
    rst.Open stSQL, cnn, adOpenDynamic, adLockReadOnly
    If rst.EOF Then
        MsgBox ("end of file")
        Exit Sub
    End If

Softop

 

[cmmrfrds]

Refer to the example of PHV. The % is the wildcard in ADO.

 

[gavinjb]

Softop,

The code I used was as follows, part of the code restricts the list to Queries begginign with qry only, you might want to remove this:

Public Function FillQueryList() As String
Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strQueryList As String

    On Error GoTo FillQueryList_Error

    cn.Open CurrentProject.Connection

    'Open the recordset with all Tables of type view (i.e. Queries)
    Set rst = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "VIEW"))
    
    'Loop through all records (queries)
    Do While Not rst.EOF

        If Left$(rst!TABLE_NAME, 3) = "qry" Then
            If strQueryList = "" Then
                strQueryList = rst!TABLE_NAME
            Else
                strQueryList = strQueryList & ";" & rst!TABLE_NAME
            End If
        End If
  
        rst.MoveNext
    Loop

    rst.Close
    cn.Close

    FillQueryList = strQueryList

FillQueryList_Exit:
   On Error GoTo 0
   Exit Function

FillQueryList_Error:
    Select Case Err.Number

        Case Else
            HandleError Err, "basSMReports.FillQueryList"
            Resume FillQueryList_Exit

    End Select
End Function