SQL statement not returning any records

O

OCoder

Joined
Dec 14, 2011
Messages
4
Reaction score
2
Cross posted at:
http://www.ozgrid.com/forum/showthread.php?t=160757
http://www.mrexcel.com/forum/showthread.php?p=2965296#post2965296

Hi, I have a somewhat simple function put together that has one purpose. It takes the arguments as provided inserts them as strings into an SQL statement, then executes that SQL on a recordset connection to an Access DB. I am using office2007 on windows XP.

The problem is the SQL statement never returns any records even though when I copy the SQL from the immediate window and paste it into Access directly and run the query it returns exactly 1 record as expected (After changing the % wildcard to * for Access). I have tried * and % as the wildcard in the Excel SQL code and neither one works. I have also tried replacing the like statement with = and it still returns nothing. Specifically, I am testing each change by checking the Rs.Recordcount value which never changes from -1.

Code: 
Function ConnectAndFillPN(ResultCell As Range, Cust As String, Ship As String, CPN As String, PNReturnType As String) As Integer
     'PNReturnType can be Int_PN or Cust_PN as string
     'Requires reference to Microsoft ActiveX Data Objects xx Library
     ConnectAndFillPN = 0
    Dim Cn As ADODB.Connection, Rs As New ADODB.Recordset
    Dim MyConn, sSQL As String
     
    Dim Rw As Long, Col As Long, C As Long
     
    MyConn = "C:\NewVar Processor.accdb"
     'Create query
    sSQL = "SELECT tbl_ship_part." & PNReturnType & " FROM tbl_ship_part WHERE (tbl_ship_part.Cust_Name = """ & Cust & """ AND tbl_ship_part.ShipTo = """ & Ship & """ AND tbl_ship_part.Cust_PN Like """ & CPN & "%"");"
     'Create RecordSet
    Set Cn = New ADODB.Connection
    With Cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open MyConn
        Set Rs = .Execute(sSQL)
    End With
        Select Case Rs.RecordCount
        Case Is > 1
        MsgBox "Caution!  There are multiple " & PNReturnType & " Part Numbers detected for:" & vbCr _
        & Cust & vbCr _
        & Ship & vbCr _
        & CPN & vbCr _
        & "Using first found " & PNReturnType & " part number.", vbOKOnly
        ConnectAndFillPN = 1
        Case Is < 1
        MsgBox "Result " & PNReturnType & " not found for:" & vbCr _
        & Cust & vbCr _
        & Ship & vbCr _
        & CPN
        ConnectAndFillPN = 1
        Exit Function
        Case Else
        End Select
    'The following statement may not be correct, but i can't get the recordset to populate yet.
    ResultCell.Value2 = Rs!Int_PN
    Set Location = Nothing
    Set Cn = Nothing
End Function
 
Back
Top