3 Attachment(s)
Error when using variable when connecting to SQL from Excel
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..........
Code:
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
Attachment 16268
