Results 1 to 10 of 10
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Using Domain Functions with External Database

    Hi,

    I'm trying to do a DLookup to get values in a table. The table is not in my current database, it is in a common-code database called "AccessCommonCode.accdb".
    This database contains utility code that I use from various "main" databases, by adding a reference in each database to "AccessCommonCode.accdb").
    This works very well. Some VBA commands that normally use CurrentDB require change to CodeDB.

    For example, if a table resides in the "main" db, I would use:
    Code:
    CurrentDB.Execute "Delete * From MyTable"
    But if the table resides in the referenced common code db, I need to use:
    Code:
    CodeDB.Execute "Delete * From MyTable"
    Changing DB name from CurrentDB to CodeDB causes code being executed in the referenced DB to work properly.

    Now I need to do a DLookup on a table which resides in the referenced DB.
    This DLookup is being executed in the "main" DB.


    Code:
    MyName = DLookup("MyName","MyTable", "ID = 25"
    This causes an error because it can't find "MyTable" in the "main" db.
    "MyTable" indeed exists in the referenced DB, but not in my "main" DB.

    Is there a way to modify the domain part of the DLookup so it points to the table in the referenced DB?
    Last edited by RMittelman; 01-21-2024 at 11:54 AM.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Try:

    MyID = Nz(CodeDb.DLookup("MyName", "MyTable", "ID=25"), "")


    Why would you use a variable named MyID to hold a name?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Over the years I've been using these replacement domain aggregate functions which allow you to also pass the database object as an argument in the function calls. Also review Allen Browne's eLookup: http://allenbrowne.com/ser-42.html

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by June7 View Post
    Try:

    MyID = Nz(CodeDb.DLookup("MyName", "MyTable", "ID=25"), "")


    Why would you use a variable named MyID to hold a name?
    Thanks for the suggestion. Tried that, didn't work. Causes error saying "Method or Data Member not found". From the "main" database it doesn't seem to be able to do anything with the "CodeDb" object. The code in the referenced DB, when executing, seems to be able to differentiate between CodeDB and CurrentDB. But this statement is executed in the "main" db, when execution of methods in the referenced DB has completed. None of the properties or methods that show up when you type "?CodeDB.Name" or other properties, in the immediate window are recognized.

    Maybe there is a way to do this using the full path-name of the referenced DB?

    BTW, that was a typo. I meant "MyName = DLookup(......"

  5. #5
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Gicu View Post
    Over the years I've been using these replacement domain aggregate functions which allow you to also pass the database object as an argument in the function calls. Also review Allen Browne's eLookup: http://allenbrowne.com/ser-42.html

    Cheers,
    Thanks Vlad. I will download your sample DB and check it out. But I am still unsure about how you obtain the database object for the referenced DB. The object I'm calling "CodeDB" in my main DB does not seem to be working or recognized by Access. If I type "?CodeDB.Name" in the immediate window, it errors out. This also happens with any property or method that pops up for auto-complete when typing "CodeDB." in immediate window.

    Maybe there's a way to loop through the references collection, find the name "AccessCommonCode", and pull out it's path, then open the DB using that?

    I'm beginning to think it was a bad idea to put the table I need in the referenced DB rather than keeping it in the "main" DB. It worked fine the old way.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm beginning to think it was a bad idea to put the table I need in the referenced DB rather than keeping it in the "main" DB
    If you're trying to run procedures from a library db that is one thing. If you're trying to access table data from some a table in another db, linking to it will greatly simplify things. I don't see the sense in doing it any other way except maybe sql. Even then, why not just link instead?
    Last edited by Micron; 01-21-2024 at 01:30 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I don't know how you instantiated CodeDb object variable but prefixing with an established application object is demonstrated in https://www.access-programmers.co.uk...tabase.163471/

    However, I agree that linking table seems more sensible.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The file I uploaded is not a db but just a VBA module. I would say this part is fairly easy to change if needed, use a boolean argument to use CodeDb or try to pass CodeDb as the pdb argument of the tLookup and see what happens.
    Code:
      Static dbLookup As Dao.Database
        Dim rstLookup As Dao.Recordset
        Dim varvalue As Variant
        Dim strSQL As String
        
        ' if calling function sends a db then we'll use that
        If Not pdb Is Nothing Then
            Set dbLookup = pdb 'Vlad: if this line fails when you pass CodeDb as argument see the next comment
        Else
            ' If our db vari is not initialised or the calling
            ' process wants the db objects refreshed then we'll
            ' set the db var using CurrentDb()
            If dbLookup Is Nothing Or pLookupReset = tLookupRefreshDb Then
                If Not dbLookup Is Nothing Then 'Vlad -you can add an additional optional argument boCodeDB and if that is true Set dbLookup=CodeDB Else Set dbLookup = Nothing
                    Set dbLookup = Nothing
                End If
                Set dbLookup = CurrentDb()
            End If
        End If
    Sorry, just got home from a full day of skiing and can't test it now, please let me know how you're making out. You can always use a local user system table to store the location of the "common code db" in the front-end, set a db variable to that and call tLookup using that variable for the optional pDB argument.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks to everyone who replied to this thread. I figured out a fix.

    If the table is in my "main" database, everything works just fine. I have a class in my reference DB which opens a form to display information. Usually that's all that it does, but it can also allow choosing from a list of values. All information displayed and/or values shown on the form are taken from a table in the "main" database. Everything worked fine. Then I tried to add the functionality to a new "main" databse. I thought "why not add the table in the reference DB so I don't need to add it into every "main" db I may want to use it from?"

    That's where the problem started. I can't read the table from the code of the "main" database if it lives in the reference DB (unless I explicitly link to it-thanks Micron). But since the form is opened by instantiating a class object which lives in the reference DB, I simply added a property in the class called "ChosenItem". If an item is chosen on the form, it updates the table by setting a "isChosen" column in the table. Then when the form is closed, returning control to the class object, it first reads the table to find the record where "isChosen" is true, and returns that value to the ChosenItem property. This is all done in the form code and/or the class object code, both of which live in the reference DB. So no problems reading that table.

    Therefore, when control is returned from the reference DB class to the "main" DB code, I only need to check the ChosenItem property of the class rather than trying to read the table directly from the "main" DB. This works with no problems.

    Thanks for all of the answers, which helped me to create the solution!

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear you got your solution working. Sometimes I used the posted code for getting a value from a different db (lets call it a "shared" or "portal") without having to link the table(s) from it that, if exploited by an above average user, could allow direct access to the values in the linked table (such as UserIds, Passwords, etc.). So the weakest link was accessing the VBA project (not as big of an issue in an accde file).
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 01-26-2021, 12:20 PM
  2. Replies: 1
    Last Post: 06-12-2018, 10:59 PM
  3. Replies: 2
    Last Post: 03-25-2017, 07:23 PM
  4. Replies: 2
    Last Post: 01-29-2015, 03:07 PM
  5. Replies: 1
    Last Post: 05-29-2013, 03:08 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums