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