Tip The Ten Commandments of Access (1 Viewer)

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 11:22
Joined
Mar 22, 2009
Messages
771
Thou shalt not use "SendKeys", "Smart Codes" or "GoTo" (unless the GoTo be part of an OnError process) for these will lead you from the path of righteousness.

What are Smart Codes?

Thanks in advance.
 

Acceesbility

Registered User.
Local time
Yesterday, 22:52
Joined
Jan 4, 2017
Messages
32
For the newbie :eek:, can anyone throw a little summary light on the whys and why not's of each of of these 10 commandments ?

:)

  1. Thou shalt design normalized tables and understand thy fields and relationships before thou dost begin.
  2. Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One.
  3. Thou shalt choose a naming convention and abide by its wisdom and never allow spaces in thy names.
  4. Thou shalt write comments in your procedures and explain each variable.
  5. Thou shalt understand error handling and use it faithfully in all thy procedures.
  6. Thou shalt split thy databases.
  7. Thou shalt not use Autonumber if the field is meant to have meaning for thy users.
  8. Thou shalt not copy and paste other people's code without at least attempting to understand what it does.
  9. Thou shalt not use "SendKeys", "Smart Codes" or "GoTo" (unless the GoTo be part of an OnError process) for these will lead you from the path of righteousness.
  10. Thou shalt back-up thy database faithfully, working not on thy Production Database, but on the Prototype Copy, as it is right and good to do.
 

kipcliff

Registered User.
Local time
Today, 00:52
Joined
Sep 19, 2012
Messages
71
1. You normalize your table to keep the repeated entries of data to a minimum. This reduces the chance of data entry errors and conflicts. You plan and sketch out your design, fields and relations beforehand so you can quickly and efficiently build your database.

2. People make mistakes, and it is easy to lose one's place in a table. Forms can help reduce input error by allowing finer control of data validation, and they present a more narrow focus for the user. I shall leave the issue of "Lookup Fields" to wiser heads.

3. A good naming convention makes it easy to identify the purpose of fields and objects, and also makes it easy to address them from code or to debug said code. Putting spaces in field names would necessitate the use of extra formatting [], which can be forgot, so better to avoid spaces altogether. Better to use CamelCase to differentiate words in a name.

4. Good comments in your code make it easier for any, including yourself, to understand what is intended by said code. This improves readability and debugging, and also reduces the chances of conflicts in case of additions or changes to code.

5. Explicitly handle all errors that your code can conceivably produce. Make the DB as easy to use and user-friendly as possible. Unexplained crashes and cryptic error messages serve only to frustrate users and impede work performance.

*hands off the baton*
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:52
Joined
Feb 28, 2001
Messages
26,946
There is a specific issue I will take with ChrisO's earlier comments regarding the need to dereference objects and close openable objects.

I have personally seen cases in which failure to close a Word Application Object (and ALSO for the case of Excel Application Objects) where failure to close the external object leaves a dangling and unsaved file floating in memory. Chris is probably correct about objects internal to Access such as recordsets and instantiated structures. However, for objects that "reach out" via automation to other applications, it is necessary to understand that the applications have a "life of their own" and must be TOLD to go away and shut things down etc. They DO NOT GO AWAY on their own. As to Outlook Application Objects, you MUST take care to know whether you have closed Outlook before you try to open it - and in fact if there IS an open-but-minimized Outlook window and you try to open a new one, your program WILL take a trap from which you cannot continue operation without some really complex recovery code.

As part of the closure process, you also hurt yourself if you fail to dereference object variables. Set obj-var = Nothing has this effect: When you instantiate something, be it a data structure, an array, or an application object for which you have a referencing variable (in effect, a type-casted object variable), you allocate something in your process's scratchpad (dynamic) memory. If you force the created object to stop what it is doing via .Close or whatever other syntax is appropriate, that allocated memory is STILL THERE. Causing the object to be dereferenced forces the WINDOWS (not Access) memory management routines to reclaim the memory occupied by the structure, which reduces the amount of virtual memory being used by your process. It will only take one instance of "Insufficient Virtual Memory" to teach you to close what you open and put away what you take out.

