escheider
asked on
Filter Query using Access
Hello Experts:
Im trying to create a function that will perform a search on a specified table and column from an Access Database. Here is the function I have written:
Function FindString(TableName As String, ColumnName As String, Criteria As String) As Variant
Dim Rst As New ADODB.Recordset
Dim SetConn As ADODB.Connection
Set SetConn = New ADODB.Connection
With SetConn
.ConnectionString = "Provider=Microsoft.Jet.OL
.Open SetConnString
End With
With Rst
SqlString = "Select * From " + TableName + " Where " + ColumnName + " Like '" & Criteria & "'"
.Open SqlString, SetConn, adOpenStatic, adLockOptimistic, adCmdText
If Not .BOF Then
FindString = .GetRows
End If
End With
Rst.Close
SetConn.Close
Set Rst = Nothing
Set SetConn = Nothing
End Function
This function should produce a querystring something similiar to "Select * from TableName Where Column Like *Criteria*"
But, it doesn't seem to be working as it never returns the variant array.
Any help would be nice
VB6, Access 2000
Im trying to create a function that will perform a search on a specified table and column from an Access Database. Here is the function I have written:
Function FindString(TableName As String, ColumnName As String, Criteria As String) As Variant
Dim Rst As New ADODB.Recordset
Dim SetConn As ADODB.Connection
Set SetConn = New ADODB.Connection
With SetConn
.ConnectionString = "Provider=Microsoft.Jet.OL
.Open SetConnString
End With
With Rst
SqlString = "Select * From " + TableName + " Where " + ColumnName + " Like '" & Criteria & "'"
.Open SqlString, SetConn, adOpenStatic, adLockOptimistic, adCmdText
If Not .BOF Then
FindString = .GetRows
End If
End With
Rst.Close
SetConn.Close
Set Rst = Nothing
Set SetConn = Nothing
End Function
This function should produce a querystring something similiar to "Select * from TableName Where Column Like *Criteria*"
But, it doesn't seem to be working as it never returns the variant array.
Any help would be nice
VB6, Access 2000
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks ac, I figured it was something simple. Also, thanks for performance tips. It actually works...yipee
.Open SqlString, SetConn, adOpenStatic, adLockOptimistic, adCmdText
To:
.Open SqlString, SetConn, adOpenForwardOnly, adReadOnly, adCmdText