CODEWITHSUNDEEP
excelvbadaolate-binding
Absinthe
Absinthe

Reputation: 3391

Excel VBA late bind Microsoft DAO 3.6 object library

Can anybody post a snippet on how to create a reference to Microsoft DAO 3.6 object library from Excel 2007?

I gather I need to use the CreateObject method but I can't find anything online concerning the right parameters to reference DAO 3.6 specifically.

I need to late bind as I won't be able to manually set references on each PC using the file.

Many thanks

Upvotes: 1

Views: 4273

Answers (2)

Matus
Matus

Reputation: 181

For DAO late binding. Not fully tested compatibility if you use ADO in your code, but it works so far in my projects.

Dim db As Object  'late binding without reference, seems to work, but might cause trouble, not tested
Dim tbl As Object

Dim dbe As Object
Set dbe = CreateObject("DAO.DBEngine.120")  'depends on win version!


Set db = dbe.OpenDatabase(file)
Set tbl = db.TableDefs("CAPEX")

Upvotes: 1

David Rushton
David Rushton

Reputation: 5030

Thes best place to learn about DAO is Microsoft's Developer Network. There is a lot of documentation on the subject.

VBA supports three data access technologies. DAO, ADO and RDO. Of these Microsoft recommend you use ADO, the newer of the three.

I've been trying to write a late bound Excel DAO example, but I've run into a little trouble. I've only been able to get an early bound approach working.

Sub ExampleEarly()
    Dim DB As DAO.database
   Dim RS As DAO.Recordset


    ' Connect to Excel spreadsheet and read from tab called VA.
    Set DB = OpenDatabase("C:\Example\ExcelFile.xls", False, True, "Excel 8.0;HDR=Yes")
    Set RS = DB.OpenRecordset("SELECT * FROM [VA$]")

'Close the recordset
    RS.Close    
    DB.Close
    Set RS = Nothing    
    Set DB = Nothing
End Sub

ADO can be late bound like so:

Dim cn As Object
Dim rs AS Object

Set cn = CreateObject("ADODB.Connection")
Set rs= CreateObject("ADODB.Recordset")

Upvotes: 1

Related Questions