none
Workaround for Microsoft Access Add-In Bug RRS feed

  • Question

  • There is a bug that has plagued every version of Access that I am aware of, which has never been resolved.  I would like to know if there is any way to work around this issue.

    I have developed several Microsoft Access add-ins at work (using the USysRegInfo technique).  When the user has focus on the add-in, and then creates a new query or table (e.g. via "Query Design" item), it is created in the Add-In's database file, and not in the currently opened database file.

    This is very confusing to the user, especially because some of my add-ins deal with importing data for use in Access, and are useful if they can stay open while the user is working on other things.

    It seems only logical that the query or table should always be created in the user's opened database when the user wants to create one.

    Is there any way to work around this in my add-in, programatically or otherwise?



    • Edited by Transistor1 Tuesday, September 22, 2015 5:51 PM fix grammar
    Tuesday, September 22, 2015 5:48 PM

All replies

  • I don't have much experience in this area so I have more of a question.

    Since you stated the Add-In's database file is updated but not the current opened database...

    does this mean there are actually two separate databases and the opened database gets a list of Tables & Queries from the Add-In database If so how do you update that list between the two when a change is made in order for the Opened database to see the new list in Add-Ins?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, September 22, 2015 6:49 PM
  • does this mean there are actually two separate databases and the opened database gets a list of Tables & Queries from the Add-In database

    Yes, that's exactly what happens.  An add-in is just an .mdb or .accdb file re-named as .mda or .accda.  The USysRegInfo table in the .mda simply contains registry entries that are added when the user "installs" it with the Add-In Manager (in the "Database Tools" tab).  The entries give the Add-In a name in the Add-Ins menu, and tell Access where the add-in lives and what VBA function to call when the menu item is clicked.

    Here's a sample of a USysRegInfo table (from my website; I just noticed it's missing the slashes in the registry keys-- I'll need to fix that, but should give you an idea)

    If so how do you update that list between the two when a change is made in order for the Opened database to see the new list in Add-Ins?

    I'm not sure I understand this part of the question. Would you mind elaborating?


    • Edited by Transistor1 Tuesday, September 22, 2015 7:09 PM Forgot to add link
    Tuesday, September 22, 2015 7:07 PM
  • If so how do you update that list between the two when a change is made in order for the Opened database to see the new list in Add-Ins?

    I'm not sure I understand this part of the question. Would you mind elaborating?


    Yes,

    Please excuse my lack of knowledge of how this works.

    It seems to me that in a database, when you are viewing a record in a Form and you make a change to the record via a subForm or Query or other means that you have to provide a method for the Form to refresh the record or Requery the record.

    I just wonder if when you are making your change to the Add-Ins that there needs to be some sort of refresh or Requery to get the list of Tables and/or queries updated. I am guessing you are referring to the list in the Navigation Pane which is also a Table of Records.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, September 22, 2015 7:26 PM
  • It's no problem-- I don't think Add-ins are very highly utilized in general.  Since it's not a common thing to use, I'm not sure how much help I'll get on this, but this problem has been driving me crazy long enough that I have to ask.

    Think of the Linked Table Manager as an example of an add-in that you're probably familiar with (I'm fairly certain it is/was one; at least in Access 97 I believe)

    When the user activates your add-in, you can have it bring up a form, which is probably the most common thing to do.  The form itself lives in the Add-in's database, but it displays in the database that is currently open.

    As an example of one of the Add-Ins I've created at work; I have one that displays tables from a remote database source and allows the user to link them into their current Access database.  It's a more user-friendly way of linking remote ODBC sources for folks who wouldn't know how to do that.

    So the user opens the add-in, links the tables they need, and then they leave my Add-in open, and immediately go to create a new query with Create->Query Design.  The list of tables that pops up is from the Add-in database, not their current database (that is, the query is being created in the Add-in database).

    However, if the user was to first click on an item in the Navigation Pane, focus changes from the Add-In database back to the current database.  If they then click Create->Query Design, the list of tables that appears is (correctly) from the current database and not the Add-in database.

    So it's not a refresh issue that's happening, but a focus issue.  No data is [intentionally] being updated in the Add-in database.

    Does this help?


    • Edited by Transistor1 Tuesday, September 22, 2015 8:02 PM
    Tuesday, September 22, 2015 8:01 PM
  • Hi,

    Could you provide the detail steps to reproduce that issue?

    Thursday, September 24, 2015 5:47 AM
  • This assumes Office 2010, but it works in any version.
    1. Create a new Access database C:\Temp5\Addin.accdb
    2. Add a new table called USysRegInfo:
          CREATE TABLE USysRegInfo (
            Subkey Text(255)
            , [Type] Integer
            , ValName Text(255)
            , [Value] Text(255)
          );
    3. Fill in the following values:
      Subkey Type ValName Value
       
      HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&My Addin
      0

       
      HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&My Addin
      1 Library |ACCDIR\Addin.accdb
       
      HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&My Addin
      1 Expression =openaddin()
       
      HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&My Addin
      1 Version 1
    4. Add a new form and call it "Form1".  It does not need to have anything on it.
    5. Create a new module, and enter the following code:
      Function openaddin()
        DoCmd.OpenForm "Form1"
      End Function

    6. [Optional 1 of 3] Go to the File tab, and click Info
    7. [Optional 2 of 3] On the right pane, click "View and edit database properties"
    8. [Optional 3 of 3] Under Title, type "My Addin", and click OK.
    9. Save and close this database
    10. Open a different database that has some tables and queries in it.
    11. Go to Database Tools, and choose Add-ins, and "Add-In Manager"
    12. Choose "Add New..."
    13. Above the Open button, choose "All Files (*.*)"
    14. Browse to C:\Temp5\, and choose Addin.accdb, and click Open.
    15. Click OK
    16. Note: If you get an error that you don't have registry access, you may need to re-open this database with administrator privileges and try installing the Add-in again.
    17. Go to Database Tools->Add Ins again
    18. Choose "My Addin"
    19. Click on the form that opened up, to make sure focus is on the form
    20. While focus is still on the form, click Create->Query Design
    21. You should see a list of tables and queries when the new query window opens.
      NOTE: THESE TABLES AND QUERIES LISTED ARE NOT FROM THE CURRENT DATABASE; THEY'RE FROM THE ADD-IN DATABASE.  THIS IS THE ACCESS "BUG".
    22. Close the new query you just created.
    23. Highlight any table in the Navigation Pane in your current database
    24. Now click Create->Query Design
      Now, you can see the tables and queries from the current database. THIS IS THE EXPECTED BEHAVIOR.








    • Edited by Transistor1 Thursday, September 24, 2015 4:39 PM
    Thursday, September 24, 2015 4:07 PM
  • Sorry-- I've had to edit the steps a bit; so if you've received them in your email and you're working on them, please look at the steps on the forum directly instead.
    Thursday, September 24, 2015 4:26 PM
  • Hi Transistor1,

    Base on my test in Access 2013, I can reproduce it too.

    I suggest that you could submit the feedback through Access 2013. (Click smile icon, Ctrl + Alt +7; Ctrl + Alt + 8)

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 28, 2015 2:58 AM
    Moderator
  • Hi Starain,

    Thank you very much for your reply.

    If I understand correctly, reporting it through 2013 will help Microsoft be aware of the problem to fix it in future versions.  That's great, but I was hoping that someone could also help me find a workaround for the existing versions.

    I'm going to go ahead and submit the report through Access 2013 right now, as you suggested.


    Monday, September 28, 2015 1:00 PM
  • I have developed several Microsoft Access add-ins at work (using the USysRegInfo technique).  When the user has focus on the add-in, and then creates a new query or table (e.g. via "Query Design" item), it is created in the Add-In's database file, and not in the currently opened database file.

    Hi Transistor1,

    I have no clear picture of what you mean with the Add-In's. I do not use them, I think, and so I have not studied them.

    But perhaps there is some analogy to the way I work. All my applications work with the same referenced library database, which takes account of more than 95% of all the functionality. Besides that, these applications can also refer to the data of some external databases. In the normal situation the tables are referenced that are in the same database as where the code resides. But by using late binding of the tables you can access any table in any database at will.

    Is this about what you are searching for?

    PS. I do not know about Query Design or QueryDefs, as I do not use them.

    Imb.

    Imb.

    Monday, September 28, 2015 2:02 PM
  • Hi Imb,

    Thanks for your reply.  I also use VBA libraries, by referencing an external .accdb or .mdb file (in VBA Tools->References menu).  It's useful for sharing logic, but it's different than an add-in. 

    The Linked Table Manger is an example of a built-in Access add-in.  Add-ins allow you to extend the functionality of the entire Access application for other users.  An Add-In can be used while working on any Access file, not just  in one application.

    I explained how to reproduce this problem in a post above.  If you try to reproduce it, it will also give you an idea of how add-ins work.



    • Edited by Transistor1 Monday, September 28, 2015 2:14 PM
    Monday, September 28, 2015 2:13 PM
  • Hi Starain,

    I thought I had seen the "happy" and "sad" faces in Access 2013 retail before, but I can no longer find them.  I also tried Ctrl+Alt+7, then Ctrl+Alt+8 as you suggested, but it didn't work.   I also have the Access 2016 demo installed, so I tried it there as well, but I still couldn't find it.

    Would you be able to guide me as to where I can find the feedback in Access?

    Thanks in advance!

    Monday, September 28, 2015 2:16 PM
  • Thanks for your reply.  I also use VBA libraries, by referencing an external .accdb or .mdb file (in VBA Tools->References menu).  It's useful for sharing logic, but it's different than an add-in. 

    The Linked Table Manger is an example of a built-in Access add-in.  Add-ins allow you to extend the functionality of the entire Access application for other users.  An Add-In can be used while working on any Access file, not just  in one application.

    Hi Transistor1,

    I can now see that Add-In's are different things. It looks complicated to me with all the registry stuff.

    At second sight I think I do not need them. All my (> 80) applications share the same library database, which includes already all the necessary functionality for any of my applications. New functionality can easily be added, and is immediately available in all the other applications. 

    But thank you for your explanation.

    Imb.

    Monday, September 28, 2015 2:48 PM
  • I've been building Add-ins since Access 2000. None of mine have ever shown this behavior. The only thing I do differently is to open my add-in entry point form in Dialog mode. If you'd like to get my add-ins they are all free at That'll do IT.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, September 28, 2015 5:21 PM
  • I've been building Add-ins since Access 2000. None of mine have ever shown this behavior.

    I've been building them for at least 8 years, and they have shown this behavior across all versions of Access. It's less obvious in Access 2002, but much more obvious in 2007+.

    Are your add-ins modal or modeless?

    If they are modal, you obviously won't see the behavior. Would you mind walking through the steps of the problem as shown above, as the behavior has been confirmed by Starain Chen of Microsoft?

    Edit - I tried downloading a couple of your add-ins but they won't work with 64-bit Access.  The "Table Restorer," for example, tells me that the "wizard you've requested is not installed or in a bad state."

    When I try to open the .mda directly, it tells me, "Please open with 32-bit Access". I don't have a 32-bit installation available at the moment.

    You can try my "commercial" add-in, Access SQL Editor, here.  I was trying to avoid plugging my product, but it's the only add-in that I have available for public download.  All of my others are used in my "day job".

    When used with Access 2007+, the behavior will be obvious with this add-in.  When you're editing a SQL query in my add-in, then you create a query with Access's built-in editor with the focus still on the editor, Access tries to create the new query in the add-in database.


    • Edited by Transistor1 Monday, September 28, 2015 6:06 PM
    Monday, September 28, 2015 5:26 PM
  • Okay i see what you are talking about...I think. One of the requirements with an add-in is that you specify which DB engine you are using. There is the CurrentDB and the CodeDB. Without seeing your code, I can't know what you are doing so I'm not going to be of any help to you.

    And yes, unfortunately, all my add-ins are 32-bit made for Access 32-bit. They won't run in 64-bit as you probably know.

    You asked if my add-ins ran modal. I stated earlier that I opened my add-in form in Dialog mode...much the same thing as modal.

    Instead of blaming this on a bug, I'd look for a solution.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, September 28, 2015 8:47 PM
  • Hi Bill

    Instead of blaming this on a bug, I'd look for a solution.

    That's exactly what I'm trying to do. I don't want to blame anyone; I'm looking for a workaround, as the title suggests.

    I've been looking for a solution for years.  Posting on a forum is my last resort because I've written an add-in that has been rolled out to several colleagues and I've been asked this more than once.  I've tried making the file read-only, compiling it into an .mde, password-protecting the project; anything I could think of.

    Any ideas?



    • Edited by Transistor1 Monday, September 28, 2015 8:55 PM
    Monday, September 28, 2015 8:50 PM
  • Since the problem appears to be where the focus is set, you might have to remove the "New Query" menu item which would force the user to use the Nav Pane. Or maybe even use that item to force focus on the database and not your add-in form.

    Short of that, you'd have to share your code with us so we might see what's going on. I realize you might not want to do that, but it might be the way to get your answers.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, September 29, 2015 12:27 AM
  • Hi Bill,

    Thank you for your reply.

    The problem does not relate to that specific add-in; it happens with all non-modal add-ins.  Whether the user uses the "New Query" button to create their query, or whether they create it in Access; the result will be the same once they shift focus from the Add-in to Access.

    You can reproduce it yourself in Access, as posted earlier.  Here are the steps:

    This assumes Office 2010, but it works in any version.
    1. Create a new Access database C:\Temp5\Addin.accdb
    2. Add a new table called USysRegInfo:
          CREATE TABLE USysRegInfo (
            Subkey Text(255)
            , [Type] Integer
            , ValName Text(255)
            , [Value] Text(255)
          );
    3. Fill in the following values:
      Subkey Type ValName Value
       
      HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&My Addin
      0

       
      HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&My Addin
      1 Library |ACCDIR\Addin.accdb
       
      HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&My Addin
      1 Expression =openaddin()
       
      HKEY_CURRENT_ACCESS_PROFILE\Menu Add-ins\&My Addin
      1 Version 1
    4. Add a new form and call it "Form1".  It does not need to have anything on it.
    5. Create a new module, and enter the following code:
      Function openaddin()
        DoCmd.OpenForm "Form1"
      End Function
    6. [Optional 1 of 3] Go to the File tab, and click Info
    7. [Optional 2 of 3] On the right pane, click "View and edit database properties"
    8. [Optional 3 of 3] Under Title, type "My Addin", and click OK.
    9. Save and close this database
    10. Open a different database that has some tables and queries in it.
    11. Go to Database Tools, and choose Add-ins, and "Add-In Manager"
    12. Choose "Add New..."
    13. Above the Open button, choose "All Files (*.*)"
    14. Browse to C:\Temp5\, and choose Addin.accdb, and click Open.
    15. Click OK
    16. Note: If you get an error that you don't have registry access, you may need to re-open this database with administrator privileges and try installing the Add-in again.
    17. Go to Database Tools->Add Ins again
    18. Choose "My Addin"
    19. Click on the form that opened up, to make sure focus is on the form
    20. While focus is still on the form, click Create->Query Design
    21. You should see a list of tables and queries when the new query window opens.
      NOTE: THESE TABLES AND QUERIES LISTED ARE NOT FROM THE CURRENT DATABASE; THEY'RE FROM THE ADD-IN DATABASE.  THIS IS THE ACCESS "BUG".
    22. Close the new query you just created.
    23. Highlight any table in the Navigation Pane in your current database
    24. Now click Create->Query Design
      Now, you can see the tables and queries from the current database. THIS IS THE EXPECTED BEHAVIOR.


    If there's any code in particular that you think would be useful to share other than this, I'm happy to post it.  My "commercial" (for lack of a better word) add-in is implemented as an ActiveX Object hosted on a simple form.  If you message me at the e-mail listed here, I'll send you the password for the add-in's code so you can see what's happening behind the scenes.  The form itself is very simple.


     




    • Edited by Transistor1 Tuesday, September 29, 2015 12:45 AM
    Tuesday, September 29, 2015 12:37 AM
  • Hi,

    >> I can no longer find them.  I also tried Ctrl+Alt+7, then Ctrl+Alt+8 as you suggested, but it didn't work.

    It seems that it related to the version of Office. My Office version is Office 365 ProPlus – en-us. I checked the Office professional, it doesn’t have the smile icon.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 29, 2015 1:35 AM
    Moderator
  • (1)...your add-ins but they won't work with 64-bit Access.  

    ...it tells me, "Please open with 32-bit Access".

    (2)...I don't have a 32-bit installation available at the moment.

    (3)...When you're editing a SQL query in my add-in, then you create a query with Access's built-in editor with the focus still on the editor, Access tries to create the new query in the add-in database.


    (1) I didn't see what bit rate Starain Chen tested with. Is it possible this only affects 64bit?

    (2) The 32bit and 64bit should be on the same disk or ISO file

    (3) Should you maybe close the add-in before building a query in the product database file? As a possible workaround?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, September 29, 2015 9:45 PM
  • (1) I didn't see what bit rate Starain Chen tested with. Is it possible this only affects 64bit?

    (2) The 32bit and 64bit should be on the same disk or ISO file

    (3) Should you maybe close the add-in before building a query in the product database file? As a possible workaround?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Hi Chris,

    Thanks for your reply.

    (1) Unfortunately, no.  I have 32-bit at work, but I can't install 3rd party add-ins on my work computer. This "bug"(?) affects all "bitness" versions of Access.

    (2) I know... I have installed 32-bit before, but Office takes quite a while to install, and I don't need 32-bit.  I'm quite certain the bitness is irrelevant here.  It relates to non-modal add-ins.  I'm certain because the add-ins I've created at work under 32-bit exhibit the behavior (that's what prompted me to ask the question, not my "commercial" add-in).

    (3) That's really the only way around it that I know of, but the users would like to keep the add-in open; so it's not really a solution for me... but thanks for the suggestions.





    • Edited by Transistor1 Tuesday, September 29, 2015 11:41 PM
    Tuesday, September 29, 2015 10:55 PM
  • Hi Transistor1,

    My Access is 64-bit. I don’t find a workaround to deal with it.

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 30, 2015 1:32 AM
    Moderator