By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,599 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

Back to Basics

100+
P: 675
I seem to program myself into too many corners, so I'm trying to understand a few basic ideas. I'm not asking for exact code, but for direction and Access logic.

For a test DB, I created two tables and defined relationships
tAAA:
Expand|Select|Wrap|Line Numbers
  1. Key Autonumber (PK)
  2.   1
  3.   2
tNames:
Expand|Select|Wrap|Line Numbers
  1. Key AutoNumber (PK)  FK Long (Foreign Key)  Name Text
  2.   1                     1                   Elizabeth
  3.   2                     2                   OldBirdman
  4.   3                     1                   Liz
  5.   4                     1                   Beth
  6.  
I have a form, fAAA with a textbox txtKey and a combobox cboNames
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. cboNames.RowSource = "SELECT tNames.Key, tNames.Name FROM tNames " & _
  3.         "WHERE (((tNames.FK)=" & txtKey & ")) " & _
  4.         "ORDER BY tNames.Name;"
  5. End Sub 'Form_Current
  6.  
  7. Private Sub cboNames_DblClick(Cancel As Integer)
  8. DoCmd.OpenForm "fEditNames", acNormal, , _
  9.          "FK=" & txtKey, acFormEdit, acWindowNormal, txtKey
  10. End Sub 'cboNames_DblClick
  11.  
Form named fEditNames is Continuous. It shows all names for the Key current when opened. But I can't add a new record to tAAA and enter a name in tNames at the same time. I can add a new record in fEditNames if it is for an existing record in tAAA. I have to have a name, or the entry in tAAA is invalid.

The form fEditNames has the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. RecordSource = "SELECT * FROM tNames WHERE FK=" & 2
  3. txtFK.DefaultValue = OpenArgs
  4. End Sub 'Form_Load
My user (or me) gets to this form. Either a name is entered, or eventually the operation is cancelled. If a name is entered, it must meet the criteria for related tables, but I won't know the new key in tAAA until later. If it is cancelled, the pending(new) entry for tAAA must be cancelled also, so I can't save before calling tEditNames.
How should this be done?
Jul 27 '09 #1
Share this Question
Share on Google+
17 Replies


ADezii
Expert 5K+
P: 8,680
@OldBirdman
Hello OldBirdman, the scenario, which you describe, is the enforcing of Referential Integrity between a Parent and Child Table so there will be no chance that 'Orphaned' Records will be created. The simple Chain of Events to enforce RI is:
  1. ADD a Record to the Parent Table.
  2. SAVE the previously added Record in the Parent Table.
  3. Now, and only now, can you ADD/SAVE a Record to the Child Table with a Foreign Key Field Value equal to the Primary Key Value of the Parent Table.
  4. Does this make sense to you?
Jul 28 '09 #2

100+
P: 675
Yes, it makes sense, but it is going to make things difficult. In my actual program, I will be creating both pieces by picking apart a webpage copy/paste to the program. Until everything is in place, I can't ask the user if he wants to abort the creation of the new parent AND child.
You're saying I have to save the parent, and then determine if I even got the child. Maybe I didn't, or what I got is a duplicate, or too much is invalid, or information is incomplete, either with the parent or the child.
This says I should reserve Key=1 as an existing parent. I could:
1) Create my new parent in this record
2) Create a child, linked to Key=1
3) If Abort (don't keep anything), I cancel the child. The parent is there for re-use.
4) If not Abort, I move the new data to a new record. This is not an easy task, involving loops thru all the fields of the record.
5) Save the newnew record
6) Change the foreign key (FK) of the child to the newnew record.

--OR--

1) Create my new parent
2) Save parent
3) Create the child
4) If Abort, UnDo child and delete parent - may cause holes in sequential keys
5) If not Abort, I'm done.

This all seems so complex for what seems like it would be very common. I call a hotel, and try to make a reservation. They get my name, and ask dates. I say Saturday, and they say OK, and I say also the following Monday (but not Sunday), and they can't do. I say "Thank You!" and hang up. They've created a parent, and one child, both to be deleted, or undone? I guess.

So - what, if any, are the penalties for saving a record and then deleting it, as compared to cancelling the save, or an UnDo?
Jul 28 '09 #3

Delerna
Expert 100+
P: 1,134
Thats why good database design is so important.

But in your hotel example no record would have been created.
You would (possibly) have
a table storing rooms
and a table storing a calendar
and a table storing a guest and linking them to a room and the calendar

During the room reservation process you are only creating records
in the third table and access only creates that record when you navigate away from it in the reservation making form.
Halfway through the process you cancel the reservation.
The database operator cancels the record and no record ever gets created.

Now your actual scenario may be different to the hotel scenario.
In that case you need to design your way around such difficulties.
It would be better to provide a description of your actual scenario
like you did for the hotel and we can try and help you.

PS
Whoever said database design wasn't a complex process? :)
Jul 28 '09 #4

ADezii
Expert 5K+
P: 8,680
I agree with basically everything that Delerna has said. Trying to maintain Referential Integrity with your current logic would be difficult at best and not very efficient. I think that is time to go back to the drawing Board and rethinking your logic on this matter.
Jul 28 '09 #5

100+
P: 675
My actual application is a Movie database, to locate movies by store section. Users are customers to video rental store.
I started with a simple table, tMovies:
Expand|Select|Wrap|Line Numbers
  1. Key PK Autonum
  2. Title     Text
  3. Genre   Number
  4. Starring  Text
  5. .....
and so forth. I then realized that I had to add alternate titles, as many movies either have two or more titles, or they are compound, like "National Geographic: Tigers", and "Tigers" might be considered a title.
For queries, I was doing a multi-step query with the first step a Union query involving table tAltTitles and tMovies.
So the example presented is really the new version of this program, where tNames is really tTitles, and tMovies no longer has a title field.
This simplifies filter queries, and the program. Expanding on the related tables, Genre can have multiple values, as can Starring, Date Released (with special editions, director's cut, etc.).
So I have the primary record with almost no data, and all my data in related sub-tables.
An employee goes to add a new movie to the database, and creates a record in table tMain. When they enter the title (primary record is saved in order to create related records), they realize that the new movie is a version of one in the database. They press 'Cancel' and I delete the primary record, which will delete any records in related tables. Employee now locates existing record, and modifies tVersion. That is a real scenario. Same thing might apply to an AddressBook. Primary record has no fields except key. Related tables are tName (Nickname, Given Name), Phone#(Home, Business, Cell), EMail(InternetProvider, Yahoo.com, Hotmail,com, GMail.com) and other tables to store multi-value entries.
I appreciate that good database design is important. We all learn a limited set of things we can do (with Access), and then gradually expand our expertise. Eventually the gaps in our learning become apparent, and we step back and reorganize. Hence the title "Back to Basics".
Jul 29 '09 #6

Delerna
Expert 100+
P: 1,134
I appreciate that good database design is important. We all learn a limited set of things we can do (with Access), and then gradually expand our expertise. Eventually the gaps in our learning become apparent, and we step back and reorganize.
OldBirdman, I hope that my post did not cause offence.
I agree with your appraisal of the learning process wholeheartedly.
It is how I myself have come to learn software development, including access, with very little "official" schooling in the subject.
It is, indeed, how I will continue to learn for the remainder of my days.

There was no intent on my part to suggest that that is not a valid way of learning.
Kind regards
Aug 4 '09 #7

P: 74
How about an unbound form, with unbound fileds, that run an append query only after an abort cant be done (accpeting the record)? Then you can append both tables (in the proper order, of course).
Aug 4 '09 #8

Delerna
Expert 100+
P: 1,134
Or some intermediate tables that store the records as the movies are being entered. A confirmation button then runs some vba code that appends the intermediate records to the real tables and deletes the intermediate records.

Using intermediate tables would more easily allow the form to handle multiple child records against a parent record than an unbound form would allow.

These a just 2 examples, I am sure if you think about it many more solutions will come to mind. You are in the best position to make the choice. ;)
Aug 4 '09 #9

100+
P: 675
I have come up with a database design 'template' here that seems to be simple logically, but apparently cannot be easily implemented.
Mentally reviewing several projects, they all have a similar pattern, and that is that the "Primary Record" has little or no data. Examples would be:
Expand|Select|Wrap|Line Numbers
  1. Table=Species
  2. Key=Autonum
  3.  
  4.   Table=Name
  5.   Key=Autonum
  6.   FK=Long (ForeignKey)
  7.   Name=Text
  8.   Type=Numeric '1=Latin; 2=English; 3=....
  9.  
  10.   Table=Country
  11.   Key=Autonum
  12.   FK=Long (ForeignKey)
  13.   Country=Numeric 'Key to Table=Countries
--- or ---
Expand|Select|Wrap|Line Numbers
  1. Table=Movies
  2. Key=Autonum
  3.  
  4.   Table=Title
  5.   Key=Autonum
  6.   FK=Long (ForeignKey)
  7.   Name=Text
  8.   Type=Numeric '1=Name on Box; 2=Partial Name; 3=....
  9.  
  10.   Table=Starring
  11.   Key=Autonum
  12.   FK=Long (ForeignKey)
  13.   Star=Numeric 'Key to Table=Actors
  14.  
  15.   Table=Edition
  16.   Key=Autonum
  17.   FK=Long (ForeignKey)
  18.   Version=Numeric '1=Original Theatre Version; 2=Director's Cut; 3=Re-release...
I'm going to use a familiar one, AddressBook

Expand|Select|Wrap|Line Numbers
  1. Table=Contact
  2. Key=Autonum
  3.  
  4.   Table=Names
  5.   Key=Autonum
  6.   FK=Long (ForeignKey)
  7.   Name=Text
  8.   Type=Numeric '1=Legal Name; 2=Nickname; 3=Prior Name (Marriage); 4=...
  9.  
  10.   Table=Phone
  11.   Key=Autonum
  12.   FK=Long (ForeignKey)
  13.   PhoneNo=Text 'Phone number without AreaCode or Int'l Code
  14.   PhoneArea=Text 'Area Code
  15.   PhoneIntl=Text 'International prefix
  16.   Type=Numeric '1=Land Line; 2=Cell; 3=Satellite; 4=...
  17.  
  18.   Table=EMail
  19.   Key=Autonum
  20.   FK=Long (ForeignKey)
  21.   Name=Text
For the example AddressBook, I either have a relational database with Contact as the primary table and many (3 shown) child tables. Another way to look at it is that I have many tables with many-to-many relationships, with Table=Contact acting as the bridge table.

Trying to maintain Referential Integrity with your current logic would be difficult at best and not very efficient. I think that is time to go back to the drawing Board and rethinking your logic on this matter.
I'm sitting at my drawing board. I'm thinking.
ADD a Record to the Parent Table.
SAVE the previously added Record in the Parent Table.
Now, and only now, can you ADD/SAVE a Record to the Child Table with a Foreign Key Field Value equal to the Primary Key Value of the Parent Table.
I need at minimum a record in the table=Names. The other tables do not need entries, but user might want to do these first. Actually, I would need one name designated as the Type=1, so this would be displayed by default.
How about an unbound form, with unbound fileds, that run an append query only after an abort cant be done (accpeting the record)? Then you can append both tables (in the proper order, of course).
I can't seem to figure out how to do this. As multiple phone numbers might be known at creation time (Business Card contains phone#=Office, Cell, Fax), I need the tables for subform. A possibility here might be to use ComboBoxes and Value Lists to store the intermediate data.
Using intermediate tables would more easily allow the form to handle multiple child records against a parent record than an unbound form would allow.
I am not sure what an intermediate table is, or how to create one.
These a just 2 examples, I am sure if you think about it many more solutions will come to mind. You are in the best position to make the choice. ;)
That is the trouble. I'm not in the position to make the choice, because I really am lacking knowledge in the use of VBA to create and/or manage tables.
OldBirdman, I hope that my post did not cause offence...
Never. I am so grateful that complete strangers will take their time to consider my problems. Even answers that have no apparent relationship to my questions are useful. They may not work, but if my question were mis-interpreted, the answer may contain another way to look at at problem that I had not thought of.
Aug 5 '09 #10

