Database design

[LabProARW]

What is the best or proper way to design a database app where I am using a FrmMain with a left side panel with a number of menu buttons. My question is basically when opening FrmMain should I be querying my Access back end database file to get everything into memory that the menu buttons could ask for, i.e. "Recent Samples", "View Maintenance", "Reports", "Sample Search"... or should I only connect to the database back end to get only what each menu button is going to need when the form specific to the button opens? My FrmRecentSamples currently has what seems to be the same _DatabaseBackEndDataSet as my FrmRecordMaintenance. Would my app be establishing a database connection to the backend each time that I select one of my menu options - or is my _DatabaseBackEndDataSet in client PC memory being used for all of my forms launched by my menu buttons? If I use a master query to accommodate the potential needs of each of my panel buttons that would seem to occupy a large amount of memory. I don't know if my _DatabaseBackEndDataSet file on each form is the same data set, but it does have the same name on multiple forms. *Should I wait until a button launches a form to have an individual dataset connect to the back end to retrieve data?

 

[Skydiver]

*Should I wait until a button launches a form to have an individual dataset connect to the back end to retrieve data?

Unless there is a particular speed performance goal you are trying to hit, doing lazy queries -- only getting the data when you need it -- is the best practice. It's times when you need some thing to be particularly responsive like (for example you are doing autocomplete) that you do some pre-fetching.

 

[LabProARW]

Unless there is a particular speed performance goal you are trying to hit, doing lazy queries -- only getting the data when you need it -- is the best practice. It's times when you need some thing to be particularly responsive like (for example you are doing autocomplete) that you do some pre-fetching.

Thank you for the best practice information! I suppose that I have used the same dataset name for a few different forms does not mean that I have loaded into memory several copies of the same data, because when I close a form VS automatically clears that dataset from memory? Is it also the case that each time a form opens which references a dataset, that a fresh connection to the back end database occurs and a new copy is placed into memory?

 

[cjard]

Depends on whether you fill it or not; the designer inserts code in the form load to do that but you can take it out