Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Currentdb vs dbEngine(0)(0) (XP/W2k)

    Hi,

    Just wondering if someone can point me in the right direction. I'm self taught in the development area and ran into the "can't open any more databases" problem on a db I am working on for a friend. After reviewing the posts (thanks all) I have made changes which have reduced this error occuring. However once in a while it still happens. I suspect that is the way I am referencing database's and recordset's now.

    I have being watching the open databases and notice that each time I use currentdb it creates a new db and wonder if that I set the dbEngine(0)(0) rather than set a currentdb whether this have any adverse effect? For example I have a function that adds a new city name on the not in list event to the lookup table.

    If I use current DB I get the following:
    Total Databases: 2
    DB(0) Recordsets: 0
    DB(1) Recordsets: 1

    If I use dbEngine I get the following:
    Total Databases: 1
    DB(0) Recordsets: 1

    If I can use dbEngine safely would this help me reduce the nbr of connections in regards to hitting the can't open any more db's error at all?

    Many Thanks
    Tony

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Currentdb vs dbEngine(0)(0) (XP/W2k)

    According to the online help, CurrentDb creates a new instance of the database object, while DBEngine(0)(0) refers to the already open instance. It is recommended to use CurrentDb, and in general, this shouldn't cause problems. As with all object variables, you must destroy it after use by setting it to Nothing:

    Sub DoSomething()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler

    ' Set object references
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblData", dbOpenDynaset)
    ' Code to process recordset goes here
    ...

    ExitHandler:
    ' Release object memory
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    ' Inform user
    MsgBox Err.Description, vbExclamation
    ' And clean up
    Resume ExitHandler
    End Sub

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Currentdb vs dbEngine(0)(0) (XP/W2k)

    Thanks Hans,

    I suspect I may have also just spotted where I may be running into trouble, in the ExitHandler I am not resuming next, hence this would mean that if rst had an errorr then the db may not close and leave an instance open.... The joys of learning....

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Currentdb vs dbEngine(0)(0) (XP/W2k)

    If interested in a few more details, see this somewhat archaic MSKB article:

    ACC2: Use CurrentDB() Rather Than DBEngine(0)(0)

    As seen, the CurrentDB function was introduced a while back, but the info in article is still applicable.

    HTH

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Currentdb vs dbEngine(0)(0) (XP/W2k)

    In regard to these lines of code you mentioned:
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblData", dbOpenDynaset)

    Why not just use:
    Set rst = currentdb.OpenRecordset("tblData", dbOpenDynaset)

    Any particular reason you chose the former?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Currentdb vs dbEngine(0)(0) (XP/W2k)

    I must admit that I haven't tested it in Access 2002, but if I opened a recordset in Access 97 using

    Set rst = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)

    the recordset object would have limited availability, after a few instructions it wouldn't be valid any more. I never had that problem if I assigned a database object first. So it has become an automatism to always create a database object first, then a recordset object.

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Currentdb vs dbEngine(0)(0) (XP/W2k)

    >>the recordset object would have limited availability, after a few instructions it wouldn't be valid any more. <<

    I've never encountered that problem.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Currentdb vs dbEngine(0)(0) (XP/W2k)

    As I wrote, I never bothered to check it again, so maybe it was just a glitch. If you don't have the problem, there is no need to change; for me, it's mostly a matter of "if it ain't broken, don't fix it". <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Currentdb vs dbEngine(0)(0) (XP/W2k)

    >>"if it ain't broken, don't fix it". <<

    Yeah, I know the feeling. Some things I do are so automatic now that it would require considerable effort on my part to change; so unless there is some significant benefit, why bother!<g>
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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