As to the discussion of GoTo cases, I'll try to be gentle, but basically it is incredibly unwieldy and of questionable purpose to completely avoid GoTo <common end of routine> cases when your routine has a lot of "entrance qualifications" and you have to do more than just a couple of tests in the front of your code.

One or two simple tests, or performing tests by setting local Boolean variables and then doing one If-Then-Else-End If could work OK, but there comes a point when having 10 nested If-Then-Else-End If structures becomes harder to read than the spaghetti that everyone deplores. Note that my comment ONLY applies to the "you are not yet ready to do this" kind of test where everything flows down to the orderly exit point of the code you just called.

Therefore, it is important to know that the guidelines are about as fluid as the "Pirate's Code" as referenced by Captain Jack Sparrow in the "Pirates of the Caribbean" movie series. "Just guidelines."
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:52
Joined
Feb 28, 2001
Messages
26,946
To Accessibility:

1. Thou shalt design normalized tables and understand thy fields and relationships before thou dost begin.

Normalizing tables is a DB jargon way of saying that an Apples table describes Apples and an Olive Oil table describes Olive Oil, and they have different properties so would be hard to define in a single table. You have to know how things are related - and how they are not related - to know how to work with them.

2. Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One.

Opening up the innards of your database is an invitation to disaster. If you wanted to track what people did, allowing direct control of tables essentially guarantees that you WILL be blind-sided. The "lookup" field is an idea whose time came and went in the same tick of the clock, but Microsoft didn't see it that way. The problem is how complex it is to work with such fields. VERY unwieldy at the code level.

3. Thou shalt choose a naming convention and abide by its wisdom and never allow spaces in thy names.

For simple programs it is easy to look at some variable and know what it means. However, that is definitely harder when you are doing a complex business model where many entities are related and thus have similar names. The part about spaces is mostly because of typing extra keystrokes and also because not all possible back-end upgrade hosts allow embedded spaces.

4. Thou shalt write comments in your procedures and explain each variable.

The first "real" job I ever had was to fix a bug somewhere in some code that had NO COMMENTS AT ALL. Worse, the variables were named A, B, C, etc. even though the language supported 16-character variable names. (I think that last comment gave away my age...) So first I had to laboriously track every branch in the code and then decide what each variable represented. I rewrote the code in a way that the NEXT maintainer wouldn't buy a voodoo doll with my face and then stick pins in it.

5. Thou shalt understand error handling and use it faithfully in all thy procedures.

This is because everyone knows the basic rule of life is "stuff happens." You have to be able to handle that stuff when it happens because if you don't, Access WILL handle it - and shut down your application.

Granted, some procedures are too simple to take an error, and the only reason they exist as separate procedures rather than in-line code is because you want to repeat that computation/process reliably wherever you use it. But most real-world activities are subject to errors caused by bad input or by issues with your computer's environment or a myriad of other "stuff." So by judicious choice of where to put error handlers, you make it possible for your program to GRACEFULLY avoid Access intervention.

6. Thou shalt split thy databases.

This applies in cases where one of two things must occur.

6.1 - you are sharing the data among many users and want to minimize lock contention. Therefore you split this into front-end (FE) and back-end (BE), with all queries, forms, reports, and modules in the FE and the data to be shared in the BE. Then you give users private copies of the FE and do a mapping trick or two so that they all "point" to the same BE.

6.2 - you are building a "pure" Access database but it grows to require more than 2 Gb of data, yet the excess is merely being archived. You can split the database so that you have an archiving BE (or more than one such BE) and one working FE/BE combination.

7. Thou shalt not use Autonumber if the field is meant to have meaning for thy users.

In this forum, you can research the topic "Natural vs. Synthetic Keys" and find a near-religious level war on the subject. Autonumbers are synthetic keys that were generated as a convenience for keeping records uniquely identified. However, they have no real (natural) relationship to the rest of the record. It is like a street address number in this sense: The fact that the Smiths live at 41 Maple Street and the Jacksons live at 43 Maple Street is of no meaning to anyone but the postal carrier. Equally, the fact that this record has ID number 12345 and that record has 123446 is of no consequence to any program except the code that has to find the record by its ID number.

By contrast, if you had a table of states for USA postal codes, the state names have 2-letter abbreviations that are good NATURAL keys... natural because the abbreviations are related to other information in the record in some way.

8. Thou shalt not copy and paste other people's code without at least attempting to understand what it does.

This is simply because sometimes you adapt code with side-effects that you didn't understand and it interacts with other parts of your program in some strange way.

9. Thou shalt not use "SendKeys", "Smart Codes" or "GoTo" (unless the GoTo be part of an OnError process) for these will lead you from the path of righteousness.

As to "GoTo" see my previous but recent comments. "SendKeys" and "SmartCodes" are attempts to make things happen automatically by not using Access to do your dirty work. Again, this is to be taken with a grain of salt, so to speak. If you have some kind of external program interaction that requires you to simulate key strokes or to support some strange automatic launching sequence, you are probably playing with something that does not expose its operations via the Component Object Model. This is not usually easy to do and from a software engineering viewpoint, is sort of the equivalent of a "square peg in a round hole." This rule is therefore more to be considered as an admonition to use the features sparingly and to understand why you need to use them as well as when you need to use them.

10. Thou shalt back-up thy database faithfully, working not on thy Production Database, but on the Prototype Copy, as it is right and good to do.

Production databases and prototype databases are INSTANTLY in the apples-and-oranges category. They are in the same GENERAL category but they have very different specific behaviors and requirements. You want to secure your production copy but you want a wide-open prototyping copy, because you want to prevent others from mucking about in your code. The best way to do that is to make a copy of the prototype (when you are ready for a new release) and do the final lock-down steps on the copy that is between the prototype and production. That intermediate copy, when fully secured, compacted & repaired, and backed up just in case something goes wrong, will BECOME your production copy. But you still have the prototype that looks just like it (other than not being locked down.)

Just remember, as noted earlier, "stuff happens" - which is why you make backups as often as possible.
 

Acceesbility

Registered User.
Local time
Yesterday, 22:52
Joined
Jan 4, 2017
Messages
32
Thank You Doc_Man

For the enlightenment !
:D

Stuff Happens
For sure it does !
I couldn't even register without the need of "Error Handling" :eek:

"Acceesbility" was meant to be "Accessability"
:banghead: :eek: :banghead:
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 16:52
Joined
Jan 20, 2009
Messages
12,847
There is a specific issue I will take with ChrisO's earlier comments regarding the need to dereference objects and close openable objects.

I half agree with ChrisO.

I have personally seen cases in which failure to close a Word Application Object (and ALSO for the case of Excel Application Objects) where failure to close the external object leaves a dangling and unsaved file floating in memory.

Technical point but one does not Close an Office Application object. The method is Quit.

Of course, the object will stay in memory if it is not Quit. You can dereference it inside VBA but all that does is destroy the pointer to the object. The application object itself will persist independently.

Chris is probably correct about objects internal to Access such as recordsets and instantiated structures.

I disagree with Chris on this. It is difficult for anyone to give an authoritative opinion because Microsoft doesn't document the exact processes. However it is possible to make a reasonable inference about what is going on.

When Access opens a recordset, it negotiates with the ACE database engine (a separate process) to have it built. ACE returns a memory address which Access uses to create a pointer to that structure. Destroying the pointer inside Access (Set to Nothing) does not tell ACE to release it. The Close method tells ACE it can demolish the structure. Without the Close the recordset is probably dropped after a time out. Consequently, it lingers in memory unnecessarily.

Objects that are opened should be Closed. Microsoft gives what I believe is incorrect advice, saying that Set to Nothing has the same effect as Close.

As part of the closure process, you also hurt yourself if you fail to dereference object variables.

On this I agree with ChrisO. Objects pointers are removed from memory when they go out of scope. This happens when the procedure ends, whether it is set to Nothing or not. ChrisO had disproved claims about memory filling unless object pointers are cleaned up by running a loop calling a procedure millions of times without setting its objects to Nothing. The memory did not fill up.

While I cannot say that the same is true for VBA, it is documented by Microsoft that objects in VB.NET should NOT be set to Nothing at the end of a procedure. This is because the reference to the object in that line of code will actually cause the object pointer to be maintained in memory even when it is no longer required.

Memory is managed in applications by counting references to the object. The Windows Garbage Collector will clean it up when the reference count falls to zero. Having the Set to Nothing at the end of a procedure means the reference count won't get to zero until that line is reached.

I do understand that VB.NET and VBA are different animals and that might make a difference. However, ChrisO's test still stands unless someone is able to repeat it with a different result.

Either way, with the amount of memory available in modern computers, having pointers in memory longer then they should certainly isn't something to lose sleep over.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:52
Joined
Feb 28, 2001
Messages
26,946
Galaxiom, you QUIT the program but CLOSE what the program opened. If in my other post I said that in a confusing manner, I apologize - but I don't stand down on the concept. Regrettably, I have no easy way to test the circumstance now since I have retired and the balky program in question is no longer under my control.

When ChrisO did the experiment, do you know if he used local or global object variables? I agree that objects should be cleaned up automatically when the pointer goes out of scope - but good programming practice suggests that you keep your code modular, passing in the data elements on which to operate, necessitating that the data elements you are going to use - the object pointers - DO NOT go out of scope so easily.

My concern was/is that if you keep on creating new objects without first dereferencing the old ones, they are still in scope until your code exits. Absolutely, YES, when your application exits, it gets wholesale cleaned up because your process or your "SVCHOST" shell (or whatever else you are using) exits and the memory associated with it gets wiped. At process termination, your scratchpad memory loses all structure and becomes just another bunch of free pages to be used as needed for subsequent operations. Until then, you risk virtual memory exhaustion errors for your process.

By the way, if you explicitly close the files opened by your app object, you really DON'T have to QUIT the app - you can just dereference it. See next discussion regarding "rundown."

Microsoft gives what I believe is incorrect advice, saying that Set to Nothing has the same effect as Close.

I believe that what happens is that setting the external app object to NOTHING will have the effect of a Close but its default closure behavior might not be what you wanted. You would lose data (since there is no connection to a session for the external application to ask the "Save" question) because you would probably get a Close/NoSave behavior. If that's what you wanted, then no harm, no foul. If you close all files before this moment, again no harm, no foul.

This rundown is equivalent to what other O/S's call "Process Rundown" status. To keep your directory and file structure sane, you CANNOT just stop the code. Your file extent pointers and file write-back buffers will be wrong if you were writing something at the time, so the code has to visit each of its I/O channels (via "handles") to assure that IF a file is involved, the file system is allowed to clean up any incompleteness or inconsistency.

At least SOME flavors of UNIX and (I know this for a dead-certain fact) OpenVMS have this concept. If OpenVMS has it, Windows probably has it, given that Dave Cutler modeled Windows NT on OpenVMS algorithms. (You have to do web searches, but the articles exist to support that claim.)

I will clarify that I have PERSONALLY SEEN that a copy of Word persisted in my task list if I didn't remember to explicitly close what I opened. I had to either (a) use task manager to kill it or (b) log out/shut down the computer. That was on Win 7 Professional 64-bit/Office 2013 32-bit, and happened as recently as Q1 of 2015 when that was observed. I was working on using Word and Bookmarks to do the equivalent of a UNIX "MAN" command - to open a manual to a specific page in "ReadOnly" mode. It did it, I witnessed it, and I will not step away from that statement.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 16:52
Joined
Jan 20, 2009
Messages
12,847
Galaxiom, you QUIT the program but CLOSE what the program opened.

It is not the code project but the Office application thathas the file open. So you tell the Office App to Close the file, then you tell the Application to Quit. That is the correct exiting sequence.

When ChrisO did the experiment, do you know if he used local or global object variables?

Probably neither. I don't know the details of the test but I do know that ChrisO was more than enough knowledgeable to design a test for his hypothesis, that objects did not need to be set to Nothing before they go out of scope because this happens automatically. I expect that he used variables declared inside a procedure and ran that procedure in a loop.

I agree that objects should be cleaned up automatically when the pointer goes out of scope - but good programming practice suggests that you keep your code modular, passing in the data elements on which to operate, necessitating that the data elements you are going to use - the object pointers - DO NOT go out of scope so easily.

It appears to me that you are suggesting that it is a better practice to reuse a module variable and pass it to the procedure each time. So once declared it sits around in memory and might never be used again. You see going out of scope as as a weakness? I can't agree with that. The if the variable is relevant only in the context of a procedure then it aught to to have the scope of the that procedure.

My concern was/is that if you keep on creating new objects without first dereferencing the old ones, they are still in scope until your code exits.

Yes. An object created needs to be closed and the application managing needs to Quit. What I took from Chris is that the object pointer itself is automatically removed when it goes out of scope so setting it to Nothing is not required.

By the way, if you explicitly close the files opened by your app object, you really DON'T have to QUIT the app - you can just dereference it. See next discussion regarding "rundown."

Really? How soon does the application disappear when it has no files open? It is important because in some of my applications I keep a hidden Word instance running at module level and pass files to it as required. It is a worry it it could spontaneously Quit if left to itself for too long.

I accept that if the application quits, any files it has open will be released from memory. But not simply by dereferencing the app form the code project.

I believe that what happens is that setting the external app object to NOTHING will have the effect of a Close but its default closure behavior might not be what you wanted. You would lose data (since there is no connection to a session for the external application to ask the "Save" question) because you would probably get a Close/NoSave behavior.

I believe that what happens when the pointer is destroyed is that the code
loses its ability to communicate with the object. The object itself remains in memory which is why it should be told to Quit, ideally after commanding it to empty its Documents (or Workbooks etc) Collection.

I will clarify that I have PERSONALLY SEEN that a copy of Word persisted in my task list if I didn't remember to explicitly close what I opened.

Yes, Close what you Open before dereferencing it or it will persist. I see it happen regularly when code breaks during development and I have to kill it in Task Manager. However I am not convinced that setting the pointer to Nothing will destroy the external object itself. Even exiting the code project entirely doesn't destroy the Office app instance because, one created, it has a life of its own.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:52
Joined
Feb 28, 2001
Messages
26,946
You see going out of scope as as a weakness?

Weakness, no. A design option for a specific case? Yes. A design option for ALL possible cases? No.

I had a situation in which my multiple forms had to use the same Word App Object code many times because all of the relevant forms used that like the UNIX "MAN" page. About 30 forms were involved for this one. So did each of them have duplicate code? No more would I use duplicate code than I would denormalize a table. So that meant that the General Module routines needed to be passed an object pointer to do essentially the same task from 30 different places with about 150 possible bookmarks, for a context-sensitive HELP function.

In that context, the object variable was external to the code that used it and did not necessarily go out of scope until the form closed. But for four work-horse forms that in combination did about 90% of the functionality of the database, that often meant that the form would stay open for hours. It also meant that careless or neglectful approaches would clog memory. Which is why I adopted the "Everything I Needed to Know I Learned in Kindergarten" (EINTKILIK) rules: If you open it, close it. If you turn it on, turn it off. If you take it out, put it away.

