Expanding related tables and splitting database

[bcraigie]

Hi all, my first post here. I've been asked to finish off an access database that someone else has part-developed in their spare time.

Apologies if I don't get the terminology correct. I've searched and searched but can't find the right word to call the + symbol (in datasheet view) to the left of a record in a table where it has children. In the image below, I've put arrows on, showing where the plus symbol was and has now become a - sign as it has expanded the children.

It works very nicely to see parent, child and grandchild etc records that are related by keys (defined in the relationships screen). This makes it easy for the data manager to open up related records if someone has put in some bad data that needs corrected.

However, I was advised to split the database so that the forms etc were in a separate file from the data, which I understand is best practice.

After I did that the + symbol is no longer there for users to click on to see related records (but I checked and the relationships are still there).

I wondered if that was just something we have to live with, or if there's a way to get those + symbols back again even when the DB is split.

If it is relevant, this is Office Pro Plus 2016.

If it isn't possible to get the + symbols back after splitting the database, I suppose I could create a form that has parent and child records, but it seems a lot of work (that the customer may not pay for) unless there's a quick way to build such a form?

Thanks for any help or advice.

 

[Gasman]

Users should not be anywhere near tables.
Forms allow for data validation, editing tables for the most part do not.

 

[bcraigie]

Thanks, yes. I'm aware of that. Having already spent months developing their database and far exceeded their budget, I'm not sure how much further it will stretch to developing more forms to let them correct their inevitable data entry errors. I was hoping there was a quick way to re-create this facility with forms (which can then have the validation applied).

 

[GPGeorge]

Hi all, my first post here. I've been asked to finish off an access database that someone else has part-developed in their spare time.

Apologies if I don't get the terminology correct. I've searched and searched but can't find the right word to call the + symbol (in datasheet view) to the left of a record in a table where it has children. In the image below, I've put arrows on, showing where the plus symbol was and has now become a - sign as it has expanded the children.

View attachment 119147

It works very nicely to see parent, child and grandchild etc records that are related by keys (defined in the relationships screen). This makes it easy for the data manager to open up related records if someone has put in some bad data that needs corrected.

However, I was advised to split the database so that the forms etc were in a separate file from the data, which I understand is best practice.

After I did that the + symbol is no longer there for users to click on to see related records (but I checked and the relationships are still there).

I wondered if that was just something we have to live with, or if there's a way to get those + symbols back again even when the DB is split.

If it is relevant, this is Office Pro Plus 2016.

If it isn't possible to get the + symbols back after splitting the database, I suppose I could create a form that has parent and child records, but it seems a lot of work (that the customer may not pay for) unless there's a quick way to build such a form?

Thanks for any help or advice.

That's one of the features in Access that leads newcomers down the primrose path to the swamp.

It's called Subdata Sheets and it's a bad idea. Right now, in early development, it seems really sweet. It's not.

What you are doing is embedding tables inside other tables inside of other tables. That is going to come back and bite your users. As more and more records are added, things get increasingly slower, until they cry Uncle.

Splitting the database into two accdbs is indeed a best practice. There are very few times I like to use the term "best" because there are differing opinions on most subjective evaluations. In this case, there's enough of an argument in my opinion to call it a best practice.

The interface objects (forms and reports ) and the logic objects (VBA and macros) and the queries belong in the Front End. The data objects (tables) belong in the Back End.

Each user gets a copy of the Front End on their own computer. All of the Front Ends connect to the single Back End located in a shared folder on your network. NOT in OneDrive or other such location. Only on the LAN.

Don't worry about losing the ability to see expanded Subdata Sheets. It's better that way anyway. You should only show your users data through forms and reports anyway.

 

[Mike Krailo]

If it isn't possible to get the + symbols back after splitting the database, I suppose I could create a form that has parent and child records, but it seems a lot of work (that the customer may not pay for) unless there's a quick way to build such a form?

How many forms are we talking about? You could show them one form built correctly and explain the work involved creating it and all of the reasons behind why this is the proper approach to finishing the database application. They can then decide what they want you to do. They might not realize what is possible yet until you show them. Of course, one main form will likely have a sub form or two involved, so really you are creating a minimum of two forms for your example.

A finished application does not have access to the nav pane, or design view or any of the things that the developer has access to. Therefore, the forms are necessary not just to protect the tables and data, but to keep users from tinkering or changing the application. So I would show them a sample of what a finished application would look like without all possible forms working yet and just get the navigation part working. Maybe they will give you the green light after that.

The usual way is to create a Main Menu of buttons that allow the users to navigate to any area of the application they need. Personally, I only like to have one single form open at a time, but that is all a design choice you have to decide for yourself. Is it a lot of work creating all the forms and making everything work seamlessly without any errors? Yep, it could take quite a while to make attractive looking forms if you don't do it on a regular basis.

I had one customer that insisted on using tabbed forms and had to deviate from my normal process in that case.

 

[CJ_London]

unless there's a quick way to build such a form

click on the table
click on create
in the ribbon select More Forms
select multiple items

do this for the main form, then your subform

open your new mainform and then drag the new subform to the footer section of the main form.

Providing relationships have been set in the BE, the new subform control linkchild and linkmaster properties should be automatically populated. If not, then populate yourself - the mainform PK in the linkmaster property and the FK in the subform for the linkchild.

You can the edit the forms to show just the controls you want the user to see.

You can have subforms within subforms - I believe 7 layers deep

Or simply have the multiple forms, and when a user clicks on a record on the main form it opens the child form as a separate form

Or use queries - you can set a subdatasheet name although I don't recommend it for the same reasons as George

 

[arnelgp]

I'm not sure how much further it will stretch to developing more forms to let them correct their inevitable data entry errors.

you employ Validation on your data to minimized errors on data entry. creating many forms with no validation is just creating a garbage can, garbage in, garbage out.

 

[The_Doc_Man]

You are getting lots of good advice here. I hope I can add a bit more, from my own admittedly jaundiced viewpoint.

The problem with databases is that they have a hidden relationship of two factors inside of them. The factors are "convenience" and "security." These two factors have to be considered as two sides of the same coin (that coin being the DB as a whole). You can have some amount of each factor, but absolute convenience usually means limited or no security.

Using the sub-datasheet approach, you have convenience, but anyone can traipse in those datasheets and bollix up the works really easily - because just as it is convenient to SEE related data, it is convenient to MODIFY related - or UNRELATED data. (Can you say "bull in a china shop?) What you truly do not want is for a junior clerk who considers typing as a foreign language to have access to inner workings of databases. The only people who should ever be looking at the tables directly as datasheets are folks who understand both programming in general and your data layout in particular. And even for experts, that examination would only be for evaluation purposes.

The potential for damage through typing errors and ignorance of relationships cannot be overestimated. This is why folks split databases and then build forms to stand in the way of browsing the tables. The forms can have validation built-in to prevent you from creating an abomination, and trust me when I say that inexperienced or untrained users will NOT generally be thinking about what not to do.

As to "the budget" for this work, there is a hidden implication that you think the work will be done one day. (Or your boss thinks that.) As long as the DB is in use, it will need changes. If it represents your business in some way, and your business changes, the DB will have to change in order to prevent the tail from wagging the dog. The first and most important question to ask is, "How important is this DB to the operation of the business?" The answer will range from "It's a nice idea but we could live without it" to "We can't live without what it provides." The closer you get to the "we can't live without..." position, the less sense a "budget" makes. It is worth asking the question.

It is perfectly permissible to show your boss this thread and, heck, you can even pass along questions. And you can start new threads to continue the discussion along several lines at once if you wish.

As to one other point you mentioned: For searches in this forum, you can look for "Database Normalization" and "Securing a Database" as two subjects that are useful for implementing a stable and useful DB. You said you didn't know what to look for. There are two topics for starters.

 

[CJ_London]

Just read your introduction- so the design principles are much the same as web development- only bring in the data actually required to minimise network traffic. You can’t do that with tables

 

[Mark_]

In addition to what other's have said, some nice advantages of forms is you can lay out data in a manner that follows HOW the end user will use that form. Yes, in some cases you will have two forms to update the same table, just with very different layouts to support two different business activities that both access the same data.

This also allows you to PREVENT users from changing fields they shouldn't. For example, your "ItemID" looks like the field that connects the child table to the parent. In your current layout, what prevents an end user from changing this? Were it me, I'd make sure end users don't see the primary or foreign keys directly.

 

[Pat Hartman]

Here is a very rudimentary form example that shows 4 levels of nesting. I can make it a little fancier if you need. It has some validation for new records to ensure the parent exists and ALL forms require some basic validation to prevent bad/missing data from being saved which probably will work best in the form's BeforeUpdate event. See if you can understand the concept and change the forms to show your data. There is a very tiny amount of code to keep the forms in sync.