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

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

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

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

"Ken" wrote in message
...
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

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

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" wrote:

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

"Ken" wrote in message
...
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

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

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

"Ken" wrote in message
...
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" wrote:

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

"Ken" wrote in message
...
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