Delerna
Expert 100+
P: 1,134
INTERMEDIATE TABLES
===================
There is no such thing as an "Intermediate table".
What I mean by that term is a set of duplicate tables (duplicates of the real tables)
These duplicate tables only ever store the current record that the user is entering. At the end of the entry process the user either
1) clicks an "update" button that copies the information from the intermediate tables into the actual tables and then deletes them from the intermediate tables ready for the next entry session.

or

2) Clicks a cancel button which just deletes them from the intermediate tables

That way there are no partially created records going into the actual tables that will need to be deleted because someone changed there mind halfway through.

UNBOUND FORM
=============
You are correct, it is difficult to handle one to many relationships with an unbound form. It can be done, however and your thoughts there are valid possibilities for overcomming these difficulties.

The intermediate table idea is a step up from an unbound form and should make handling one to many relationships much easier to handle.
But this too has its own difficulties. It works easily for completely new parent/child records. But how do you handle the case where the parent record and a few children records already exists and you user wants to add more children or delete some of them?

I think every project has its own set of complexities and challenges that are unique to that project. Because of this there is no "correct" way to develop a project. We have a set of rules that we try to follow because on the whole they make sense. However, every rule is made to be broken.

For example there is a rule that states that we shouldn't save calulated fields.
But what if a query needs to perform a complicated calculation over a large number of records. Improved performance resulting from calculating the value once and then saving it for fast retrieval would then override that rule.

Or the normalization rules that we follow. Working with SQL cubes has taught me that these rules are not the "be all and end all" that they are purported to be, especially in these days of cheap disk space. I see them more as guide lines than rules and dramatic impovement in query perfomance has caused me to break them on more than one occasion.

Don't get me wrong, I do see the logic in all the database design rules. I just don't see them all as "absolute", and I wouldn't last long in my current job if I did. In the company I work for, users demand performance and performance is the only absolute.
Aug 5 '09 #11

ADezii
Expert 5K+
P: 8,680
There is no such thing as an "Intermediate table".
Hello Delerna, just some useless information as always. I, as well as others, often refer to an Intermediate Table as a Table Linking what would otherwise be a MANY <==> MANY Relationship between 2 Tables. It typically breaks the MANY <==> MANY Tables into 2 - 1 ==> MANY Tables with this Intermediate Table acting as the go-between on the MANY side.It usually contains a Composite Primary Key based on the Primary Keys of the 2 Tables now on the 1 side. Just some food for thought. A typical representation would be something similar to:
Expand|Select|Wrap|Line Numbers
  1. tblDoctors.[DoctorID]{1} ==> tblDoctorsPatients.[DoctorID]{MANY}
  2. tblPatients.[PatientID]{1} ==> tblDoctorsPatients.[PatientID]{MANY}
The combination of [DoctorID] and [PatientID] in tblDoctorsPatients would comprise the Primary Key, and would, of course, be Unique.
Aug 6 '09 #12

Delerna
Expert 100+
P: 1,134
Thanks Adezii, I have never known what to call that but now I do :)

In that case I probably should clarify what I was trying to say a little further. I mean there are no special tables that you need to create. They are just tables like any other. It is the use to which the tables are being put as a temporary store during the creation of a record before final insertion into the actual tables that I was referring to. Not sure what to call that either now?
Aug 6 '09 #13

ADezii
Expert 5K+
P: 8,680
@Delerna
Not to further confuse you, but this type Table that I am referring to is usually called a 'Linking' not 'Intermediate' Table. Take care.
Aug 6 '09 #14

100+
P: 675
I am leaning toward an unbound form for creating new records. I am more comfortable with forms, controls, and the VBA for control events than for tables created and opened with VBA without using controls.

I want a primary table with only a key(autonum) field. Access will let me create such a table, but I cannot figure out how to add records to this table. Is it possible? How?

