Relationships

[David44Coder]

@LarryE There's only empty tables as per the design prior to your Junction design. (I don't think that'll be what you want, but will attach it. No, I'm told it's too large).
I started with a table and fields I thought would be used/needed. It's all changed since then. Serial number is the physical drives serial number (which I thought might be a good way to identify each one). Location held whether it was internal or external. This was early planning - and maybe not the best idea as one computers external drive on LAN may be anotehrs internal. There's some not connected/powered up as offline backup. When time to add files I'd choose which ones to use.

 

[LarryE]

@LarryE There's only empty tables as per the design prior to your Junction design. (I don't think that'll be what you want, but will attach it. No, I'm told it's too large).
I started with a table and fields I thought would be used/needed. It's all changed since then. Serial number is the physical drives serial number (which I thought might be a good way to identify each one). Location held whether it was internal or external. This was early planning - and maybe not the best idea as one computers external drive on LAN may be anotehrs internal. There's some not connected/powered up as offline backup. When time to add files I'd choose which ones to use.

OK. I'm working on some forms and reports and will attach a file for you to learn from and try out for testing soon.

 

[David44Coder]

Thanks Larry appreciate that. Don;t do it for me though ! Although I'd learn either way, all going well.

 

[LarryE]

Thanks Larry appreciate that. Don;t do it for me though ! Although I'd learn either way, all going well.

I tend to help by creating examples, so here is an example of how to implement a very simple backup file inventory tracking project. I only ask that you at least study the relationships and how I used a series of sub-forms for data input. Maybe just use it as a study guide if that's what you want to do.

 

[David44Coder]

Thanks Larry, Opening it now.
Pat, I was reading the serial number from the drive. It seemed a good way to identify it,as presumably it'd be unique.
But early days to see issues and time to fix.

 

[The_Doc_Man]

Just remember that a drive can possibly "host" multiple logical volumes or partitions. Therefore, if you have "volume" and "drive" in the same table, DriveSerial cannot be a UNIQUE field.

 

[LarryE]

Just remember that a drive can possibly "host" multiple logical volumes or partitions. Therefore, if you have "volume" and "drive" in the same table, DriveSerial cannot be a UNIQUE field.

In the file I gave the OP, the Drive table was a child of the Volume table and it's Serial number was field only and not linked to anything. I always use Autonumbers ONLY as Primary keys. I hope the OP will learn from that. Maybe Volume should have been a child of the Drive table.

 

[LarryE]

In the file I gave the OP, the Drive table was a child of the Volume table and it's Serial number was field only and not linked to anything. I always use Autonumbers ONLY as Primary keys. I hope the OP will learn from that. Maybe Volume should have been a child of the Drive table.

I used the term "Volume" to define the storage environment each computer has access to, such as OneDrive, Internal HD, External Network, external CD drive etc. Volume really should be re-named Environment. Then each Environment has multiple Drives and each drive has multiple folders etc.

 

[David44Coder]

That's a very impressive front end @Larry. You didn't use your 'junction' method in msg 41?
I would next populate the tables then see if I can follow how it all works? I did think there might be more queries and get a idea how they used the tables. This is a bit of of a jump from where I was. Any advice on usage?

 

[LarryE]

That's a very impressive front end @Larry. You didn't use your 'junction' method in msg 41?
I would next populate the tables then see if I can follow how it all works? I did think there might be more queries and get a idea how they used the tables. This is a bit of of a jump from where I was. Any advice on usage?

There is no need for any queries actually. I only made one for the report. I used the original design because it was better for your project.

I used subforms linked together with Master/Child fields. ACCESS automatically inserts foreign keys into the proper tables for you when do that.

Please look carefully at how subforms were used. It's important in understanding how ACCESS works.

There are 2 computers
Each has multiple Volumes which are actually environments which have multiple Drives
Each Drive has multiple Folders
Each Folder has multiple backup files

The BackupFile form is a subform of the Folders form
The Folders form is a subform of the Drive form
The Drive form is a subform of the Volume form
The Volume form is a subform of the Computer main form.

Very basic ACCESS. Simple and uncomplicated.

Make sure you study the table designs. Primary Keys are automatically assigned. Always use Autonumber Data Types in Primary Keys. They are used by ACCESS as references only and for no other purpose.

 

[David44Coder]

I may be missing something but If there are no queries how would you find a particular backup file? I can see how you could query the table holding the filenames, but how are the other tables included?
I've haven't ever used a report and I'm not (yet) sure of the subforms functions. They follow the same sequence as the tables in the Relationships screen?
Should I add some of my data into the tables? Okay on using Autonumbers.

 

[LarryE]

I may be missing something but If there are no queries how would you find a particular backup file? I can see how you could query the table holding the filenames, but how are the other tables included?
I've haven't ever used a report and I'm not (yet) sure of the subforms functions. They follow the same sequence as the tables in the Relationships screen?
Should I add some of my data into the tables? Okay on using Autonumbers.

1. I am not sure what you mean by "find a particular backup file". You navigate to the Computer, then then Volume you want, then the Drive, then the Folder and finally the backupfile name. Use the navigation buttons to navigate forward, backwards etc. Each form has these buttons except the BackupFile name form because it is a continuous form.
2. If you want a form that displays all the backup files and their locations then here is an opportunity to create your own form and insert it as a sub-form at the bottom of the Computers form. You can copy the FrmBackupFile and use it as a new form. Name it FrmSummary. Then open the FrmSummary in Design mode, open its Record Source, and insert QryReport into it. Add all the fields you wish to display into the forms Record Source and add those fields to the form. Each form (and the report) has its own query as a Record Source. The only query that I specifically named is the QryReport. All the other queries used to edit forms, I did not name, so they do not show as named queries under the Queries section of the Navigation Bar.
3. The forms and sub-forms do follow the same sequence as the table relationships show. Computers>Volumes>Drives>Folders>Backup Files.
4. You can open the report by simply clicking the Report button at the bottom of the form. The form will close but re-open when you close the report.
5. My main point in giving you this file is so you learn how forms and reports work in a very basic simple ACCESS design. There is limited VBA code. I used VBA code so navigation buttons work and so you can quickly go to selected records on each form. Try the Find drop-down arrows on each form to select a particular Computer or Volume or Drive or Folder. You can open the VBA code window with alt+F11.

 

[David44Coder]

It's good to get a bit of an overview, This is all very new and unknown, How you've done things is different to what I know or understand so it will take me a while to figure it all out (he says optimistically).
I'll have a go at frmSummary as you've outlined. Might not succeed but can always try again.
Will this also search and display any same files or folders wherever they are?
I'm still keen to add my data as it may become more familiar and help nail down whats happening. And is this done manually (entering data into the tables) or is there some other mechanism ? (I suspect the former).

 

[LarryE]

It's good to get a bit of an overview, This is all very new and unknown, How you've done things is different to what I know or understand so it will take me a while to figure it all out (he says optimistically).
I'll have a go at frmSummary as you've outlined. Might not succeed but can always try again.
Will this also search and display any same files or folders wherever they are?
I'm still keen to add my data as it may become more familiar and help nail down whats happening. And is this done manually (entering data into the tables) or is there some other mechanism ? (I suspect the former).

You Add records on each section of the form using the

button. To add a new Computer, click the symbol on the Computer section for example. To Delete a record click the

button. You can delete the Laptop computer I entered for example. You can also delete the Volume One Drive I entered under the Win11-PC computer. It was a test record. Note that when you delete the Laptop computer I entered, all records related to that computer are also deleted automatically. That is how Relational Integrity works.

You use the form for adding, deleting and editing records and ONLY the form. Never ever enter data directly into a table by opening the table itself. Always use forms to add, delete and edit data.

Try adding your own real data. For example, Win11-PC has a Volume LAN that has a Drive A and Folder Hub G, but does not have a Backup File Name. Enter your backup file name.

The best I can do is give you an example of what you wanted to do.

 

[David44Coder]

I'm getting the hang of what to do. Have deleted Laptop and entered one of mine incl all the details/fields except Filename.
I see the Green text info.
Where is the data entered, e;g; If I wanted a browse Dialog to pick up all the details automatically?
Then a method to add all the files in a folder. Or are these tasks it doesn't do, or can't do yet ? Something I would add?
You say Never ever enter data directly into a table so I'm mindful of that.
If I hover over Report the balloon it says Delete this Account. I did click it and got something I couldn't return from.
I haven't studed the inner workings too closely... have looked at everything though. As expected a bit mysterious to start with!

 

[LarryE]

If I hover over Report the balloon it says Delete this Account.

So sorry, that should be Open Report instead of Delete This Account.

1. Open The FrmCumputer in Design mode
2. Change the Control Tip Text property from Delete This Account to Open Report

should be

I did click it and got something I couldn't return from

When you click the Report button it opens the report for you to view and/or print. It's a list of all the backup files and where they are located. When you close the report, the main Computer form opens again.

Where is the data entered, e;g; If I wanted a browse Dialog to pick up all the details automatically?

I don't know what you mean.

 

[David44Coder]

I've changed the tip text, and seen where to close the Report.
I kind of see how the PK and FK works. Always numbers and PK is Access-generated autonumber. Correct ?
Is a sub form always a child of a main (parent) form?
Are the sub Forms just dragged into the main Form. Footers are headers are extensions of the main form?
Why do you say not to enter data directly to the tables, only through the Form? It (seems to) allow entry of only one thing at a time.
I meant pick up a folder name from a browse dialog, rather than typing the name in - and again for the files, getting them all at once.

 

[LarryE]

PK is Access-generated autonumber. Correct ?

Yes.

Are the sub Forms just dragged into the main Form. Footers are headers are extensions of the main form?

Yes.

Are the sub Forms just dragged into the main Form. Footers are headers are extensions of the main form?

They are not "dragged", but yes they are inserted as a control.

Why do you say not to enter data directly to the tables, only through the Form? It (seems to) allow entry of only one thing at a time.

That is correct. You enter each Computer name, Volume name, Drive, Folder and Backup file name one at a time.

I meant pick up a folder name from a browse dialog, rather than typing the name in - and again for the files, getting them all at once.

It may be possible. I will see what can be done.

 

[David44Coder]

Thanks Larry. I'm getting a better idea and it good seeing how this all fits together.
I can get a list of folder names & files. Populating tables automatically is pretty essential.
It's not practical to enter one at a time unless done by code.
Then seeing how to query it in a useful way. I can handle this in one table but not so good with many.

 

[LarryE]

Thanks Larry. I'm getting a better idea and it good seeing how this all fits together.

I can get a list of folder names & files

. Populating tables automatically is pretty essential.
It's not practical to enter one at a time unless done by code.
Then seeing how to query it in a useful way. I can handle this in one table but not so good with many.

I have been able to use some complex and advanced VBA code to be able to select the files you want, break them into Drive, Folder and File parts and then append the parts to the appropriate tables. I am still testing it.

If you will attach the file containing the folder and file names, I will see if I can import them all at once into the appropriate tables. If you can send it in EXCEL or ACCESS table format, that would be ideal.