Results 1 to 9 of 9

Thread: DIm

  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DIm

    On Access 97 I used Dim db As Database
    Now on Access 2000 the Database is not define on DIm
    How can I Open a recordset without define the database?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: DIm

    Hi Horacio,
    You need to manually set a reference to the Microsoft DAO object library (Tools-References) - Database will then appear as an option once you've typed the 'as'.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIm

    And lose the reference to the ADO library else you'll probably get all sorts of error messages if both ADO and DAO libraries are referenced.

  4. #4
    Scott A
    Guest

    Re: DIm

    There's no real problem with having both DAO and ADO referenced. All you need to do is explicitly state which library you want to use.
    Example:
    Dim rstDAO As DAO.Recordset
    Dim rstADO As ADODB.Recordset

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DIm

    In fact, I always set both references because each has some handy features the other lacks. However, don't try to mix them in the same routine. For some reason, even when they are specifically referenced, Access does NOT like mixing the two object models in the same piece of code.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DIm

    Thanks for the input.
    The first answer/suggestion just said to add a reference to DAO, which would result in lots of error messages (I suspect the next line required would have been Dim RS as Recordset). If you want both DAO and ADO then you do have to explicitly reference as your example. If you just require DAO then why have ADO referenced? and why go to the hassle of defining variables as your example?
    I tend to avoid ADO completely (I found the early versions had problems especially if one works in C++ whereas DAO was stable, maybe the later releases offer more but I have so much code/experience with DAO outside of Access I am loath to change).
    Do you have any good examples of why ADO is superior to DAO?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DIm

    The best reason for specifying the object model in your dim statements is for readability and maintenance. Open a new database in 2000 and import everything from an existing app that uses DAO. Now compile it. If you have specified DAO.Recordset, or whatever, you'd at least know what the problem was. If not, the recordset object declaration will compile because ADO also has a recordset object, but the code won't run.

    Reasons to use ADO? Well, for one thing, ADO has a simpler object model than DAO (well, once you get used to it) and is less linear than DAO. With DAO, you have to actually instantiate an object in order to work with its properties--that is, you have to set a recordset = dbs.OpenRecordset before you can do anything else with it. In ADO, you don't have to create a higher order object and then open a recordset as a member of that object's collection. You can set the recordset to a new ADODB.Recordset and then manipulate its properties, including its connection property and the SQL or queryname that will provide its records, before you ever open it. Then you start working with it.

    Don't misunderstand. For some purposes, DAO is actually faster than ADO, like when you just want to return a single value from a table. And Access forms bound to ADO recordsets are not updateable, period, end of discussion. If you want an editable recordset bound to a form, you're looking at DAO. On the other hand, ADO recordsets can be faster to manipulate in code. And ADO is not as closely bound to the Jet engine as DAO.

    They each have their uses, and I use both.
    Charlotte

  8. #8
    Scott A
    Guest

    Re: DIm

    Additionally, I believe that Microsoft is not adding anything new to DAO. Pretty much all their data access stuff is being pushed to ADO. Not sure how much longer DAO will be around or supported. Access 2000 was the first "phase" of migrating from DAO to ADO. I'm sure that with Access 10 and Access.NET that Microsoft will push even harder towards ADO in Access.

    If you do the work now, learn ADO, and start utilizing it in Access, migration to future Access versions will be easier (if there is such a thing with MS products).

    ADO also provides a lot more functionality. DAO is pretty much tied to JET. Sure you can use ODBCDirect in DAO, but for the most part it was only intended for JET. ADO, on the other hand, can handle anything that has an OLEDB provider. This includes all major DB Engines (Oracle, SQL Server, DB2, just to name a few) as well as text, Active Directory, XML, the file system. And the list goes on.

    Microsoft is also moving from ODBC to OLEDB. DAO does not support OLEDB Providers, yet ADO can support ODBC and OLEDB. (Well, this is kind of a lie. OLEDB actually provides the support for ODBC by providing an OLEDB "wrapper" Provider for ODBC.)

    I'm not saying that ADO is better than DAO. A lot of it depends on what you want and need to do. But, in general, ADO is more robust, flexible and, in Microsoft's eye, is the future.

    HTH

    (steps down from soap box)

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: DIm

    That's what I love about this Lounge - I post what I think is a simple answer to a simple question and end up probably learning more than the original poster...
    Sorry for the slightly misguided steer horacio!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •