SQL statement not returning any records

O

OCoder

New Member
Joined
Dec 14, 2011
Messages
11
Cross posted at:
http://www.ozgrid.com/forum/showthread.php?t=160757

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 = <exact value=""> and it still returns nothing. Specifically, I am testing each change by checking the Rs.Recordcount value which never changes from -1.

The code is below. Thanks for any help!

</exact>
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Try using single quotes instead of double quotes.
Code: 
    sSQL = "SELECT tbl_ship_part." & "PNReturnType"
    sSQL = sSQL & " FROM tbl_ship_part "
    sSQL = sSQL & " WHERE (tbl_ship_part.Cust_Name = '" & "Cust" & "'" & _
                        " AND tbl_ship_part.ShipTo = '" & "Ship" & "'" & _
                        " AND tbl_ship_part.Cust_PN Like '" & "CPN" & "*');"
Also, check the SQL from the code works in Access.
 
Upvote 0
Thanks Norie, but that code resulted in the variable names being appended to the string instead of the variable values. I modified it to the following:

Code: 
    sSQL = "SELECT tbl_ship_part." & PNReturnType
    sSQL = sSQL & " FROM tbl_ship_part "
    sSQL = sSQL & " WHERE (tbl_ship_part.Cust_Name = '" & Cust & "'" & _
                        " AND tbl_ship_part.ShipTo = '" & Ship & "'" & _
                        " AND tbl_ship_part.Cust_PN Like '" & CPN & "*');"

This brought back the variable values, but the same result. rs.recordcount = -1

I did check my original SQL statement using ?sSQL in the immediate and copy it to Access. It works fine in Access giving the 1 record result I am looking for.
 
Upvote 0
My bad - I added the "" around the variables to test.

If RecordCount = -1 it doesn't necessarily mean no records were returned.

Try opening instead of executing - you only really execute action queries like INSERT, UPDATE etc
Code: 
Set Rs = New ADODB.Recordset
 
Rs.Open strSQL, Cn, adOpenStatic
 
Upvote 0
Norie,

Thank you so much for the help! That was what i needed. Opening the SQL query gave me the proper result.
 
Upvote 0
I am having a similar problem..

Code: 
sSQL = "SELECT XB.FirstName, XB.FamilyName, XB.Type " & _
            " FROM XB WHERE (((XB.FirstName) Like '*" & XB.Value & "*')) " & _
            " OR (((XB.FamilyName) Like '*" & XB.Value & "*'));"

the resulting string is

Code: 
SELECT XB.FirstName, XB.FamilyName, XB.Type  FROM XB WHERE (((XB.FirstName) Like '*denis*'))  OR (((XB.FamilyName) Like '*denis*'));

The SQL provides a working result in Access, but not when I execute it via VBA in excel, here is the code in excel
Code: 
Private Sub FindButton_Click()
' Setup variable and define connection
Dim cnAccess As ADODB.Connection
Dim sConnect As String
Dim LocationIndex As String
Dim rsData As ADODB.Recordset
Dim sSQL As String
Dim cccount As Integer
 
On Error GoTo CheckError
' Database type and location
    sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=\\XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
 
' SQL Method (currently not used for storing)
    sSQL = "SELECT XB.FirstName, XB.FamilyName, XB.Type " & _
            " FROM XB WHERE (((XB.FirstName) Like '*" & XB.Value & "*')) " & _
            " OR (((XB.FamilyName) Like '*" & XB.Value & "*'));"
 
    Set rsData = New ADODB.Recordset
    Set cnAccess = New ADODB.Connection
 
    cnAccess.ConnectionString = sConnect
    cnAccess.Open                                           ' Opens Connection to database
    rsData.Open sSQL, sConnect, adOpenStatic, adLockReadOnly ' SQL running engine (currenly not used for storing)
 
    rsData.MoveFirst
    With AwaitingDocs
    Do Until rsData.EOF
    Set lstItem = ListViewInbox.ListItems.Add()
        lstItem.Text = rsData![FirstName]
        lstItem.SubItems(1) = rsData![FamilyName]
        lstItem.SubItems(2) = rsData![Type]
        rsData.MoveNext
    Loop
 
    cnAccess.Close
    Set cnAccess = Nothing
    MsgBox (sSQL)
' Closes DB connection
Exit Sub
CheckError:
     MsgBox ("No Records Found")
XB.Value = sSQL
End Sub

Please help!

Kind Regards,

DrD
 
Upvote 0
How does it not work?

Does it error, perhaps here?
Code: 
    Set lstItem = ListViewInbox.ListItems.Add()
        lstItem.Text = rsData![FirstName]
        lstItem.SubItems(1) = rsData![FamilyName]
        lstItem.SubItems(2) = rsData![Type]
        rsData.MoveNext
    Loop

Or is the ListView not populated correctly?
 
Upvote 0
The error I was thinking of was the use of !.

I'm pretty sure that's not valid syntax in Excel, even if it is you could just use this.
Code: 
    Set lstItem = ListViewInbox.ListItems.Add()
        lstItem.Text = rsData.Fields("FirstName")
        lstItem.SubItems(1) = rsData.Fields("FamilyName")
        lstItem.SubItems(2) = rsData.Fields("Type")
        rsData.MoveNext
    Loop
By the way 'Type' is a reserved word in Access and probably isn't a good idea to use for a field name.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Excel-SQL -> Converting recordset to a variant array. GetRows() very slow.
Replies
6
Views
4K
jacksonmoore457
J
M
  • Question
Calling stored procedure with variables in SQL SERVER from Excel
Replies
0
Views
1K
mrfitness79
M
M
  • Question
vba help - update value in listbox1 from recordset
Replies
3
Views
1K
Fluff
Fluff
S
  • Question
ACCESS, VBA: Fetch data from sql server and paste it into local table.
Replies
3
Views
818
Micron
M
B
  • Question
SQL Insert Statement > Start Insert Records from Row(3) and onwards
Replies
1
Views
1K
John_w
J

Forum statistics

Threads
1,215,084
Messages
6,123,029
Members
449,092
Latest member
ikke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top