Pulling data from SQL into a VBA 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

Development Tools

Other Development Tools

Pulling data from SQL into a VBA variable

Author

Topic

MCCCLXXXV
Starting Member

10 Posts

Posted - 2009-05-13 : 16:20:17

I'm new to using SQL in VBA. I found and modified the following code to connect to my SQL database and pull in data. It works just as it should, but I'm wondering how I can make it pull the data into a VBA varialbe instead of pulling it into the Excel sheet.



Sub GetData()

' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=192.168.3.5\SQLEXPRESS;INITIAL CATALOG=Models;"

'Use an integrated login.
strConn = strConn & " UID=UNAME; PWD=PASS"

'Now open the connection.
cnPubs.Open strConn


' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
    .Open "SELECT Date FROM DateTBL WHERE DateID=27"
    ' Copy the records into cell A1 on Sheet1.
    ThisWorkbook.Sheets("CHAT").Range("A1").CopyFromRecordset rsPubs
    
    ' Tidy up
    .Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

End Sub

The code above just pulls in 1 "cells-worth" of data - just 1 date - and puts it in cell A1. I'm wondering how I could modify the above code so that it stores the data into a VBA variable ('dSQLdate' for example) instead of writing the data to the worksheet. It's probably very simple, but I'm not exactly sure how to use these advanced VBA/SQL things. Thanks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-05-14 : 08:55:13

myvar = rspubs.xxx

When you do this the xxx bit in VBA-intellisense will show you the exposed properties of the rspubs object. I think you need .fields(n)....where n is a number (starting at 0) or a name.

lack
Starting Member

5 Posts

Posted - 2009-09-28 : 04:18:34

try this:

dim dSQLdate as date
if not rsPubs.eof then dSQLdate = rsPubs!Date

above lines should save the corresponding date from the select statement to dSQLdate variable. if the select returns nothing u'll probably get 0:00:00 on dSQLdate.

oh, and if u want to manipulate the date value, adding and subtracting with numbers simply adds and substracts days from it.
if u want more search for dateadd function.

i program in access vba though, so i don't know if it applies to excel vba as well. http://msdn.microsoft.com is ur friend ;)

------------- Vi Veri Universum Vivus Vici -------------
* I, while living, have conquered the universe by truth *

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources