Wild Card in ADO

[MFS 43]

Have been trying to convert recordsets from DAO to ADO.

Program worked fine in DAO but am having trouble using wildcard in SQL string
for ADO recordset.

The problem is in the "Where" clause. Here are the two versions:
XXXXXXXXXXXXXXXXXXXXXXXXX

DAO version:

Dim Db as Database
Dim LN As String
Dim strSQL As String
Dim rst As Recordset

Set db = CurrentDb

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "*"" )); "

Set rst = db.OpenRecordset(strSQL)

This works great in DAO.

ADO version:

Dim LN As String
Dim strSQL As String
Dim rst As New ADODB.Recordset

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME] " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "%"" )); "

rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

This does not work in ADO

XXXXXXXXXXXXXXXXXXXXXXXXXXX

I have tried many versions of the syntax in the "Where" clause but can't seem
to get the wildcard to work properly in the ADO version. Note the change fro
"*" to
%" for the wildcard but that does not work either.

Any suggestions?

Thank you for the help.

[Lyle Fairfield]

mf***@aol.com (MFS 43) wrote in
news:20***************************@mb-m28.aol.com:

Have been trying to convert recordsets from DAO to ADO.

Program worked fine in DAO but am having trouble using wildcard in SQL
string for ADO recordset.

The problem is in the "Where" clause. Here are the two versions:
XXXXXXXXXXXXXXXXXXXXXXXXX

DAO version:

Dim Db as Database
Dim LN As String
Dim strSQL As String
Dim rst As Recordset

Set db = CurrentDb

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "*"" )); "

Set rst = db.OpenRecordset(strSQL)

This works great in DAO.

ADO version:

Dim LN As String
Dim strSQL As String
Dim rst As New ADODB.Recordset

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME] " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "%"" )); "

rst.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

This does not work in ADO

XXXXXXXXXXXXXXXXXXXXXXXXXXX

I have tried many versions of the syntax in the "Where" clause but can't
seem to get the wildcard to work properly in the ADO version. Note the
change fro "*" to
%" for the wildcard but that does not work either.

Any suggestions?

Thank you for the help.

Are you connecting to SQL Server? If so try using single quotes for your
string delimiters.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Terry Kreft]

Try

....
& "WHERE ((([PatientDemographics].[LASTNAME]) Like '" & LN & "%"' )); "

i.e. single quotes instead of the embedded double quotes.
--
Terry Kreft
MVP Microsoft Access
"MFS 43" <mf***@aol.com> wrote in message
news:20***************************@mb-m28.aol.com...

Have been trying to convert recordsets from DAO to ADO.

Program worked fine in DAO but am having trouble using wildcard in SQL string for ADO recordset.

The problem is in the "Where" clause. Here are the two versions:
XXXXXXXXXXXXXXXXXXXXXXXXX

DAO version:

Dim Db as Database
Dim LN As String
Dim strSQL As String
Dim rst As Recordset

Set db = CurrentDb

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "*"" )); "

Set rst = db.OpenRecordset(strSQL)

This works great in DAO.

ADO version:

Dim LN As String
Dim strSQL As String
Dim rst As New ADODB.Recordset

strSQL = "SELECT [PatientDemographics].[LASTNAME],
[PatientDemographics].[FIRSTNAME] " _
& "FROM [PatientDemographics] " _
& "WHERE ((([PatientDemographics].[LASTNAME]) Like """ & LN & "%"" )); "

rst.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
This does not work in ADO

XXXXXXXXXXXXXXXXXXXXXXXXXXX

I have tried many versions of the syntax in the "Where" clause but can't seem to get the wildcard to work properly in the ADO version. Note the change fro "*" to
%" for the wildcard but that does not work either.

Any suggestions?

Thank you for the help.