Returning Data From ADO

[thefonz37]

Historically, I've built reports in Excel using Microsoft Query to grab data
off remote SQL servers and build various graphs and things. However, I was
getting a little frustrated with some of aspects of Microsoft Query, so I
started Googling and discovered that, with a little extra elbow grease, you
can pull data using something called ADO and not only can you make the SQL
cleaner, it apparently runs faster.

The problem I'm having is finding a decent tutorial to introduce this
concept. I keep finding blocks of VB code, but with no instruction about how
to fit this VB into spreadsheets. All I'm trying to do is mimic the
functionality of Microsoft Query really and I'm not having much luck.

Does anybody know any good resources that can explain this process a little
better?

 

[Sam Wilson]

Hi, I did lots of rooting around for this and eventually worked it out for
myself.

1. In your VB window go to tools/references and tick "Microsoft ActiveX Data
Objects 2.X Library"
2. The following code is then the basis for running a query:

Sub Demo()

Dim c As ADODB.Connection
Dim rs As ADODB.Recordset
Dim s As String

Set c = New ADODB.Connection
c.Open "Insert Connection String Here"

s = "SQL Select Statement of your choice"

Set rs = c.Execute(s, , 1)

do until rs.eof
'Do something with your results
rs.movenext
loop

rs.close

c.Close

End Sub

That should get you in the right direction - the online help for
adodb.recordsets has lots of help too.

Sam

 

[Joel]

I fnd the help in Access VBA to be pretty good. Do a search in Access VBA
help for ADO.


These are the extra things you need to do in excel to use the Access macro
code

1) Add a reference to the access library in Excel VBA from menu

tools - References - Microsoft Access Library 11.0 object Library.

You also may need
tools - References - Microsoft ActiveX Data object 2.8 library

2) Create and Access object in Excel VBA

set obj = CreateObject("Access.Application")

3) Use the object above in the code you find for Access VBA.