The incident that made me aware that I had somehow missed the EINTKILIK rules was when one of my users showed me six copies of Word open, NONE of which were opened manually by anything he was doing at the time. Yes, it was my bad, and yes, I found and fixed that problem by applying the EINTKILIK rules. Call them guidelines if you prefer, wouldn't bother me any. But I know I needed to honor those rules more closely than I had previously.

Just as we learn to avoid relational integrity problems and unhandled trap conditions, we learn other tricks of the trade to prevent issues from becoming overwhelming - or from surreptitiously sand-bagging you when your back was turned. I'm actually a proponent of .Close, .Quit, and explicit Set x = Nothing. I was just pointing out that there were pitfalls if you didn't abide by these rules in some cases.
 

Acceesbility

Registered User.
Local time
Yesterday, 22:52
Joined
Jan 4, 2017
Messages
32
The_Doc_Man Wrote:

Just as we learn to avoid relational integrity problems and unhandled trap conditions, we learn other tricks of the trade to prevent issues from becoming overwhelming.
I was just pointing out that there were pitfalls if you didn't abide by these rules in some cases.
So to the Newbie, what would your essential recommended reading be that best explains the rules ?

:)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:52
Joined
Feb 28, 2001
Messages
26,946
Oh, that such a book exists to be recommended! I'm retired with over 30 years of database experience in at least four different database tools, Access being most recent. As theory expands with new discoveries, new mathematical proofs of correctness (and, sadly, of incorrectness), and as new tools are built based on "what has gone before" - any book I could identify is probably already out of date. (I know for a fact that most security books have that problem - the security world changes faster that a book publishing cycle.)

Keep reading articles and asking questions. Question stuff that doesn't seem to make sense. See if there is a way to sanely test something you just saw. Make good backups just in case the test goes off in a random compass direction and takes things down the rabbit hole when it goes.

That last paragraph is not terrible advice for newbies, journeymen, professionals, wizards, and gurus alike.
 

Acceesbility

Registered User.
Local time
Yesterday, 22:52
Joined
Jan 4, 2017
Messages
32
Doc_Man Wrote:
Oh, that such a book exists to be recommended!
Ouch !:rolleyes:
I never implied there was One book!

I was merely seeking an up to date list of what you might recommend covers the essential topics you mentioned, well.

Just as we learn to avoid relational integrity problems and unhandled trap conditions, we learn other tricks of the trade to prevent issues from becoming overwhelming.
Building Microsoft® Access Applications by John Viescas.
Would you consider this book out of date?

On page 27 he doesn't sound keen on AutoNumber Primary Keys.:eek:

Stephen hawking spend his life looking for Black Holes and all along he just needed to open Microsoft Access !

I though this time round I might stand a chance of grasping the fundamentals of Access but I'm beginning to remember it's just a gateway to hell.
:banghead:
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 16:52
Joined
Jan 20, 2009
Messages
12,847
I believe that what happens is that setting the external app object to NOTHING will have the effect of a Close but its default closure behavior might not be what you wanted.

Why would you believe that? Because someone at Microsoft said it? Clearly you have never performed the trivial test required to confirm the reality. Run the following code. It will leave a hidden instance WinWord.exe running until it is killed.

Code:
Dim WordApp As Object
 Set WordApp = CreateObject("Word.Application")
Set WordApp = Nothing

The following code does not leave Word running, despite the object not being Set to Nothing.
Code:
Dim WordApp As Object
 Set WordApp = CreateObject("Word.Application")
WordApp.Quit

Quit is absolutely essential. Set to Nothing simply destroys the pointer to the Word session.

I will clarify that I have PERSONALLY SEEN that a copy of Word persisted in my task list if I didn't remember to explicitly close what I opened. I had to either (a) use task manager to kill it or (b) log out/shut down the computer.

