Here is the code..
' Code Get the connection
Public Function GetDBConnection(FileName As String) As
ADODB.Connection
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
FileName
Set GetDBConnection = conn
End Function
' code to generate the record Set from query
Public Function GetRecordSetUsingQuery(Query As String) As
ADODB.recordSet
Dim conn As ADODB.Connection
Dim recordSet As ADODB.recordSet
Set conn = GetDBConnection("E:\temp\db1.mdb")
Set recordSet = New ADODB.recordSet
recordSet.Open Query, conn, adOpenDynamic, adLockOptimistic
Set GetRecordSetUsingQuery = recordSet
End Function
-- Code above is a part of common module that is used by all the
forms.
Following is the Part of Query builder.
Dim Criteria AS String
Criteria = "SELECT * FROM BookInfo WHERE "
If BookNameCheck.Value = True And IsNull(BookNameBox.Value) = False
Then
Criteria = " BookName Like '*" & BookNameBox.Value & "*'"
cnt = cnt + 1
End If
If AuthorNameCheck.Value = True And IsNull(AuthorNameBox.Value) =
False Then
If cnt > 0 Then
Criteria = Criteria & " AND "
End If
Criteria = Criteria & " AuthorName Like '*" & AuthorNameBox.Value
& "*'"
cnt = cnt + 1
If CategoryIDCheck.Value = True AND IsNull(CategoryBox.Value) = False
Then
If cnt > 0 Then
Criteria = Criteria & " AND "
End If
Criteria = Criteria & " CategoryID = " & CategoryBox.Value + 1
cnt = cnt + 1
End If
<snip for other other searchable parameters>
Dim rs As ADODB.recordSet
Set rs = Search.GetRecordSetUsingQuery(Criteria)
If rs.EOF = True Then
MsgBox "Search Did Not Retrieve Any Results", vbInformation, "Book
Search"
Else
DoCmd.OpenForm "SearchResults", , , , acFormReadOnly, acDialog,
Criteria
End If
Down here it always get empty recordset back for the queries that are
using *LIKE* but if I just include Criteria it will build following
query
SELECT * FROM BookInfo WHERE CategoryID = 1 and this works fine. I
dont understand why it doesnt work the same way when my query is using
LIKE.
Thanks
-HKM
"TC" <a@b.c.d> wrote in message news:<1068001298.293023@teuthos>...
Show us the code!
TC
"HKM" <hk****@yahoo.com> wrote in message
news:44**************************@posting.google.c om... Hello,
I have a query engine that builds the SQL Query to obtain the
recordSet. Following is an Exmaple Query that my QueryBuilder
outputted
SELECT * FROM BookInfo WHERE BookName LIKE '*cobol*'
When I use this query to build recordset I get empty recordSet back
when the BookInfo table has entries that qualify for this criteria. To
add to it, if I execute this query seperately under MS ACCESS Query
Menu, it works fine. It gives me a perfect output.
Does anyone know why this should happen ?
I'm using Microsoft.Jet.OLEDB.4.0 as my provider and access database
is locally stored.
Thanks in advance,
-HKM
