VBA to SQL SELECT and save to variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

VBA to SQL SELECT and save to variable

Author

Topic

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2014-02-06 : 12:28:12

I'm trying to use a VBA application to SELECT data from SQL SERVER2012 express, and save the result in a variable. I'm having trouble understanding the syntax for assigning the result to the variable called ProductName. I pasted my code below (see the long string of question marks for what I think is the problem area). Help would be greatly appreciated. thanks, sfjtraps

'Connect to the database
'Select batch entered data and display it
'**************************************************************************************
Private Sub SelectBatchData()

Dim ConnectionString As String
Dim cnt As ADODB.Connection
Dim tblName As String
On Error GoTo Handler
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim ProductName As Variant

'Set the batch data table name
tblName = "Batch_Data" 'Table Name

'Build connection string
ConnectionString = "Provider=SQLOLEDB.1;User ID=sa;Password=barney;Initial Catalog=Batch;Data Source=D142JEFFERSS\SQLEXPRESS"

'Connect to database and select data
Set cnt = New ADODB.Connection
cnt.Open (ConnectionString)

strSQL = "SELECT Product_Name FROM " & tblName

Set rs = New ADODB.Recordset

rs.Open strSQL, cnt

ProductName = ??????????????????????????????????????

'Close recordset and connection
rs.Close
cnt.Close

ConnectionString = "Nothing"

Exit Sub 'Exit sub here or handler will display an unnecessary msgbox

'Error handler for bad data entered on the batch start screen
'Message screen is displayed with the bad data entry description
Handler:
If Err.Description = "" Then
MsgBox strerrormsg
Else
MsgBox Err.Description
End If
End Sub

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-02-06 : 18:55:32

It has been a while since I used ADO Recordsets but...
ProductName = rs.Fields("Product_Name").Value

???

===============================================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber (1894-1961)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources