Query works, SQL in VBA does not

[dch]

I am running Access2002 under Windows XP Pro. All of my
VBA is written with ADO methods.

I am trying to open a recordset in VBA. I developed the
SQL statement by creating a query and then copying the
Access generated SQL text into my VBA code. I have done
this numerous times so am familiar with what needs edited
when transfering to VBA.

This query runs fine in the query builder, but gives an
error when the recordset is opened in VBA. The code is:

Public Sub ProblemSQL()
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String

Set cnn = CurrentProject.Connection

Let strSQL = "SELECT c.IOCardName, v.IOCardVersionID "
& _
"FROM (tblUsageLocation AS u INNER JOIN tblIOCard AS c
ON u.UsageLocationID = c.Usage) " & _
"INNER JOIN tblIOCardVer AS v ON c.IOCardID =
v.IOCardType " & _
"WHERE c.IOCardDescription Like '% 2 F%' AND
u.UsageLocationName='cable';"

rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic,
adCmdText

Debug.Print "IOCardName: " & rst!IOCardName
Debug.Print "IOCardVersionID: " & rst!
IOCardVersionID
End Sub

When the rst.open line executes, I receive an error as
follows:

Run-time error '-2147467259 (80004005)':

Method 'Open' of object '_Recordset" failed

I thought at first there where problems with my WHERE
syntax. I am never sure if I need to use the '*' or
the '%' as a wildcard in SQLs in VBA.

I was finally able to get the SQL to run by completely
removing the INNER JOIN structure between
tblUsageLocation and tblIOCard and then entering the
necessary info into the WHERE statement to make up for
the missing JOIN.

I realize you can't test this without knowledge of how
the tables are set up, but any ideas on why this INNER
JOIN would cause a problem in VBA but not in the query
builder?

Thanks in advance.

-dch

 

[Guest]

Update to problem:

Further experimentation suggests that the problem may be
with the AND portion of the WHERE statement. If I only
have one criteria in the WHERE, the SQL works, but when I
have two, the prolem occurs.

-dch

 

[John Vinson]

I am never sure if I need to use the '*' or
the '%' as a wildcard in SQLs in VBA.

If your database tables are in Access (using a .mdb file, and the JET
engine) use *. IF your tables are in SQL Server (including MSDE, for
instance in an Access .adp project) use %.

 

[John Vinson]

"FROM (tblUsageLocation AS u INNER JOIN tblIOCard AS c
ON u.UsageLocationID = c.Usage) " & _
"INNER JOIN tblIOCardVer AS v ON c.IOCardID =
v.IOCardType " & _
"WHERE c.IOCardDescription Like '% 2 F%' AND
u.UsageLocationName='cable';"

Is that a typo in the post, or in your VBA? You are defining
tblUsageLocation as u but joining to v.

 

[Gary Walter]

Hi dch,

It reminds me of "reserved word" problem
switching between designer and OLE DB
Provider.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q238243

where

***quote***
The Microsoft Jet database engine runs in several different modes depending on
whether it is called from Microsoft Access, Data Access Objects, the Microsoft OLE DB
Provider for Microsoft Jet, or the Microsoft Access ODBC driver. It can be run in
either ANSI mode or non-ANSI (traditional) mode.

Each of these modes results in a slightly different set of reserved words. Queries
written in one mode may use a reserved word as an identifier and fail in another mode
because of this.
***unquote***

I believe "Usage" is one.

Try putting it in brackets
(or changing field name).

Or possibly handling ADO connection errors
might give better clue?

Public Sub ProblemSQL()
On Error GoTo Error_ProblemSQL
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String

Set cnn = CurrentProject.Connection

strSQL = "SELECT c.IOCardName, v.IOCardVersionID " _
& "FROM (tblUsageLocation AS u " _
& "INNER JOIN tblIOCard AS c " _
& "ON u.UsageLocationID = c.[Usage]) " _
& "INNER JOIN tblIOCardVer AS v " _
& "ON c.IOCardID = v.IOCardType " _
& "WHERE (c.IOCardDescription Like '% 2 F%') " _
& "AND " _
& "(u.UsageLocationName = 'cable')"

Debug.Print strSQL
rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic, adCmdText

Debug.Print "IOCardName: " & rst!IOCardName
Debug.Print "IOCardVersionID: " & rst!IOCardVersionID

Exit_ProblemSQL:

Exit Sub

Error_ProblemSQL:

Dim errADO As ADODB.Error

For Each errADO In cnn.Errors
MsgBox "Error " & errADO.Number & " = " & errADO.Description
Next

Resume Exit_ProblemSQL

End Sub

Good luck,

Gary Walter