query works in querybuilder, not in VB

[wylie72]

I'm trying to create a adodb recordset in an Access Module for updating
but when I try to open it it returns EOF. However, I can cut and past
the sql into QueryBuilder it returns the record. At a complete loss on
this one.

VB Code:
Dim prst As New ADODB.Recordset

prst.Open "Select Inventory.InvNo, Inventory.Receiptdate,
Inventory.ReceiptNo, Inventory.Item, Inventory.CurrentItemCost,
Inventory.Qty, Inventory.ShipDetailNos, Inventory.Location,
Inventory.SerialNumber from Inventory where ShipDetailNos Like '*:" &
Str(intNo) & ":*'", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

[Steve Jorgensen]

On 20 Oct 2005 07:25:10 -0700, wy*****@gmail.com wrote:

I'm trying to create a adodb recordset in an Access Module for updating
but when I try to open it it returns EOF. However, I can cut and past
the sql into QueryBuilder it returns the record. At a complete loss on
this one.

VB Code:
Dim prst As New ADODB.Recordset

prst.Open "Select Inventory.InvNo, Inventory.Receiptdate,
Inventory.ReceiptNo, Inventory.Item, Inventory.CurrentItemCost,
Inventory.Qty, Inventory.ShipDetailNos, Inventory.Location,
Inventory.SerialNumber from Inventory where ShipDetailNos Like '*:" &
Str(intNo) & ":*'", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

By default, JET uses different wildcard characters in ADO than in DAO. In
ADO, it uses the ANSI characters for wildcards. I believe it is "%" and "_"
instead of "*" and "?".

When I'm using the query builder for a query that will be executed using ADO,
I try to always use the "ALike" keyword instead of "Like". "ALike" means
ANSI-Like, and it always follows the ANSI rules. Using ALike, you only use
the ANSI wildcards regardless of settings or database library, so what you do
in the builder will still be right in the SQL srting passed to ADO.

[wylie72]

Just tried

prst.Open "Select [snip] from Inventory where ShipDetailNos ALike '%:"
& Str(intNo) & ":%'", CurrentProject.Connection

and

prst.Open "Select [snip] from Inventory where ShipDetailNos ALike '*:"
& Str(intNo) & ":*'", CurrentProject.Connection

and still no go.

[Steve Jorgensen]

On 20 Oct 2005 07:38:57 -0700, wy*****@gmail.com wrote:

Just tried

prst.Open "Select [snip] from Inventory where ShipDetailNos ALike '%:"
& Str(intNo) & ":%'", CurrentProject.Connection
With or without ALike, that should have worked in ADO. I can't say why it
wouldn't.

and

prst.Open "Select [snip] from Inventory where ShipDetailNos ALike '*:"
& Str(intNo) & ":*'", CurrentProject.Connection

and still no go.

The "*" wildcard will -never- work with ALike, so that's no surprise.

[wylie72]

bear in mind I'm doing this within an Access form, not in a Vb front
end. I use * within Access forms for wildcard, even with ADO, and it
works fine. i've been stumped on this one for going on 6 hours now.
Tried changing everything around (lock type, * vrs files, etc) and
still it's crapping out. Unfortunately it's part of a critical process
I have to have completed by Monday morning.

[Steve Jorgensen]

On 20 Oct 2005 07:55:13 -0700, wy*****@gmail.com wrote:

bear in mind I'm doing this within an Access form, not in a Vb front
end. I use * within Access forms for wildcard, even with ADO, and it
works fine. i've been stumped on this one for going on 6 hours now.
Unless you're using an ADP front-end to SQL Server, that's not a meaningful
thing to say. Access seems to sort of talk to JET through DAO, and sort of
around behind it, and does not use ADO. In any case, searching within a form
is using the Access user interface to cycle through the opern records, and
find the record - it's not using a Where clause in a database query.
Tried changing everything around (lock type, * vrs files, etc) and
still it's crapping out. Unfortunately it's part of a critical process
I have to have completed by Monday morning.

In what way is it crapping out?

[MacDermott]

You might try storing your SQL string to a separate variable first.
You can then evaluate that string, and cut and paste it into the Query
Builder.
You may find that what you have isn't exactly what you thought you had...

For example, Str() returns a string which starts with a space, which may not
be exactly what you want here. You could consider using CStr() if you don't
want that space...

<wy*****@gmail.com> wrote in message
news:11********************@g44g2000cwa.googlegrou ps.com...

I'm trying to create a adodb recordset in an Access Module for updating
but when I try to open it it returns EOF. However, I can cut and past
the sql into QueryBuilder it returns the record. At a complete loss on
this one.

VB Code:
Dim prst As New ADODB.Recordset

prst.Open "Select Inventory.InvNo, Inventory.Receiptdate,
Inventory.ReceiptNo, Inventory.Item, Inventory.CurrentItemCost,
Inventory.Qty, Inventory.ShipDetailNos, Inventory.Location,
Inventory.SerialNumber from Inventory where ShipDetailNos Like '*:" &
Str(intNo) & ":*'", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic