VBA Excel 2007 SQL query


S

Striker

I'm trying to query a SQL database and bring the results to a spreadsheet.
Be gentle on my, I have not coded in VBA for many years.

I'm thinking I can open a connection lke this
Set MyConnection = new ADODB.connectio0n
MyConnection.Open "Driver=SQL
Server;Server=MyServerName;Database=MyDatabase; (not sure I need a
password)

So question is how do I run a SQL query? I have only used this to add stuff
to a database before?
 
Ad

Advertisements

N

Nigel

Turn on the macro recorder, then tab to DATA and set up your connection
'From other sources', and query options. Turn off recorder and you will have
a skeleton code that you can adapt for general use.
 
D

Dick Kusleika

I'm trying to query a SQL database and bring the results to a spreadsheet.
Be gentle on my, I have not coded in VBA for many years.

I'm thinking I can open a connection lke this
Set MyConnection = new ADODB.connectio0n
MyConnection.Open "Driver=SQL
Server;Server=MyServerName;Database=MyDatabase; (not sure I need a
password)

So question is how do I run a SQL query? I have only used this to add stuff
to a database before?

Dim rs As ADODB.Recordset
Dim sSQL As String

sSQL = "SELECT * FROM MyTable WHERE ID=1"

Set rs = MyConnection.Execute(sSQL)

More info here
http://www.dailydoseofexcel.com/archives/2004/12/16/ado-recordset-basics/\
 
Ad

Advertisements

S

Striker

VBA does not seem to like the line continuation _. If I put my query on one
line 600 spaces long it works, but the query is hard to read that way.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top