boblarson
Smeghead
- Local time
- Today, 05:24
- Joined
- Jan 12, 2001
- Messages
- 32,059
If you need that type of accuracy, I'm guessing using Access is the wrong thing to be using to begin with.
Dirty Rotten Forms (1 Viewer)
- Thread starter Thales750
- Start date
I like Access, but I am a dinosaur, I understand that.
As we grow our business we will hire more young people and I will probably only use Access for table modeling, and form and query demos.
The actual programmers will do whatever is the latest, and greatest, I suppose. But I will always love my first real programming media.
We have a point of sale product we sell, that uses a MySQL backend to store billions of records and Access for the local systems because it will continue to work even if the connection to the web is missing.
Funny though, when the net is down all they can take is cash, and who has that now days?
nanscombe
Registered User.
- Local time
- Today, 13:24
- Joined
- Nov 12, 2011
- Messages
- 1,082
Globally unique identifier, Access' Replication ID or MySql UUID.
The penalty you pay is that it takes up 128 bits.
From Wiki:
From MySql:
Dinosaurs like me. The only time my plastic gets used outside home is the cash point.
The penalty you pay is that it takes up 128 bits.
From Wiki:
From MySql:
Dinosaurs like me. The only time my plastic gets used outside home is the cash point.
Last edited:
- Local time
- Today, 08:24
- Joined
- Feb 19, 2002
- Messages
- 43,263
Sorry, I don't have a clue what problem you are trying to avoid. Are you saying that you get errors with bound forms?
- Local time
- Today, 07:24
- Joined
- Feb 28, 2001
- Messages
- 27,172
I have both bound and unbound forms. Each have their uses.
Our problem: We have over 1000 servers (it's a hosting center) and we get 20-30 security bulletins per month. But not all of them apply equally to all of the servers. We have automated a bunch of the decisions, but we still have to give the administrators a way to record the work they do and the result of their research.
Using unbound forms, I let them do complex bulk updates in a two-dimensional matrix. For instance, the admins might be selecting all of the UNIX-based database servers and noting that none of them run Websphere or Wireshark. So with two multi-select combo boxes, the admins select a bunch of servers and a bunch of security notices, then select status "Server does not run this software." Then [COMMIT] and our reporting is done for literally a few thousand responses spread over 60 projects - wholesale updates. Or perhaps they selected the status "Waiting for scheduled downtime" and will later update servers after each one is patched, with different selection methods but the same concept. There, an unbound form was the only way to go.
For the bound forms, we are either adding new users, new servers, new security bulletins, or new projects piecemeal. There, the one-to-one form display of an underlying single entity is unmatched for ease of use and direct applicability to our situation.
The points that make the above relevant to this discussion?
1. Both form types make sense in a real-world application. Each is a valuable tool in your toolkit. Don't leave behind a wrench and only take the hammer. Otherwise, you had better hope that all you see is nails and not nuts and bolts.
2. It is possible to make things not quite bullet-proof but pretty darned so, using SQL-based queries (DoCmd.RunSQL things) on a shared Access backend - as long as you are careful. Or use DAO where it makes sense.
The concern is making something robust. What I did to make our apps work was to first build a prototype form with lots of common features and all of the event code already partly instantiated. The OnOpen, OnLoad, OnCurrent, OnActivate, etc. - DEFINITELY including the BeforeUpdate event and OnClose events. Also included pre-built command buttons like COMMIT, CANCEL, HELP, File Trouble Report, CREATE, REMOVE, etc.
We trap attempts to close the form without saving. We trap attempts to save a record that is incomplete. We trap attempts to save data in an impossible format. All of the hooks for that are pre-built into the prototype forms for bound or unbound application elements.
The point there is to do the work up front to build the infrastructure so that instead of having to do this over and over again for every form for every business entity you will ever touch, you CLONE the form and fill in the blank spaces with the specifics. If 60% of your tedium in getting a complex set of forms built up is because of the repetitious event code it takes to armor-plate your application, do the really nasty work up-front in the abstracted prototypes. Then go back and customize. Saving 60% of your work for every new form adds up - and yet you can get a good product. Oh, by the way, it doesn't hurt that using cloned forms gives your product a consistent appearance, look-and-feel, or however you want to say that.
As to the autonumber discussion:
I've always been in favor of autonumbering things, but not blindly so. You don't ALWAYS need a PK in table A unless a child is going to claim a record in table A as its parent. There, autonumbers make sense unless there is a natural PK somewhere to be found. Natural keys are preferable to synthetic keys, in my opinion, but there are those who disagree.
But there should be no confusion about autonumbering. If you are using an autonumber system, the numbers will be unique but not predictable, even if you use the autoincrementing rather than the random form of autonumbers.
If you wanted contiguous numbers, you didn't want an autonumber in the first place. Contiguity is never a property of the autonumber set in Access. It is OK to want to know the value of an autonumber after the fact - but not before the fact. Most of the respondents here know that, but the casual readers of the thread might need to be reminded.
Our problem: We have over 1000 servers (it's a hosting center) and we get 20-30 security bulletins per month. But not all of them apply equally to all of the servers. We have automated a bunch of the decisions, but we still have to give the administrators a way to record the work they do and the result of their research.
Using unbound forms, I let them do complex bulk updates in a two-dimensional matrix. For instance, the admins might be selecting all of the UNIX-based database servers and noting that none of them run Websphere or Wireshark. So with two multi-select combo boxes, the admins select a bunch of servers and a bunch of security notices, then select status "Server does not run this software." Then [COMMIT] and our reporting is done for literally a few thousand responses spread over 60 projects - wholesale updates. Or perhaps they selected the status "Waiting for scheduled downtime" and will later update servers after each one is patched, with different selection methods but the same concept. There, an unbound form was the only way to go.
For the bound forms, we are either adding new users, new servers, new security bulletins, or new projects piecemeal. There, the one-to-one form display of an underlying single entity is unmatched for ease of use and direct applicability to our situation.
The points that make the above relevant to this discussion?
1. Both form types make sense in a real-world application. Each is a valuable tool in your toolkit. Don't leave behind a wrench and only take the hammer. Otherwise, you had better hope that all you see is nails and not nuts and bolts.
2. It is possible to make things not quite bullet-proof but pretty darned so, using SQL-based queries (DoCmd.RunSQL things) on a shared Access backend - as long as you are careful. Or use DAO where it makes sense.
The concern is making something robust. What I did to make our apps work was to first build a prototype form with lots of common features and all of the event code already partly instantiated. The OnOpen, OnLoad, OnCurrent, OnActivate, etc. - DEFINITELY including the BeforeUpdate event and OnClose events. Also included pre-built command buttons like COMMIT, CANCEL, HELP, File Trouble Report, CREATE, REMOVE, etc.
We trap attempts to close the form without saving. We trap attempts to save a record that is incomplete. We trap attempts to save data in an impossible format. All of the hooks for that are pre-built into the prototype forms for bound or unbound application elements.
The point there is to do the work up front to build the infrastructure so that instead of having to do this over and over again for every form for every business entity you will ever touch, you CLONE the form and fill in the blank spaces with the specifics. If 60% of your tedium in getting a complex set of forms built up is because of the repetitious event code it takes to armor-plate your application, do the really nasty work up-front in the abstracted prototypes. Then go back and customize. Saving 60% of your work for every new form adds up - and yet you can get a good product. Oh, by the way, it doesn't hurt that using cloned forms gives your product a consistent appearance, look-and-feel, or however you want to say that.
As to the autonumber discussion:
I've always been in favor of autonumbering things, but not blindly so. You don't ALWAYS need a PK in table A unless a child is going to claim a record in table A as its parent. There, autonumbers make sense unless there is a natural PK somewhere to be found. Natural keys are preferable to synthetic keys, in my opinion, but there are those who disagree.
But there should be no confusion about autonumbering. If you are using an autonumber system, the numbers will be unique but not predictable, even if you use the autoincrementing rather than the random form of autonumbers.
If you wanted contiguous numbers, you didn't want an autonumber in the first place. Contiguity is never a property of the autonumber set in Access. It is OK to want to know the value of an autonumber after the fact - but not before the fact. Most of the respondents here know that, but the casual readers of the thread might need to be reminded.
That’s the key Doc, A single entity (meaning table) add record. We almost never have that any more.
If we add a contact, it will be:
Linked to a link table, linked to phone numbers,
Linked to a link table, linked to email address,
Linked to a link table, linked to a company,
Linked to a link table, linked to a vender, or customer.
And on, and on. We have almost entirely eliminated al one to many link in favor of many to many. In addition to the many to many relationship we are also tracking multi-location, multi computer , and multiple proprietors (user employed by different companies using the system) In the beginning this was really hard to deal with because it take a lot more management because Access is designed to work most efficiently with the traditional one to mane and a single primary and foreign key.
All that, could be done using subforms, but it gets real crowded and confusing to users.
- Local time
- Today, 08:24
- Joined
- Jan 23, 2006
- Messages
- 15,379
Please show us some of this many to many you have working so well.
I think you may have your own definition of Many to Many.
Also, have you just changed topics here - I was expecting to hear more about Autonumbers.
For anyone who cares, here is a good write up on Autonumbers. Many properties and comments as to what they are and what they are Not. (Part of which I believe is what Bob and Pat were saying).
http://www.utteraccess.com/wiki/index.php/Autonumbers
I think you may have your own definition of Many to Many.
Also, have you just changed topics here - I was expecting to hear more about Autonumbers.
For anyone who cares, here is a good write up on Autonumbers. Many properties and comments as to what they are and what they are Not. (Part of which I believe is what Bob and Pat were saying).
http://www.utteraccess.com/wiki/index.php/Autonumbers
nanscombe
Registered User.
- Local time
- Today, 13:24
- Joined
- Nov 12, 2011
- Messages
- 1,082
A single "entity" form for adding / amending data is fine, it's certainly good enough for web based applications where there is little choice.
Surely all it means is that the code behind the form will be doing the work of bringing together, and splitting, all of the data in the myriad of data and link tables behind the scenes. Another advantage of this would be if you needed some form of table level encryption as you could encrypt / decrypt your data on the fly.
Autonumbers (sequentially generated numbers) are fine, if you are working with a single central backend, but as soon as you have multiple databases in various locations which need to interchange data seamlessly it gets a bit hairy. At that point I switched to generating my own location aware sequence numbers.
Swapping / consolidating datasources from in excess of 30 locations, even on a less frequent basis, can be interesting.
Surely all it means is that the code behind the form will be doing the work of bringing together, and splitting, all of the data in the myriad of data and link tables behind the scenes. Another advantage of this would be if you needed some form of table level encryption as you could encrypt / decrypt your data on the fly.
Autonumbers (sequentially generated numbers) are fine, if you are working with a single central backend, but as soon as you have multiple databases in various locations which need to interchange data seamlessly it gets a bit hairy. At that point I switched to generating my own location aware sequence numbers.
Swapping / consolidating datasources from in excess of 30 locations, even on a less frequent basis, can be interesting.
Last edited:
- Local time
- Today, 08:24
- Joined
- Jan 23, 2006
- Messages
- 15,379
nanscombe
Registered User.
- Local time
- Today, 13:24
- Joined
- Nov 12, 2011
- Messages
- 1,082
Really what?
Isn't that just the link you post earlier? (The first one at least).
So you can start autonumbering at a given value? They've caught up then.
Nope, misread that. An iPhone on a moving bus isn't the greatest reading platform.
As I said I generated my own IDs.
Isn't that just the link you post earlier? (The first one at least).
So you can start autonumbering at a given value? They've caught up then.
Nope, misread that.
An iPhone on a moving bus isn't the greatest reading platform.As I said I generated my own IDs.
Last edited:
- Local time
- Today, 08:24
- Joined
- Jan 23, 2006
- Messages
- 15,379
You really believe autonumbers are sequential?
I provided 2 links, 1 is a repeat to ensure you saw it and the second was an Allen Browne site showing some of the non-sequential issues and corrections.
Did you read the UtterAccess info? Perhaps you meant a sequential numbering scheme in general, but on an ACCESS related forum - autonumber - has specific meaning.
I provided 2 links, 1 is a repeat to ensure you saw it and the second was an Allen Browne site showing some of the non-sequential issues and corrections.
Did you read the UtterAccess info? Perhaps you meant a sequential numbering scheme in general, but on an ACCESS related forum - autonumber - has specific meaning.
nanscombe
Registered User.
- Local time
- Today, 13:24
- Joined
- Nov 12, 2011
- Messages
- 1,082
I happened to mention Autonumber (sequential number) or whatever. For simplicity's sake I made no mention of; gaps for deleted records, gaps because of imported data from other sources, gaps because of Microsoft bugs (sorry features). Neither did I mention the use of random values or GUIDs.
For a lot of people an Autonumber is simply an automatically incrementing counter.
It was not meant as a detailed technical description of an in-built numbering scheme but merely as a generalisation.
For a lot of people an Autonumber is simply an automatically incrementing counter.
It was not meant as a detailed technical description of an in-built numbering scheme but merely as a generalisation.
Last edited:
nanscombe
Registered User.
- Local time
- Today, 13:24
- Joined
- Nov 12, 2011
- Messages
- 1,082
Last edited:
- Local time
- Today, 07:24
- Joined
- Feb 28, 2001
- Messages
- 27,172
I recall a long discussion that might still be in the archives of this forum on the topic of synthetic vs. natural vs. non-existent keys and when each was appropriate. Basically, all three are correct to use at different times. Folks get dependent on keys until they start wasting space on keys they never really use. They forget the cost to the system of maintaining the indexes of a set of keys over a network link.
There have also been discussions regarding complex-format keys, which can never be autonumbered, because an autonumber key as defined by Access has to be a stand-alone field. Things can depend on it, but it ONLY depends on something in the TableDef structure for the table that contains it. Yet every so often, a new person brings up that same question. In a very large sense, I blame Microsoft for paring down the help text on certain important structural concepts in order to save space at the expense of clarity.
As to a statement "we never see a single-table operation any more" - you might not see it on the public pages, but it is there. And Thales, you confuse the issue in your comments:
A lot of that can still be a single-table add/change/remove form with a lot of combo boxes for the things linked by FKs. The underlying form will be BOUND to a single table. The FKs will be linked only as the result of a .RowSource type of reference. They aren't bound in sub-forms.
Obviously, when a person represents (acts as an agent for) more than one company, that requires a junction table. But even so, that just means that you had single-table entries for each company since BOTH elements in the junction table have to exist somewhere as single entities. It might be obscured by many layers, but there MUST be some "pure" entities in your database somewhere. The contact is, in fact, one such pure entity. The links are impure but the contact isn't.
There have also been discussions regarding complex-format keys, which can never be autonumbered, because an autonumber key as defined by Access has to be a stand-alone field. Things can depend on it, but it ONLY depends on something in the TableDef structure for the table that contains it. Yet every so often, a new person brings up that same question. In a very large sense, I blame Microsoft for paring down the help text on certain important structural concepts in order to save space at the expense of clarity.
As to a statement "we never see a single-table operation any more" - you might not see it on the public pages, but it is there. And Thales, you confuse the issue in your comments:
A lot of that can still be a single-table add/change/remove form with a lot of combo boxes for the things linked by FKs. The underlying form will be BOUND to a single table. The FKs will be linked only as the result of a .RowSource type of reference. They aren't bound in sub-forms.
Obviously, when a person represents (acts as an agent for) more than one company, that requires a junction table. But even so, that just means that you had single-table entries for each company since BOTH elements in the junction table have to exist somewhere as single entities. It might be obscured by many layers, but there MUST be some "pure" entities in your database somewhere. The contact is, in fact, one such pure entity. The links are impure but the contact isn't.
It started out as a philisophical discussion about unbound forms. and moved to Autonumbers.
Doc I use a lot of one to many relationships, just not on core data.
Core data is data that will be used over and over to manage contacts, companies, including vendors and consultants etc.
Transactional data is sales orders, purchase orders, and proposals, etc.
In this thread I was thinking of Core Data when I started it.
Rules that I follow now
Never repeat core data.
Never delete any records
Only non-changing data in core tables.
If Core data must change for some rare reason the system must keep a history.
No null fields in core data.
To that end:
tblContact
ContactID AutoNumber
ContactsFirstName Text
CopntactLastName Text
ContactDateOfBirth Text
ContactLogDate Date/Time
SocialSecurity Text
No other fields.
tblContactPhoneNumbers
ContactPhoneNumbersID AutoNumber
ContactPhoneNumber Text
ContactPhoneNumbersLogDate Date/Time
tblLinkContactPhoneNumber
ContactPhoneNumbersID AutoNumber
ContactID Number
ContactPhoneNumbersID Number
ContactPhoneNumbersLog Date/Time
ContactPhoneNumbersStatus Number/Lookup
The status of the phone number is:
Primary: There can only be one per contact.
Active: Still used but not primary
Archive: Not Used
This allows contacts to share phone numbers, and never repeat a phone number in the system.
Many to many.
These are the types of arrangements that moved me away from bound forms on data entry of core data.
With DAO I can create any link I choose and never worry about getting the keys aligned, including, AutoNumber, composite, or some kind of GUID.
DAOs ability to return the value of current fields is paramount in keeping multiple tables synchronized.
Core data is data that will be used over and over to manage contacts, companies, including vendors and consultants etc.
Transactional data is sales orders, purchase orders, and proposals, etc.
In this thread I was thinking of Core Data when I started it.
Rules that I follow now
Never repeat core data.
Never delete any records
Only non-changing data in core tables.
If Core data must change for some rare reason the system must keep a history.
No null fields in core data.
To that end:
tblContact
ContactID AutoNumber
ContactsFirstName Text
CopntactLastName Text
ContactDateOfBirth Text
ContactLogDate Date/Time
SocialSecurity Text
No other fields.
tblContactPhoneNumbers
ContactPhoneNumbersID AutoNumber
ContactPhoneNumber Text
ContactPhoneNumbersLogDate Date/Time
tblLinkContactPhoneNumber
ContactPhoneNumbersID AutoNumber
ContactID Number
ContactPhoneNumbersID Number
ContactPhoneNumbersLog Date/Time
ContactPhoneNumbersStatus Number/Lookup
The status of the phone number is:
Primary: There can only be one per contact.
Active: Still used but not primary
Archive: Not Used
This allows contacts to share phone numbers, and never repeat a phone number in the system.
Many to many.
These are the types of arrangements that moved me away from bound forms on data entry of core data.
With DAO I can create any link I choose and never worry about getting the keys aligned, including, AutoNumber, composite, or some kind of GUID.
DAOs ability to return the value of current fields is paramount in keeping multiple tables synchronized.
Last edited:
