none
Can an app become “over-massaged”? RRS feed

  • Question

  • Win10 A10. Working on an “inherited” split database, trying to get it completely stable and then upgrade it by adding more tables and moving data into normalized form. There are holes in the data, i.e., some expected values simply don’t exist.

    In my attempts to find and rectify these issues, I spend “a lot of” time modifying queries and writing code.

    Yesterday, after modifying and closing a query, and opening another, the IDE stopped responding to the mouse when I tried to change the width of the Properties pane. Its btty was replaced two days ago to address this problem, so it's not a mouse problem.

    Compact and repair helped for about an hour. Saving, closing, reopening worked for a while.

    Since this database has had problems before, I did one more compact & repair, closed it, and imported everything from the front end into a clean copy of an Access 2010 database. It seems to be working now.

    Is what I am experiencing a common phenomenon?

    Can an app become “over-massaged”?

    What else should I be doing?


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, March 15, 2016 4:46 PM

Answers

  • I can think of two factors that might be contributing to the trouble you had. 

    First, though it *probably* isn't a factor here, there is a limit to the number of controls that can be created in the lifetime of a form -- it's about 754, IIRC.  That's not a limit to the number of controls that can currently exist on the form, but rather a limit to the number of controls that can be created, so it doesn't matter if you delete some controls before adding others; the controls you deleted still count toward this limit.  So this can be a factor in databases that are heavily maintained over a long time.  I believe this limit is reset if you import the form into a new database.  I know it is reset if you export the form to a text file (using the hidden SaveAsText method), delete the form, and then import it back into the database (using the hidden LoadFromText method).

    Second, in the course of long use and maintenance, it's not unusual for corruption to creep into various database objects.  Sometimes the objects keep working fine, but you can't open them in design view.  Or you may see erratic behavior when working with them in design view.  In cases like these, you may be able to clean up the corruption by importing the objects into a new database, or sometimes you may have to export an object to text and reload it from the text file, using SaveAsText/LoadFromText.

    You can minimize the chances of corruption creeping into your database by (a) only doing design work on a copy that resides on your local hard disk, not on a network share, and (b) never modifying VBA code behind a form or report unless the object is open in design view -- not in any of the other views that may be available.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Peter N Roth Wednesday, March 16, 2016 10:46 PM
    Wednesday, March 16, 2016 5:30 PM
  • 1) Thanks, Dirk. You are probably correct, controls don’t seem to be involved, but with an inherited thing, how can one ever be sure? Or how could I keep track of such a count on a creation of my own?

    2) My work is on my design machine, front end/back end, no network.

    3) Insofar as modifying VBA: I usually compile before running, but occasionally I will start running test code and get halted at a Sub that has a misspelling in it, and the code hangs on the signature line. I fix the error and proceed. Are you saying this is a problem?

    4) Simultaneous to all of this is the problem with the VBE7.dll update (KB3085515 I think), which makes me suspicious of everything.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    1) You can test to see how many have been created by adding a textbox and see the automatic number it gives as it remembers the last number used. Do this for each type of control and add them together and it will tell you the total number of controls ever added. REMEMBER do not save or it will increment these new controls as well.

    To control this, as dirk mentioned do your work in a copy of the data base, or what I usually do is import the object into a new shell and make the changes to it there and send it out as text and import it back to the original from the text file.

    2) this is good but be sure this is a copy and not the backup or live version.

    3) Yes this can be a problem. Is weird as it sounds this can be and has been a problem.

    4) this shouldn't be an issue. Just double check your references for kicks. Also check your code to see if there is a call to any previous engines.


    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

    • Marked as answer by Peter N Roth Wednesday, March 16, 2016 10:47 PM
    Wednesday, March 16, 2016 9:49 PM
  • (b) never modifying VBA code behind a form or report unless the object is open in design view -- not in any of the other views that may be available.

    Hi Dirk,

    I am afraid I do not understand what you mean with the above sentence. How or when could you modify VBA code behind a form that is not open in design mode?

    Imb.


    If you are using a db and an error pops up and you go into the debug where it highlights the error and then make changes it can cause corruption. This should be used only to get an idea of where the problem exists. Then go into design mode to fix the issue.

    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

    • Marked as answer by Peter N Roth Wednesday, March 16, 2016 10:48 PM
    Wednesday, March 16, 2016 10:00 PM

