Error when using variable when connecting to SQL from Excel

[DaveGib]

Hi All,
I have a two part query an dwould appreciate any help with either or both parts.
1.) I am haveing a problem substituting a variable obtained in vba in a SQL string.
I have the code below and can get the results I want if i use a fixed value for the Store Number, but i would like the query to be flexible and get the required storenumber from the user, to get the relevent information for that store.
When trying to extract a recordset and i hard code the store number of 250 it works ok, however if i substitute the 250 with " & StorNum & " where StorNum is the variable obtained from a userform i get the error below.
Some of the store numbers have a suffix letter, but there isn't a problem when the Store is hard coded, - i get the correct result.
2.) As you will see in the commented section in the code below, i am trying to find a way that the Active X Data Objects library is automatically updated/invoked if not present, and would appreciate any comments on what i have done.

the image below seems rather small, and I am not sure if you can see it, but basically it says the conversion failed converting the varchar value 100D to data type int.

the 100D is a store number, but as mentioned i don't get the error when the search string has the store number hard coded.

any help/advise/suggestions would be greatly appreciated..........

Sub GetStockUsage()

        Application.ScreenUpdating = False
        
        
        
                'Referencing the ADO Library
                '-------------------------------------------------------------------------------
                'The ADO library must be referenced by your project.
                'To reference ADO from Microsoft Visual Basic
                '1.In Visual Basic, from the Project menu, select References....
                '2.Select Microsoft ActiveX Data Objects x.x Library from the list. Verify that at least the following libraries are also selected:
                    'Visual Basic for Applications
                    'Print Visual; Basic; runtime; objects And procedures
                    'Print Visual; Basic; objects And procedures
                    'Print OLE; Automation
                '3.Click OK.
        Dim StorNum As Variant
        StorNum = 250       '*** The Store Number is obtained from a Userform
        
    Dim StockIssuesConn As Object    ' causes VBA to automtically referance the correct Microsoft Active X Data Objects Library in Tools/ References
        Set StockIssuesConn = CreateObject("ADODB.connection")
'    Dim StockIssuesConn As ADODB.Connection    ' Connection String Variable
    Dim StockIssuesData As Object     ' Data Extracted Variable
        Set StockIssuesData = CreateObject("ADODB.Recordset")
'    Dim StockIssuesData As ADODB.Recordset     ' Data Extracted Variable
    Dim LedgerCodeField As ADODB.Field        ' Field Heading Variable
        '******* CONNECT TO THE SQL DATABASE
            Set StockIssuesConn = New ADODB.Connection
            
            StockIssuesConn.ConnectionString = "Provider=SQLOLEDB.1;Password='SQLPassword here';Persist Security Info=True;User ID=sa;Data Source='Server IP Here';Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID='PC Name Here';Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=BSBS_'DataBase Name Here'"
            StockIssuesConn.Open
    On Error GoTo CloseConnection

        '******** OPEN A NEW RECORDSET FOR DATA..................
          Set StockIssuesData = New ADODB.Recordset
        '******** GET ALL THE STOCK ISSUES
        'THIS WORKS:-
        With StockIssuesData
            .ActiveConnection = StockIssuesConn
            .Source = "SELECT Year, Period, BatchRef, TransRef, LedgerCode, Contract, Description, Stockno, Quantity, Unit, Rate, Debit, Credit, Store, SYSDATE FROM TRANSACTIONS WHERE  (Store = '250') AND (TransType = 'STB') AND (Allocation = 'Contracts')ORDER BY TransRef, Stockno"
            .LockType = adLockReadOnly
            .CursorType = adOpenForwardOnly
            .Open
        End With
'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'        THIS DOESN'T WORK!!! :-(
'        With StockIssuesData
'            .ActiveConnection = StockIssuesConn
'            .Source = "SELECT Year, Period, BatchRef, TransRef, LedgerCode, Contract, Description, Stockno, Quantity, Unit, Rate, Debit, Credit, Store, SYSDATE FROM TRANSACTIONS WHERE  (Store = " & StorNum & ") AND (TransType = 'STB') AND (Allocation = 'Contracts')ORDER BY TransRef, Stockno"
'            .LockType = adLockReadOnly
'            .CursorType = adOpenForwardOnly
'            .Open
'        End With
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    On Error GoTo CloseRecordset
        '****** CLEAR ANY PREVIOUS ENTRIES
        Worksheets("Data").Select
        Cells.Clear
        Range("A1").Select
        '******* GET COLUMN HEADINGS AND WRITE
        For Each LedgerCodeField In StockIssuesData.Fields
            ActiveCell.Value = LedgerCodeField.Name
            ActiveCell.Offset(0, 1).Select
        Next LedgerCodeField
        '******** WRITE ALL THE STOCK USAGE DATA
        Range("A2").CopyFromRecordset StockIssuesData
        On Error GoTo 0
        '***** CLOSE DATA RECORDSET
CloseRecordset:
        StockIssuesData.Close
        '***** CLOSE CONNECTION
CloseConnection:
        StockIssuesConn.Close
End Sub

ScreenHunter_383 May. 26 15.03.jpg

[Aflatoon]

You missed the single quotes when using the variable. You need:

.Source = "SELECT Year, Period, BatchRef, TransRef, LedgerCode, Contract, Description, Stockno, Quantity, Unit, Rate, Debit, Credit, Store, SYSDATE FROM TRANSACTIONS WHERE  (Store = '" & StorNum & "') AND (TransType = 'STB') AND (Allocation = 'Contracts') ORDER BY TransRef, Stockno"

[JKwan]

I think you are missing the single quote, give this a shot:

With StockIssuesData
 .ActiveConnection = StockIssuesConn
 .Source = "SELECT Year, Period, BatchRef, TransRef, LedgerCode, Contract, Description, Stockno, Quantity, Unit, Rate, Debit, Credit, Store, SYSDATE FROM TRANSACTIONS WHERE  (Store = '" & StorNum & "') AND (TransType = 'STB') AND (Allocation = 'Contracts')ORDER BY TransRef, Stockno"
 .LockType = adLockReadOnly
 .CursorType = adOpenForwardOnly
 .Open
End With

[DaveGib]

Aflatoon and JKwan - thanks soooo much to you both, - that did the trick!!!!... very gratefull