Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    I have the following Dlookup

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

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    Since you are looking up different fields in the same record, it's more efficient to open a recordset in DAO or ADO. For example:

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    Set cnn = CurrentProject.Connection
    rst.Open Source:="SELECT * FROM tblPersonal WHERE idsPersonalKey = " & Me.lngPersonalID, _
    ActiveConnection:=cnn

    strSSNoPersonal = Nz(rst!strSSNo, "")
    strFirstNamePersonal = Nz(rst!strFirstName, "")
    ...
    ...

    rst.Close
    Set rst = Nothing
    Set cnn = Nothing

    Don't expect blinding speed on Citrix, though.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    ADO took it down to 2

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

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    Hi John,

    Glad it works well. I don't think you can improve on this (of course, I don't know how you are going to use the variables).

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    You are going to let the "crap through a goose" comment go without comment? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> Seriously?

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

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    Apparently, our "bad words" filter isn't good enough...

  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: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    Why do you have "Set rst = ...." twice? You don't need the first one which opens the table as a recordset.

    The only other thing you can do is to open the recordset directly to the backend database. In that situation, I'd recommend having a global variable defined (something like dbBE) that points the BE directly, and leave it open.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    I thought the phrase was pretty funny myself.

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    Hi Mark

    Thanks for the heads up on redundant rst

    Do you have any sample code that demonstrates:

    <The only other thing you can do is to open the recordset directly to the backend database. In that situation, I'd recommend having a global variable defined (something like dbBE) that points the BE directly, and leave it open.>

    Thanks, John

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

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    You could use something like this:

    Public dbBE As DAO.Database

    Public Function GetdbBE() As DAO.Database
    If dbBE Is Nothing Then
    ' substitute path and filename of backend
    Set dbBE = OpenDatabase("...")
    End If
    Set GetdbBE = dbBE
    End Function

    By using the GetdbBE function, you ensure that the variable will be set anew if it is cleared by an unhandled error. You can now use

    Set rst = GetdbBe.OpenRecordset("...", dbOpenDynaset)

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

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    In the Declarations are of your form put this:
    Dim ws As dao.Workspace
    Dim dbBE As dao.Database

    In the Form_Open event, put this:
    Set ws = DBEngine.Workspaces(0)
    Set dbBE = ws.OpenDatabase("... full path to BE database...")

    When you open recordset, use dbBE instead of currentdb:
    Set rst = dbBE.OpenDynaset(...etc....)

    In Form_Close, put:
    ws.close
    dbBE.close
    set ws = nothing
    set dbBE = nothing
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    Hi Mark

    I had to change the following to advoid Compile Error - Method or data member not found

    'Set rst = dbBE.OpenDynaset(strSQL)
    Set rst = dbBE.OpenRecordset(strSQL)

    And had to remove dbBE.Close to avoid following RTE:

    <pre>Private Sub Form_Close()

    ws.Close

    'Run-time error 3420 Object invalid or no longer exist
    'dbBE.Close

    Set ws = Nothing
    Set dbBE = Nothing

    End Sub
    </pre>


    It seems to work am I on the right track?

    Thanks, John

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    Hi Hans

    In my situation I had to change:

    Set rst = GetdbBe.OpenRecordset("...", dbOpenDynaset)

    To

    Set rst = GetdbBE.OpenRecordset(strSQL)

    To get it to work

    Thanks, John

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

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    What was the problem with dbOpenDynaset?

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Speed Up Dlookup (A2k (9.0.6926 SP-3))

    I Had it coded wrong, changed to:

    Set rst = GetdbBE.OpenRecordset(strSQL, dbOpenDynaset)

    and it works

    Thanks, John

Page 1 of 2 12 LastLast

Posting Permissions

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