All replies

  • Can you rephrase:

    "There are holes in the data, i.e., some expected values simply don’t exist."

    ?

    When you're talking about AutoNumbers: They where not designed to be gapless.

    Wednesday, March 16, 2016 3:24 PM
  • The latest example was not an autonumber, but improper data entry (due to design flaws). For example, an entry in the Client table did not have the required corresponding entries in the Family table.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, March 16, 2016 4:07 PM
  • Are the relationships enforced by using referential integrity?

    Wednesday, March 16, 2016 4:14 PM
  • Good Morning Peter,

    Without knowing more of your db, and having faced problems with inherited db's, may I suggest (if this approach is possible in your case);

    1. output all the data into a spreadsheet
    2. delete all records
    3. delete your queries
    4. Normalize your Tables
    5. copy/paste your Tables - design only no data (This will reset your AutoID Fields)
    6. delete the old Tables
    7. Rename the new Tables
    8. Verify/correct any relationships
    9. Build new Queries to do what you need them to do
    10. use the database documenter to view the objects properties
    11. Send out as text all Forms/Reports/Modules
    12. import Forms/Reports/Modules to new shell
    13. Change the Record Source for the Forms/Reports/Modules to the new Tables
    14. Verify all code blocks
    15. Verify shell settings
    16. compact and repair both FE/BE
    17. correct any obvious data errors in the spreadsheet
    18. make sure the column data formats match the database tables
    19. import the data to the new Tables
    20. make copies of both FE and BE
    21. test the copies to verify data, tables, queries, forms and reports work as planned

    This may seem like a lot of work but if you are having serious issues this may be what is needed to resolve the issues


    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

    Wednesday, March 16, 2016 4:36 PM
  • I can think of two factors that might be contributing to the trouble you had. 

    First, though it *probably* isn't a factor here, there is a limit to the number of controls that can be created in the lifetime of a form -- it's about 754, IIRC.  That's not a limit to the number of controls that can currently exist on the form, but rather a limit to the number of controls that can be created, so it doesn't matter if you delete some controls before adding others; the controls you deleted still count toward this limit.  So this can be a factor in databases that are heavily maintained over a long time.  I believe this limit is reset if you import the form into a new database.  I know it is reset if you export the form to a text file (using the hidden SaveAsText method), delete the form, and then import it back into the database (using the hidden LoadFromText method).

    Second, in the course of long use and maintenance, it's not unusual for corruption to creep into various database objects.  Sometimes the objects keep working fine, but you can't open them in design view.  Or you may see erratic behavior when working with them in design view.  In cases like these, you may be able to clean up the corruption by importing the objects into a new database, or sometimes you may have to export an object to text and reload it from the text file, using SaveAsText/LoadFromText.

    You can minimize the chances of corruption creeping into your database by (a) only doing design work on a copy that resides on your local hard disk, not on a network share, and (b) never modifying VBA code behind a form or report unless the object is open in design view -- not in any of the other views that may be available.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Peter N Roth Wednesday, March 16, 2016 10:46 PM
    Wednesday, March 16, 2016 5:30 PM
  • Stefan - Referential integrity through-out, yes. Or rather, will be. This one of the things I'm working on.


    peter n roth - http://PNR1.com, Maybe some useful stuff


    • Edited by Peter N Roth Wednesday, March 16, 2016 9:44 PM incomplete answer
    Wednesday, March 16, 2016 9:11 PM
  • Wow, Chris, a thorough list! I would guess I’m about in the middle of it.

    One thing I can’t do is replace the autonumber field in one of the tables, because it’s being used to track Clients who are known by their IDs, and there’s a decade of backup paper copies.

    And it’s being used on a daily basis, sometimes by volunteers.

    I appreciate the list, though, and will keep it to hand.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, March 16, 2016 9:19 PM
  • Thanks, Dirk. You are probably correct, controls don’t seem to be involved, but with an inherited thing, how can one ever be sure? Or how could I keep track of such a count on a creation of my own?

    My work is on my design machine, front end/back end, no network.

    Insofar as modifying VBA: I usually compile before running, but occasionally I will start running test code and get halted at a Sub that has a misspelling in it, and the code hangs on the signature line. I fix the error and proceed. Are you saying this is a problem?

    Simultaneous to all of this is the problem with the VBE7.dll update (KB3085515 I think), which makes me suspicious of everything.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, March 16, 2016 9:35 PM
  • 1) Thanks, Dirk. You are probably correct, controls don’t seem to be involved, but with an inherited thing, how can one ever be sure? Or how could I keep track of such a count on a creation of my own?

    2) My work is on my design machine, front end/back end, no network.

    3) Insofar as modifying VBA: I usually compile before running, but occasionally I will start running test code and get halted at a Sub that has a misspelling in it, and the code hangs on the signature line. I fix the error and proceed. Are you saying this is a problem?

    4) Simultaneous to all of this is the problem with the VBE7.dll update (KB3085515 I think), which makes me suspicious of everything.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    1) You can test to see how many have been created by adding a textbox and see the automatic number it gives as it remembers the last number used. Do this for each type of control and add them together and it will tell you the total number of controls ever added. REMEMBER do not save or it will increment these new controls as well.

    To control this, as dirk mentioned do your work in a copy of the data base, or what I usually do is import the object into a new shell and make the changes to it there and send it out as text and import it back to the original from the text file.

    2) this is good but be sure this is a copy and not the backup or live version.

    3) Yes this can be a problem. Is weird as it sounds this can be and has been a problem.

    4) this shouldn't be an issue. Just double check your references for kicks. Also check your code to see if there is a call to any previous engines.


    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

    • Marked as answer by Peter N Roth Wednesday, March 16, 2016 10:47 PM
    Wednesday, March 16, 2016 9:49 PM
  • (b) never modifying VBA code behind a form or report unless the object is open in design view -- not in any of the other views that may be available.

    Hi Dirk,

    I am afraid I do not understand what you mean with the above sentence. How or when could you modify VBA code behind a form that is not open in design mode?

    Imb.

    Wednesday, March 16, 2016 9:52 PM
  • (b) never modifying VBA code behind a form or report unless the object is open in design view -- not in any of the other views that may be available.

    Hi Dirk,

    I am afraid I do not understand what you mean with the above sentence. How or when could you modify VBA code behind a form that is not open in design mode?

    Imb.


    If you are using a db and an error pops up and you go into the debug where it highlights the error and then make changes it can cause corruption. This should be used only to get an idea of where the problem exists. Then go into design mode to fix the issue.

    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

    • Marked as answer by Peter N Roth Wednesday, March 16, 2016 10:48 PM
    Wednesday, March 16, 2016 10:00 PM
  • If you are using a db and an error pops up and you go into the debug where it highlights the error and then make changes it can cause corruption. This should be used only to get an idea of where the problem exists. Then go into design mode to fix the issue.

    Hi Chris,

    Very remarkable. After writing the first prototype for the code, I run the program in the debugger, and fine tune the code on the spot. And in all my 92 different applications I never had any corruption. One time I had a corrupted FE: after a compact-and-repair!

    Or is this because I still use A2003?

    Imb.

    Wednesday, March 16, 2016 10:18 PM
  • 4) While it shouldn’t be an issue, the update is having international repercussions because it makes the Access and Excel wizards malfunction. Egads.

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, March 16, 2016 10:52 PM
  • Very remarkable. After writing the first prototype for the code, I run the program in the debugger, and fine tune the code on the spot. And in all my 92 different applications I never had any corruption. One time I had a corrupted FE: after a compact-and-repair!

    Or is this because I still use A2003?

    I doubt that A2003 is a factor in your experience, as the big change to the structure of the VB project happened with A2000.

    There are many reliable reports of editing code at run time occasionally causing corruption of the VBA project, and I've experienced it myself.  However, it is also clear from reports and my own experience that it only does so occasionally, so there must be other contributing factors.

    It seems to me that editing code while it is running must put the project into a precarious state.  I think I've been told that Access must maintain two versions of the compiled module -- the one that was running before the modification, and the one that has been modified and recompiled.  Eventually, one of those modules must be saved, and the other discarded.  I *guess* that, under some circumstances, Access gets confused, and saves the wrong one.

    This would all be combined with complications due to the fact that, since A2000, the VB project is a stored as a single object, rather than as separate compiled modules, but yet there's still a run-time distinction between individual modules. I do not know how all this works.  However, even though I occasionally slip up and modify code at run time, I do my best to avoid doing that so as to make it as easy and simple as possible for Access to manage the code and object modules.  When I inadvertently do modify the code while it's running, I stop and flip into design view as soon as possible, and recompile and save the code then.

    Imb, if your development procedures have kept you from experiencing any VBA code corruption while modifying code on the fly, I can only speculate that either you've been lucky, or there's something about your procedures that has kept it from being subject to the corruption others have experienced.  When you are in your fine-tuning process and need to modify code, do you stop the running code (by clicking the "Stop" button) first?  Do you explicitly compile the module after modifying the code?  Do you click the Save button after modifying the code, before resuming execution?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, March 17, 2016 3:42 PM
  • Imb, if your development procedures have kept you from experiencing any VBA code corruption while modifying code on the fly, I can only speculate that either you've been lucky, or there's something about your procedures that has kept it from being subject to the corruption others have experienced.  When you are in your fine-tuning process and need to modify code, do you stop the running code (by clicking the "Stop" button) first?  Do you explicitly compile the module after modifying the code?  Do you click the Save button after modifying the code, before resuming execution?

    Hi Dirk,

    I do not believe I am that lucky guy that had never a corruption in a vulnerable environment. I am too long and too intensive working in this way, that I believe - when luck exists - that I was lucky to choose a stable way of working.

    My different FE's are relative small. This is because in every application a reference is made to a library database (General), that accounts for more than 95% of total functionality. When I am developping, I try to move more and more from the different FE's to the General, if necessary supported by meta data tables. I code "on the fly" both in the FE and in General. The only difference is that changes in the FE are saved on closing, and in General not (it WAS in A97). So at the end of the modifications I have to grap these changes, and build it in in General by directly opening it. For this reason I mostly work via the prototype (directly in General) and fine tuning (via the running application), but I am not afraid to program a completely new routine all on the fly.

    To stop the code in a running application I place a breakpoint (brown dot and line) in the code, or I insert a code line: Glo_stop. I never use a (or the?) Stop button. Glo_stop executes a Stop, but only when the program is running in the development environment. I can leave these line in the production version, because it has no effect there. During the debugging I can place or remove the brown breakpoints at will.

    Because the code in General is re-used many, many times, and I do not want a Stop on every occasion, I can dynamically set a stop when that part of the code is to be run in that specific context. This stop can again dynamically be removed if not necessary any more.

    In the debug mode I can change lines, add lines, remove lines, replace lines, rerun a part of the code, so whatever you need. Occasionally I do a compile, just to see if I have syntax errors. I do not save the code inbetween. When I close the application (I have grabbed the changes in General) Access asks to save the changes, and I (always) answer Yes.

    Thus, so far I only do the bad things, but it works already for around 20 years, without corruption!

    Perhaps you can explain this behaviour by one difference with "standard-Access-debugging": I only use the debugger, no Immediate window, no Objects overview, no fancy other things. If I want to see the value of a variable, then I hoover over it. When the expression is to complicated to evaluate in the debugger, I assign it to a global variable ("jan" - a typical Dutch name) and hoover over jan. If I must experiment with the code, I add a couple of lines: jan = jan, so that I can see the result of the lines, without waiting before the next "official" line. But even then it is easy to add an additional line before its execution. Only when I am in a Select Case statement, or try to influence splitted lines, the debugger stops and you have to do all over again. But the jan's are still there!

    I hope I made my way of working clear. It makes the combination of developping and debugging real fun, and that is neccessary when you want to debug code in General, that is so generalized that it can handle any application.

    Imb.



    • Edited by Imb-hb Thursday, March 17, 2016 11:47 PM developping/debugging
    Thursday, March 17, 2016 11:28 PM
  • Imb – Interesting, but it’s not exactly clear what you’re doing!

    How big (how many lines) is General? How does it amplify built-in functionality?

    Do your forms use VBA (class) modules?

    How do you determine from within your code that Access is in design mode or running?

    When you “dynamically remove a stop”, are you toggling a global variable?

    Do you plan to market General?


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Friday, March 18, 2016 1:12 AM
  • 1. Imb – Interesting, but it’s not exactly clear what you’re doing!

    2. How big (how many lines) is General? How does it amplify built-in functionality?

    3. Do your forms use VBA (class) modules?

    4. How do you determine from within your code that Access is in design mode or running?

    5. When you “dynamically remove a stop”, are you toggling a global variable?

    6. Do you plan to market General?

    Hi Peter,

    1. In all my applications I use the same generalized forms. They are tuned towards the different entities in the database with the aid of a meta data table, that contains the information how a control on a form should "behave" - in the most wide sense - in relation to the field which it represents. All these general routines are stored in General, and General is referenced by all applications. Important is also that General does all the error handling, so in the FE's I never have to include error code.

    When I start a new application, just by giving it a name, this new database has already 95% of its total functionality inherited. It only lacks the tables and field definitions.

    2. On this moment General is about 70 Mb. On one hand it grows because of added functionality, on the other hand in reduces sometimes because of even more re-used code.

    How does it amplify built-in functionality?
    I do not fully understand this question. Perhaps you mean the re-usability of code. To open a form I used one sub, that is used by any form. To open a recordset I use one function that does all the looping, including error handling, including a variety of progress monitors if needed, etc.

    3. The few different forms that I use have each their form module. This is used to translate the form events to a generalized descrition, that is futher handled in General.

    4. I have one database (Leeg) that contains already the structure of the meta data tables. When this database is in the same map as the application under test, it is in design mode, else in production mode.

    5. Sometimes I want to investigate what happens when I open a form, after two other forms were opened already. This kind of context is important to construct the right sql-string. Placing a Stop (in fact Glo_stop) would also give a stop for the first two forms. Just before opening the third form I can define a global string that is recognized by the opening procedure. It is a little more than just toggling, and more than one of these "breaks" can be defined simultanuously.

    6. Not me, perhaps one of my sons.

    One additional remark. I do not use task bars or ribbons: all necessary functionality is already build in in the form itself.

    If you have more questions, please ask.

    Imb.

    Friday, March 18, 2016 7:12 PM
  • It could be that the segregation of most of your code in the General database prevents the kind of corruption that others have experienced.  It would make some sense, because the code being modified is not contained in the running database.  But since I don't know what the deep technical cause of the corruption is, that's only speculation.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, March 20, 2016 9:42 PM
  • It could be that the segregation of most of your code in the General database prevents the kind of corruption that others have experienced.  It would make some sense, because the code being modified is not contained in the running database.  But since I don't know what the deep technical cause of the corruption is, that's only speculation.

    Hi Dirk,

    No, I do not believe it is the special role of the General database. Also developping the different FE's - though making use of the procedures in General - can be done while running the program, without any corruption.

    But I realize, I must be the lucky guy who can develop in this way!

    Imb.

    Sunday, March 20, 2016 10:10 PM
  • Imb-hb –

    This may be taking this thread in another direction.

    I have extracted 3 tables from the inherited database I’m working with. The structure is shown in the images below.

    I’ve shortened and scrambled the content for privacy reasons.

    I’ve placed a copy in my public OneDrive folder here: https://onedrive.live.com/?id=D6606A3EE4BF95F0!112&cid=D6606A3EE4BF95F0

    Would you be so kind as to show us what your use of General would produce using these tables?

    If you don’t want to divulge the General code, would you show the calls to the procedures?

    Can you publish the database as an ACCDE so we can see how it works?

     

     

     


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Monday, March 21, 2016 8:28 PM
  • This may be taking this thread in another direction.

    I have extracted 3 tables from the inherited database I’m working with. The structure is shown in the images below.

    I’ve shortened and scrambled the content for privacy reasons.

    I’ve placed a copy in my public OneDrive folder here: https://onedrive.live.com/?id=D6606A3EE4BF95F0!112&cid=D6606A3EE4BF95F0

    Would you be so kind as to show us what your use of General would produce using these tables?

    If you don’t want to divulge the General code, would you show the calls to the procedures?

    Can you publish the database as an ACCDE so we can see how it works?

    Hi Peter,

    Thank you for your interest. I agree this is a complete different discussion, so it would be better to start a new thread, or perhaps start a private communication.

    Unfortunately I cannot read your database, as I am still using A2003. In this way I still can support my "poorest clients". And for my purposes higher Access versions have not added functionality.

    In fact what I have done is build a RAD-tool around Access. When you compare all kind of different applications, they all use records with fields, and relations between records in different entities. The processes around entities, tables and fields I have generalized. The general procedures are gathered in the General database, the specific details are stored in meta data tables. From then on it makes no difference any more what kind of application you are referring, they are all the same.

    I will be glad to describe these processes. But my experience is that most people hook off finally, because the investment in "overhead" is too much for their one or two applications. But once you are through, it makes hardly any difference if you manage 2 or 92 applications. And the applications are dynamical: easy to extend and change.

    So let me know if you are interested in further discussion, or leave an email address.

    Imb.

    Monday, March 21, 2016 11:22 PM
  • OK, yes, still interested. I will save the database in 2003 format and post it to a new discussion thread entitled "RAD tool around Access"

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, March 22, 2016 1:04 AM
  • But once you are through, it makes hardly any difference if you manage 2 or 92 applications. And the applications are dynamical: easy to extend and change.
    See ... this looks like an advert to me. And it makes more sense to me to consider acquiring such a tool than to write 70MB of code that is already writ.

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, March 22, 2016 1:18 AM
  • But once you are through, it makes hardly any difference if you manage 2 or 92 applications. And the applications are dynamical: easy to extend and change.

    See ... this looks like an advert to me. And it makes more sense to me to consider acquiring such a tool than to write 70MB of code that is already writ.

    Hi Peter,

    I prepared a demo for you as mde. I prefer to send it to you personally instead of "throwing it in the crude world".

    It is a database about films, that is completely managed by my youngest sun, who is mentally disabled (Down syndrom), but he can read and write. Almost all forms are generalized forms, tuned by meta data tables, so all done by General (in production environment General is renamed to Film_general).

    I you supply an email address to me, then I can send the database with WeTransfer.

    Imb.

    Tuesday, March 22, 2016 10:39 AM