Results 1 to 9 of 9
  1. #1
    lorcan is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2021
    Posts
    4

    Use external functions (from referenced external access accdb file) in a query

    Hello,

    As I use common custom VBA functions in multiple Access accdb files, I finally placed all the common modules in a separate Access accdb.
    Then I add a reference to this "shared" Access accdb file in each Access projects (in the VBA editor), so that I can use my external public functions/subs.
    Until here everything is fine (I can use these external functions/subs in local modules).

    Then I wanted to use an external public function in a query, but it doesn't recognize/find any external functions/subs in the query design view
    The only workaround I found so far is to create a public function internally in the current Access (in a local module) which calls the external function from the external module. Then I can use the "intermediate" (local) function in queries.
    Are there another straightforward and better ways to call an external function in a query?



    Thanks in advance for your help!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sql is a different language to vba and can only reference local vba modules. So not aware of any other way than the way you are doing it

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    import the module w your functions into your db.

  4. #4
    lorcan is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2021
    Posts
    4
    Thank you for your answers

    @Ajax: that's what I thought. Such a shame I have to create intermediate functions/subs for that

    @ranman256: the goal here is to use shared functions/subs so that when one of them needs changes, I only edit it in one location without caring about callers In that way, I don't have to edit each Access files nor reimport modules in each of them

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Ajax View Post
    sql is a different language to vba and can only reference local vba modules. So not aware of any other way than the way you are doing it
    What am I missing then? Worked for me. In db2 I created a reference to db1.

    db1 contains a function
    Function ReturnValue() As Long
    ReturnValue = 5
    End Function

    In db2, I have a query with SELECT returnValue() as Test;
    It returns 5.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I stand corrected - I used to use a library module but in the end abandoned it because a) I occasionally needed to make a 'local' change, b) changes to the library could have unintended consequences in the apps that used it and c) the apps could be widely distributed to users with different folder rights which meant I have multiple copies of the library. Instead I now have a template db with all the 'library' functions plus numerous navigation and functionally standard forms (such as login/import/export/formatting) to give me a good start for a new app. I then delete those that are not required once the app is developed

  7. #7
    lorcan is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2021
    Posts
    4
    Quote Originally Posted by Micron View Post
    What am I missing then? Worked for me. In db2 I created a reference to db1.

    db1 contains a function
    Function ReturnValue() As Long
    ReturnValue = 5
    End Function

    In db2, I have a query with SELECT returnValue() as Test;
    It returns 5.
    You are right, it works!
    I usually prefix functions/subs with the name of its module when I call them (in VBA or queries), eg: TestModule.TestFunction()
    Prefixing external functions works in local VBA but not in local queries. Dropping the name of the external module (just keeping the name of the function) solved my problem Unfortunetaly, there is no "intellisense" using external functions in local queries in design mode (that's why I thought it didn't work at all)
    Thanks!

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome. Please do mark this one as solved then.
    I believe one drawback to a code library db is that if you need to edit code and any other db that references it happens to be open, you'll get a warning that you do not have exclusive access and your changes might not save. Whether they do save or not I have no idea.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    lorcan is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2021
    Posts
    4
    Quote Originally Posted by Micron View Post
    You're welcome. Please do mark this one as solved then.
    I believe one drawback to a code library db is that if you need to edit code and any other db that references it happens to be open, you'll get a warning that you do not have exclusive access and your changes might not save. Whether they do save or not I have no idea.
    Yes, I noticed this drawback (library DB locked when 1 other Access DB using it is just open). I'll have to ask every user to close all their Access files when replacing the common lib DB file . Or maybe use an unlocking tool…
    I think there's another drawback: if you edit the library DB from a DB that uses it, changes are not saved in the referenced DB. If you close and reopen the "client" DB, all the changes are lost.
    Anyway, I'm fed up with editing tons of Access file when I need to edit a single sub or function duplicated in each Acces file…

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

Similar Threads

  1. Set file external properties using VBA?
    By roaftech in forum Programming
    Replies: 6
    Last Post: 01-06-2020, 12:05 PM
  2. Replies: 16
    Last Post: 11-03-2013, 09:41 AM
  3. Replies: 1
    Last Post: 05-29-2013, 03:08 PM
  4. external accdb won't unlock
    By taxidev in forum Programming
    Replies: 4
    Last Post: 06-07-2011, 11:53 AM
  5. A way to force Access to update external file?
    By gkun in forum Import/Export Data
    Replies: 0
    Last Post: 10-27-2009, 06:11 PM

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