Cannot open SQL database programmatically?! 2

[artOf]

Hi all,

I am new to Access so please don't be mean. I am developing an application on Access 2007 which is connected to a SQL Server 2008 database. In one of my forms I have a TreeView control and I need to fill it with entries from the database, I read that this is possible only programmatically, so I started implementing it:

        Dim dbCurrent As DAO.Database
        Set dbCurrent = CurrentDb '<- this call seems not to work, I tested it by declaring a test string variable and giving it the value CurrentDb.Name and it was empty.

Also tried:

        Dim dbCurrent As DAO.Database
        Set dbCurrent = DBEngine(0)(0) '<- this is also empty, the call DBEngine(0)(0).Count gives me 0...

I feel lost, I know that I am making a simple mistake somewhere(reference or some other property not set) but I have no idea where. I should say that the data base connection works fine when I use the Access forms to access data from SQL tables(all tables are available).
Thank you in advance!

Best Regards
artOf

 

[PWise]

if this call dose not work you must be working with a .ADP
not a .MDB

you should use ado not dao

 

[JoeAtWork]

Use an ADO Connection object instead of DAO.

Public Sub TestConnection()
    Dim MyConn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set MyConn = CurrentProject.Connection
    Set rs = MyConn.Execute("SELECT * FROM tblStuff")
    
    Do While Not rs.EOF
        Debug.Print rs("Stuff")
        rs.MoveNext
    Loop
    
    rs.Close
    
End Sub

 

[artOf]

Thank you, both very much! It works pretty fine now, I guess I should read about ADO and DAO a little bit more I have like 10 books on Access 2007 so that should not be a problem. Thank you again and I will try not to post such trivial problems anymore.

Respect
artOf