Advice on Large Front End Database

[pwomack]

I'm designing a split FE/BE reporting application that will replace FormFlow forms.

Currently, the number of FormFlow forms is ~230.

I’ve analyzed the FormFlow forms and determined that I can combine multiple of these forms into one Access form/report. However, I anticipate that my FE forms database will grow to approximately 125-150 forms/reports.

I've never attempted to develop an application with this scope in Access. Does anyone have any advice on large frontend databases? What is the practical size that a frontend can get? What things should I be considering? What things should I avoid doing?

Each report will be 4 or 5 pages on length. Since each report contains a considerable amount of information, I placed a tab control on my data entry form. Each tab of the control is used to input/display the different sections of the report (e.g., customer info, smoke test, leak test, noise test, particle test, velocity test, light test, sound test, and remarks)

All of the report sections, with the exception of the velocity section, will be common to each report. The velocity section is a subform on the tab control and is what makes the reports different. In most cases the differences are significant. In cases where the velocity sections are similar, I've determined that I can combine multiple FormFLow reports into one Access form.

Thanks for your advice.

 

[MichaelRed]

I currently attempt to maintain a ".MDB" db with ~~ 50Meg BE and ~~ 75 Meg FE. The number of forms / reports in my FE is ~~ 230. There are times / days when it is absymal, but mostly it sorta lumbers through rather sluggishly, but get the job 'done'. The app has been in development for Waaaaaaaaaaaaaaay long (circa 1984) and obviously has had several developers with varying skill sets. A brief review of the overall system has convinced me that it is in need of some serious maintenance (trimming out the blubber mostly).

Others may provide 'hard' limits, and these are certainly available. Realistic issues are generally much more involved in the networkl number of (concurrent) users, and -most importantly- the BE dbengine. In this list, the last item is (IMHO) is the issue. The Jet dbengine is quite inefficient, as all requests for data return the cartesian recordset - leaving the LOCAL processor to remove the records excluced by conditional clauses and to perform aggregation of records. The bandwidth requirements of this approach are MUCH greater than when the BE dbEngine performs htese functions and returns the condensed record set. While the ratio of the recordset size is open to debate, it is clear that the larger the individual recordsets (ee.g. TABLES) the larger the overall problem.




MichaelRed

 

[pwomack]

Michael,

Thanks for your comments.

 

[ChanceD2]

I wanna toss in my $.02 here too!

Do try to combine as much has possible. Make each form do multipule things. (i.e. - Don't make an add form, an edit form and then a delete form. )

I truly don't see how you could have that many forms. Are you sure you need that many? Have you really torn this application apart? Have you fully normalized your tables? If you don't that would make for a huge frontend (I'm hoping/guessing that is what you are doing)

If you are interested in trying to tear this down to a smaller size, let us/me know.

If not, good luck!

C-D2

 

[pwomack]

C-D2,

Thanks for your advice and interest. Let me give you some more information about the application.

Firstly, it's not a typical application (if there is such a thing as a typical application.) It's a reporting application to be used by techs in the field to create reports for various categories of equipment. The categories of equipment include biological safety cabinets (bsc), chemical fume hoods, laminar flow devices, clean benches, decontamination, etc, etc. Each category of equipment requires reports for various types of equipment within the category (e.g., there is a zoned bsc report and a non-zoned bsc report and there is a report for a bsc that is tested with a flowhood device and another report for a bsc tested with another type of device, and a bsc report with a correction factor and one without, and a report for a specific manufacturer, and on and on.)

I've determined that I can combine reports that are similar. However, many of the reports are dissimilar.

So, I hope that you can see why the number of reports will be large.

Our techs in the field will have laptops and they will be creating new reports to give to the client.

The BE database for the techs should be empty at the start of each day. However, there are several reference/lookup tables in the FE database.

So, the tech goes to a client site to test a BSC or a fume hood or a clean bench or any combination of these and enters the test results into the application's forms, and then prints the report for the client.

The number of BE tables is very few (about six so far) and I don't have any sort of parent-child table relationship except for the velocity page of the report. There are many cubic feet/minute (cfm) readings for each velocity page of the report.

So, the tech enters his/her test results, saves the data to the BE database, and then prints the report for the client. It's basically a data entry and reporting application, and there is really no need to do much updating or deleting.

I hope that I've made myself clear.

 

[JoeAtWork]

Are a lot of your forms dialog boxes for entering the parameters for the report? If so, consider creating a single dialog box for all reports (even if they use different types of parameters).

Here's how it can be set up:
1. Create a "Reports" table on the front-end database.
2. Fields to be included are the name of the report (e.g. "rpt_Customers"), a user friendly name of the report (e.g. "Customer List"), and whatever other optional fields you might find useful.
3. Create another table called "ControlsForReports". Fields to include is the name of the control (e.g. cboCustomers), and a SortOrder number (if you care which control should be listed first). This table is a list of controls for entering parameters for reports.
4. Create a third table called "ReportControls", which links which reports use which controls. This table would just have two foreign keys, one to "Reports" and the other to "ControlsForReports".
5. Create a large dialog box, on the left side put a listbox. The listbox will contain the "friendly" name of all the reports. Two hidden columns in the listbox will have the primary key to the report, and the actual report name.
6. Underneath the listbox, place two buttons, "Preview" and "Print".
7. On the right side of the screen place all the controls you have listed in "ControlsForReports". Try to size them so that they look pleasing when positioned down the right side of the screen (i.e. make all textboxes, dropdowns, etc., the same width). Set the Visible property of all these to False.
8. On the OnClick event of the Reports listbox, lookup in the ReportControls table all controls used by that report. Adjust the Top and Left properties of these controls so they stack on top of each other (a little math involved there), then make those controls visible.

I haven't given all details on how to make this work, but I think you get my drift. The initial setup is a little harder than just making individual dialog boxes for each report, but once you get it going you have the following advantages:
1. Only one parameters form for all your reports.
2. One place to run all your reports (easier for users).
3. Consistency in how to run reports (easier for users).
4. Easier to create new reports. Often the controls you need will already exist, all you need to do is enter the report name in the "Reports" table and enter which controls they use in the "ReportControls" table. Saves you the step of creating a new dialog box for entering parameters.

 

[pwomack]

Thanks Joe,

I like your design and I may very well use it in a future application.

However, I don't use a single dialog box for report generation. I have one "Summary" form where the user may enter search criteria such as client name, report type, and several other fields that are particular to my company.

The user will then open a form containing the report information by double-clicking a row on the "Summary" form. A fair amount of calculations are performed upon opening of the form (these are four or five page reports.) On the form is a button to open a report (which report to open is table driven.) No report has a record source. The reports directly reference fields on the forms.

Thanks again.