how to call function query in access using command button

[Mark Gambo]

What was I thinking, instead of running as Access Query by using Access why not execute the query via ADO from VB6:

Option Explicit
Private strDataBaseName As String
Private strDBCursorType As String
Private strDBLockType As String
Private strDBOptions As String
Private rs As ADODB.Recordset
Private cn As ADODB.Connection
Private strSQL As String
 
Private Sub Command1_Click()
Dim b as Long
 
On Error GoTo Command1_Click_Error
 
Me.Command1.Enabled = False
strDBCursorType = adOpenDynamic  'CursorType
strDBLockType = adLockOptimistic   'LockType
strDBOptions = adCmdText         'Options
 
Set cn = New ADODB.Connection
Me.MousePointer = 11
 
cn.Open ConnectString()
    
    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
 
    
    Set rs = New ADODB.Recordset       'Creates record set
    
    strSQL = "SELECT * "
    strSQL = strSQL & "FROM TABLE1 "
    strSQL = strSQL & "WHERE FIELDNAME1 ='" & Me.Text1.Text & "' "
    strSQL = strSQL & "ORDER BY FIELDNAME1;"
    
    rs.Open strSQL, cn, strDBCursorType, strDBLockType, strDBOptions
 
    If Not rs.EOF then   
        For b = 1 To rs.RecordCount
               '<=== Do whatever you need to do with the recordset here
               rs.MoveNext
        Next b
    Else
        MsgBox "The recordset contained no Records"
    End If
 
 Beep
 
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
 
On Error GoTo 0
Exit Sub
 
Command1_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command1_Click of Form " & Me.Name
End Sub
 
Private Function ConnectString()
              
        ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & <Type the Path and Name of DB Here> & _
                        ";Jet OLEDB:Engine Type=5;"
 
End Function