Excel VBA

  • Thread starter Thread starter Kathy
  • Start date Start date
K

Kathy

Does Excel VBA use ADO or DAO? If both, which is the default? If both, can you
specify which to use and where do you set it?

Kathy
 
The choice is yours. VBA allows you to use either.
ADO is the later version. DAO would be my recommendation
if you want to connect to an Access database, but I would
use ADO for that too, and I would use ADO to connect to
any other database. Its just a better tool.

In the Excel IDE, you need to set a refrence to the
control using Tools/References then select Microsoft
ActiveX Data Objects 2.7 Library.
Next, look in the object browser with the ADODB library
selected. You'll see what classes, and what
methods/properties are available.

Typically you'll need an ADODB connection to connect to a
database, and an ADODB recordset to populate from the
database. the link below is a simple example....

Sub LoadFromSQL()
Dim RST As ADODB.Recordset
Dim db As Connection
Dim SQL As String
Dim i As Long
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;driver={SQL
Server};server=" & MyServer & ";uid=;pwd=;database=" &
MyDatabase & ";"
Set RST = New Recordset
SQL = "SELECT DISTINCT [Instrument] FROM PL"
RST.Open SQL, db, adOpenStatic, adLockOptimistic
' prepare active sheet
Cells.ClearContents
With RST
For i = 0 To .Fields.Count - 1
Cells(1, i + 1).Value = .Fields(i).Name
Next
End With
Range("A2").CopyFromRecordset RST
RST.Close
db.Close
Set RST = Nothing
Set db = Nothing
End Sub

Patrick Molloy
Microsoft Excel MVP


http://www.xl-
expert.com/html_pages/dataConnectivity_SQL.html
 
Kathy,
VBA doesn't (and indeed can't) use either until you set a reference.

Tools > References then select either ADO or DAO (or both if you really need
to)

Once you've done that, Intellisense will help you.

NickHK

| Does Excel VBA use ADO or DAO? If both, which is the default? If both, can
you
| specify which to use and where do you set it?
|
| Kathy
|
|
 
Back
Top