Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    57

    Unanswered: dlookup to an outside DB

    I'm trying to do a DLookup on a table that I've opened in an outside DB...any ideas...
    Here's my first effort....thanks in advance...


    Private Sub Command6_Click()
    Dim dbConn As ADODB.Connection
    Dim tcTbl As ADODB.Recordset
    Dim varID As Variant
    Dim varPW As Variant

    Set dbConn = New ADODB.Connection
    dbConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\Bronx1\Common2\Dispatch\Db\Scrub\Scrub_BE .mdb"
    dbConn.Properties("Jet OLEDB:Max Buffer Size") = 256

    Set tcTbl = New Recordset
    tcTbl.Open "DispID", dbConn, adOpenForwardOnly, adLockOptimistic
    varPW = DLookup("[PW]", "dbConn!DispID", " [DispatchID] = '" & Forms!LogIn!DispID & "'")
    MsgBox varPW


    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Less complicated:

    Create a linked table and then use it like it's an internal table. After that, you can take the table as a DAO.TableDef object and change it's properties if you want to use code to link to a different table in a different database.

    tc

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You know how to create a connection and a recordset, so why bother with a slow DLookup? Why not use the Find method of the recordset?

    All you would have to do is add:

    Code:
    tcTbl.Find "[DispatchID] = '" & Forms!LogIn!DispID & "'"
    if not tcTBL.EOF then
       varPW=tcTbl!PW
    Else
        varPW=""
    End If
    If DispatchID is indexed this should be far faster. Assuming there are more than a handful of records.

  4. #4
    Join Date
    Oct 2004
    Posts
    57
    thanks DCKunkle...
    its great to learn new things everyday...

Posting Permissions

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