Using VBA to pull data from SQL server based on cell contents

[Guest]

Hello.
I'm trying to figure out exactly how to use VBA to look at the contents of a
cell, then query a SQL server using that data and return results to several
other cells.

My server name is Goober and the table I want to pull the data from is Jeff.
In ODBC, I have the Server connection called Testing

basically I need to look at the contents of cell A1, and then run a
statement that does the following:
Output to cell A2 the results of the SQL query
select CreatedDate from Goober..Jeff where InvoiceNumber = (Cell A1)
Output to cell A3 the results of the SQL query
select CreatedByUser from Goober..Jeff where InvoiceNumber = (Cell A1)

I'm well versed in SQL, but I am a VBA novice, so I don't know how to set up
my connections (with VBA code) or what the difference is between ADO and DAO,
so could you please include that in your answer? Also, I'd need to know if I
need to set any References.

Thanks!
Ken

 

[Jean-Yves]

Hi Ken
Have a try with this, using ADO :
Sub TEST()
'this requires to make a ref via tool
'to Microsoft ActiveX Data Object 2.x

Dim cn As ADODB.Connection
Dim rec As ADODB.Recordset

Set cn = New Connection
Set rec = New Recordset
'open the connection
cn.Open "Provider=SQLOLEDB.1;Data Source=GOOBER", "userID", "password"
'open the recordset
rec.Open "select CreatedDate from Jeff where InvoiNumber= '" & Range("A1") &
"';", cn, adOpenForwardOnly, adLockOptimistic

Do While rec.EOF = False
Range("a2") = rec.Fields("CreatedDate").Value
'or to copy an entire recorset
'Range("A1").CopyFromRecordset rec
'optional for multiple records
'rec.MoveNext
Loop

rec.Close
cn.Close
Set rec = Nothing
Set cn = Nothing
End Sub
Regards
JY

 

[Guest]

Thank you so much!

I had to massage the code a little bit because the SQL connect string wasn't
exactly right for our server, but other than that, it worked great!

 

[Jean-Yves]

Hi Ken,
Glad it worked.
You can also search on microsoft.support for different connection methods
depending on the database
such as oracle, sql, db, access or xl.
If you made a ref to ado, also some explanation to find in help.
Regards
JY