Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    optimizing DAO db connection (Access 97/2k)

    I have an Access 97 database that I've converted to 2000. The decision, for now, is not to rewrite DAO statements using ADO. There are a lot of functions that open and close a connection to the database within themselves. Is there any way to have one global connection to the database, or just to use currentdb without creating a copy of it? It seems to me that the code will be much faster if I don't have to keep creating and closing a connection to the db.

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

    Re: optimizing DAO db connection (Access 97/2k)

    You might define a global variable of type DAO.Database in a standard module.
    Set this variable to CurrentDb in the OnLoad event of the startup form of your database, or in an AutoExec macro (you need to call a VBA function with the RunCode action for the latter). Now, you can refer to the variable wherever you need a reference to the database.
    BTW, the penalty of referring to the current database is not really serious, I believe. Opening and closing a connection to an external database is far worse, because the Jet engine then keeps on creating and deleting the .ldb file.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: optimizing DAO db connection (Access 97/2k)

    My problems is not so much where to define and set the global variable, it's where to close it! Also, when you say that the penalty of referring to the current db is not really serious, do you mean that I can use the currentDB object, or that constantly creating and closing connections to the current database should not take much of a toll on my system?

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

    Re: optimizing DAO db connection (Access 97/2k)

    (a) I often use a central form in my database; users can only close the database from there (apart from crashing of course...), and I set global object variables to Nothing in the OnClose event of the form.

    ([img]/forums/images/smilies/cool.gif[/img] Others will correct me if I'm wrong, but AFAIK, using code like

    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    ... 'code using dbs
    Set dbs = Nothing

    doesn't cause a heavy load on the system.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: optimizing DAO db connection (Access 97/2k)

    Thanks Hans, I appreciate the help. Unfortunately, option a) won't work for me b/c the powers that be don't want a central form for this db, so I don't have control over closing. I guess I'll have to look for other ways to make this code faster [img]/forums/images/smilies/smile.gif[/img]

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

    Re: optimizing DAO db connection (Access 97/2k)

    The powers that be don't have to know anything about the form. Just open it hidden when the database is opened. Hans isn't talking about a switchboard necessarily, just a form that will carry out cleanup duties when the database closes. Maintaining the global object variable *is* the best way to make the connections faster, and it reduces network load.
    Charlotte

Posting Permissions

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