I can modify my table by adding a text field, FieldSize = 1. Now I can add records with "INSERT INTO ....". If FieldSize = 0, then apparently FieldSize = 255, but I can live with FieldSize =1. How do I figure out what the key assigned by Access is? Only way I can figure is to open a DAO recordset of all records, sorted by key, DESC, and doing a MoveFirst. Seems like a query that doesn't have to be, but I don't know how to do it otherwise. Any suggestions?
Aug 6 '09 #15

Delerna
Expert 100+
P: 1,134
There is only one field in your primary table and that field is autonumber?

As far as I know, you will not be able to insert records into such a table, you must have other fields that can be inserted and the autonumber field is "automatically" inserted because of them.

Assuming the autonumber field is called [ID]
You can open a DAO (or ADO) recordset based on
"SELECT max(ID) FROM thetable"
But that could be dangerous in a multiuser environment.

I suggest you come up with a better key than an autonumber field.
Try and use fields from your actual data as keys and resort to autonumber only when you can't find a unique key in your data. There usually is if you look hard enough.

I am leaning toward an unbound form for creating new records. I am more comfortable with forms, controls, and the VBA for control events than for tables created and opened with VBA without using controls.
No need to create tables in VBA in the table duplicates option.
They already exist and you bind your forms/subforms and controls to them.
You would only use VBA to copy the data out of those tables into the main tables.
Aug 6 '09 #16

Delerna
Expert 100+
P: 1,134
An afterthought.
The things posted in this thread so far are "ideas".
I am not entirely convinced that either "Unbound forms" or "duplicate tables" is your best option. I don't have control over the development of your project and therefore cannot say what is your best option for your project.

I guess what I am trying to say is that we have highlighted some possibilities for you which may or may not "fit" your project.
Have you mapped out a clear path to follow, from beginning to end on paper (or document files).
Including
Have you mapped out a complete "data model" of all the tables your project will need.
Have you identified possible difficulties and come up with solutions from beginning to end.
I know you have one. The issue of beginning a parent/child entry and being able to opt out half way through.

If you don't do these things then you may find youself going back to basics again in six months time.
Aug 6 '09 #17

100+
P: 675
This whole thread was meant to be 'ideas', and not coded solutions. So I am very happy with the way it is going.

I've been experimenting with the Unbound Form idea, and from testing, it works well if the parent table actually has zero fields, but if there are fields, it gets messy quickly.

Have you mapped out a clear path to follow, from beginning to end on paper (or document files).
Including
Have you mapped out a complete "data model" of all the tables your project will need.
Have you identified possible difficulties and come up with solutions from beginning to end.
The reason I don't want code in this thread, or most of my threads, is that I do plan where I am going, and how to get there. As to the question of identifying all tables, NO! But here I am trying to develop a "Template" to use in at least 3 projects. The problem is currently as presented in post #10.

I am a firm believer in the planning stage of a project. Too many people start writing code immediately. This is obvious by reading the postings to this forum. 10% planning, 40% coding, and 50% debugging is the norm, but I think it should be 50% planning, 25% coding, 15% debugging, and 10% at the party celebrating completion.

My original title "Back to Basics" refers to creating new rows in tables, and removing them if the user aborts. Creating a row is 'Basic', and a 'Cancel' is a normal feature of many commands or operations.

In Post#10, I want to add a new contact to my 'AddressBook'. Logically it would be:
Expand|Select|Wrap|Line Numbers
  1. Step #1 Create a row in Table=Contact
  2. Step #2 Enter 2 rows in child table=Names
  3. Row n = "OldBirdman"
  4. Row n+1 = "Birdman"
  5. Step #3 Enter 3 rows in child table=EMail
  6. Row n = "OldBirdman@Spam.com"
  7. Row n+1 = "OldBirdman@RocketMail.com"
  8. Row n+2 = "OldBirdman@DSLProvider.com"
  9. Step #4a Abort the process
  10. Step #4b Save
  11.    Verify that I have at least 1 name, otherwise abort
  12.    Save all entries
  13.  
This seems so simple, so 'Basic', that I was very surprised that someone didn't just paste a code solution to this. Guess it isn't basic then.
Aug 7 '09 #18

Post your reply

Sign in to post your reply or Sign up for a free account.