Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA
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 -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA
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 "Kathy" wrote in message nk.net... | 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 | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|