I am not surprised, especially since you believe the following hogwash.

By the way, if you explicitly close the files opened by your app object, you really DON'T have to QUIT the app - you can just dereference it.

The incident that made me aware that I had somehow missed the EINTKILIK rules was when one of my users showed me six copies of Word open, NONE of which were opened manually by anything he was doing at the time. Yes, it was my bad, and yes, I found and fixed that problem by applying the EINTKILIK rules. Call them guidelines if you prefer, wouldn't bother me any. But I know I needed to honor those rules more closely than I had previously.

So you applied EINTKILIK, rushed in and added Close, Quit and Set To Nothing to everything. Because the problem disappeared you presumed that Set to Nothing was important and you assert that here. In fact the only absolutely essential one is Quit.

Yes, if you don't Close the file then you may lose data but it will "run down" if you close the application. You have that backwards in your discussion of "run down". If the application quits the files get released. Applications can sit around indefinitely with no work file loaded.

Keep reading articles and asking questions. Question stuff that doesn't seem to make sense. See if there is a way to sanely test something you just saw.

That last paragraph is not terrible advice for newbies, journeymen, professionals, wizards, and gurus alike.

That is exactly what ChrisO did when he looped millions of times through setting an object variable then let it go out of scope without setting to Nothing. Unfortunately you have not followed you own advice.

Untested assertions are exactly what ChrisO was objecting to in his last post on this thread.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:52
Joined
Feb 28, 2001
Messages
26,946
Galaxiom, I respect you highly and will not turn this into an argument such as I once had with ChrisO. All I can say is that I'm actually a fan of doing the .Close, .Quit, and Set x = Nothing operations as part of the "clean up what you messed up" philosophy.

Did you understand MY point about when you have shared code, it is possible for the variables feeding that code to stay in scope A LOT longer than if the operation was completely contained within an event routine?

I actually do not attempt to test program boundaries these days because my #2 grandson is busy testing HIS boundaries. Keeps me busier than a one-handed paper-hanger trying to prevent him from whacking a game so badly that it becomes unplayable. He's done it twice so far, but I've been lucky enough to fix it without having to remove and re-install.

However, I will try to devise an experiment that will be repeatable with proper monitoring including resource levels. Don't forget that before I got into computing, I was both a theoretical and laboratory chemist, so I actually DO understand how to set up experiments.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:52
Joined
Feb 28, 2001
Messages
26,946
Accessbility

On page 27 he doesn't sound keen on AutoNumber Primary Keys

I remember getting into a lengthy discussion about that a few years ago. The summary of arguments was that where a natural candidate key existed, it was preferable. However, if no viable candidates were found and yet a unique key was required, the use of a synthesized key (Autonumber Longword) was not so terrible as to cause immediate condemnation to the 8th layer of Hell.

One issue that would make a candidate non-viable was that if the candidate was capable of being altered, the maintenance of the associated FKs would become difficult. Another was that the PK, though unique and immutable, was so long that having that long string in every FK was not very efficient (because you want to squeeze as many keys as possible into a fixed-size buffer when searching for a record by PK).

Understand that sometimes it gets like a religious war. Me, I'm a heretic. Use an autonumber if it makes sense. Just don't expect the autonumber that you generate would have any meaning - or any semblance of monotonic behavior. THAT is one of the biggest arguments against using autonumbers. If you think an autonumber has any meaning other than as a PK for a table, ... any meaning at all..., then you have done something dreadfully wrong.
 

Acceesbility

Registered User.
Local time
Yesterday, 22:52
Joined
Jan 4, 2017
Messages
32
Doc_Man Wrote:

Just don't expect the autonumber that you generate would have any meaning
I always just thought of an automatically generated Incremental or Random AutoNumber as a unique Static Reference for each record, nothing else.

:)
 

Users who are viewing this thread

Top Bottom