Thoughts on Best Approach for Structuring Large Access DB Application

[Fran Lombard]

Over the past 10 years I've been building and adding to one MS Access 2007 Project creating basically an ERP system for managing my Home Improvement business.

I'm reaching the point where it's getting difficult managing the application due to the sheer number of objects.

The functional Areas are:
* Customer - Opportunity (Sales Cycle) Management
* Quoting and Estimating
* Project Management (Budgets and Actual)
* Contracts - Invoicing and Accounts Receivable
* Purchasing (Material Costs) and Accounts Payable
* Labor Costs - Employees - Timesheets - Payroll and Payments
* Accounting (General Ledger) and Financial Reporting
* Fixed Assets
* System Wide Configurations (Users, Security, Enumerations, Addresses, Processing Options Email Templates, Imaging, Attachments)
* System Development Utilities (DB Tools - Class Object Builders -

All of these different Areas are interconnected all supporting the Core Entity of a Project
Many of the forms have Drill-Down functionality to the Originating Document or Transaction

I'm looking for Ideas and Suggestions for best practices regarding Structure of the Application with an eye toward
simplifying on-going development and day-to-day usage of the application.

So far the Application is all contained in one ADP file.
I've considered creating multiple ADP files (one for each functional area) but this then poses some challenges.
Like code access to common modules, drilldowns from one functional area to another ...
I've seen references to a "Library" db for common objects but have never tried this and don't really know what I don't know.

Was hoping to get some insights and suggestions as to possible approaches as well as any gotchas I'd have to prepared to avoid.

Thanks in Advance
Fran

 

[Uncle Gizmo]

With Access 2013, Microsoft discontinued ADPs ... So your first gotcha is if you use ADP'S you don't have a good upgrade route, eventually you are going to run into problems...

 

[Fran Lombard]

With Access 2013, Microsoft discontinued ADPs ... So your first gotcha is if you use ADP'S you don't have a good upgrade route, eventually you are going to run into problems...

This I know, but really, how does this help??

 

[CJ_London]

I'm looking for Ideas and Suggestions for best practices regarding Structure of the Application with an eye toward
simplifying on-going development and day-to-day usage of the application.

hard to say without knowing your current abilities or whether the plan is to pass development on to someone else but generalities in no particular order - some of which you mention, some you may already be doing

have a proper naming convention, no spaces, underscore if you must, in table and field names. Same for forms/queries/reports/modules
Give fields, controls and variables meaningful and unique names.
Use relationships
Avoid composite primary keys.
Avoid using lookups in tables.
develop common interfaces for input and output from each functional area
ensure code is properly documented
maintain a log of changes
define long term requirements
ensure look and feel is consistent across the entire application.

 

[Fran Lombard]

Thanks for your input CJ, most of the things you mentioned I'm doing.

While im currently running my Home Improvement Business, I have a background in Accouting and Accounting Systems.

5 Years Cpa - Auditor of Wall Street Companies with KPMG

10 Years Financial Systems with FlexiInternational.
Was the companies Functional Expert, Sales Demo Guy , Implementation and Integration Coordinator and Left the Company as Director of Product Marketing. My team set the Product Roadmap and designed all the enhancements.

During that time I taught myself Sql and Vb6
I'm pretty strong in both.

Having said that, this app started out as a hobby to keep my skills alive while luanching a completely different business. If i had my way. The front end would be re-written in a .net web/smart device enabled front end and i'd market the product.

Alas, i don't have the bandwidth or the financial resources to make that happen.

For now, the app having aproximately 200+ tables and 300+ screens keeping that whole app in file/ an loaded into my brain is getting tough. Im looking for some input as to approaches for busting into smaller pieces.

Thanks -being stuck inside with this Corona crap has gotten a bit nuts

Fran

 

[CJ_London]

I'm an accountant also (FCMA/CGMA) and been FD of a number of companies small and large in the past. But now specialise in developing tactical applications - typically where the enterprise systems aren't well integrated or unable to meet changing requirements. Current tally is identifying annual savings of around $1/2 billion/year. Unfortunately I don't get paid a percentage! The corona lockdown has potential to be good for my line of work as companies a) need to find savings quickly and b) are having to look at alternative ways of doing things but can't afford long lead times or budget for a strategic solution.

Your thoughts on breaking down to functional applications makes sense. An option would be to hive off certain parts (finance being the obvious one) to existing online offerings and concentrate on a) how you integrate the offline stuff and b) finding ways to handle the 20% functionality that the online offerings don't cover.

 

[strive4peace]

hi Fran,

In case this is helpful for you ... I converted a large ADP to an ACCDB. Took a long time to trace everything. Here are a couple quickie procedures I wrote to get a list of the forms and reports.

Sub s4p_Forms()
'190620
   Dim frm As AccessObject _
      , i As Integer
   i = 0
   Debug.Print "#";
   Debug.Print Tab(5); "Name";
   Debug.Print Tab(40); "DateCreated";
   Debug.Print Tab(65); "DateModified";
   Debug.Print Tab(90); "Type";
   Debug.Print Tab(95); "Properties.Count"
   For Each frm In CurrentProject.AllForms
      'If i >= 100 Then  'first time running, I stopped it at 99, and then did batches of 100 since debug window was too limited to get all at once
         With frm
            Debug.Print Format(i, "##");
            Debug.Print Tab(5); .Name;
            Debug.Print Tab(40); .DateCreated;
            Debug.Print Tab(65); .DateModified;
            Debug.Print Tab(90); .Type;
            Debug.Print Tab(95); .Properties.Count
         End With
     ' End If
      i = i + 1
      
   Next frm
  
   Set frm = Nothing

End Sub

Sub s4p_Reports()
'190620
   Dim obj As AccessObject _
      , i As Integer
   i = 0
   Debug.Print "#";
   Debug.Print Tab(5); "Report Name";
   Debug.Print Tab(40); "DateCreated";
   Debug.Print Tab(65); "DateModified";
   Debug.Print Tab(90); "Type";
   Debug.Print Tab(95); "Properties.Count"
   For Each obj In CurrentProject.AllReports
      If i < 100 Then  'first time running, I stopped it at 99, and then did batches of 100 since debug window was too limited to get all at once
         With obj
            Debug.Print Format(i, "##");
            Debug.Print Tab(5); .Name;
            Debug.Print Tab(40); .DateCreated;
            Debug.Print Tab(65); .DateModified;
            Debug.Print Tab(90); .Type;
            Debug.Print Tab(95); .Properties.Count
         End With
      End If
      i = i + 1
      
   Next obj
  
   Set obj = Nothing

End Sub

Each time, I cut from Debug window to Excel -- or maybe I used Word-- can't remember now. Then modified the code to get the next batch. It was helpful to have this list.

Then I created an ACCDB and imported just one object type at a time. It took hours and Access kept crashing. The lists helped me see what still needed to be done, and what had errors and couldn't come in. Once it is an ACCDB, you can make a query using the MSysObjects table to see what is there.

I linked to the SQL Server definition tables to get other things like the table and field names, stored procedure names and contents, etc

 

[Fran Lombard]

Thanks so much for these code bit, I'm sure they will be helpful if/when I ever get around to converting. I did convert this app to linked tables about 5 years ago, had it working, but missed the adp. Just seemed to run so much smoother and easoer to work with so switched back. Duh!

The app has grown so much since then. I really don't look foward to this.

I also played around with building an Access to .net converter. Was able to build a set of tables to extract and save all form/control/properties definitions. Then started teaching my self vb.net, then found out that language was falling out of favor and threw my hands up and said time to remodel a bathroom and put food on the table.

Anyway, Im rambling, thanks again

Fran

 

[Fran Lombard]

I'm an accountant also (FCMA/CGMA) and been FD of a number of companies small and large in the past. But now specialise in developing tactical applications - typically where the enterprise systems aren't well integrated or unable to meet changing requirements. Current tally is identifying annual savings of around $1/2 billion/year. Unfortunately I don't get paid a percentage! The corona lockdown has potential to be good for my line of work as companies a) need to find savings quickly and b) are having to look at alternative ways of doing things but can't afford long lead times or budget for a strategic solution.

Your thoughts on breaking down to functional applications makes sense. An option would be to hive off certain parts (finance being the obvious one) to existing online offerings and concentrate on a) how you integrate the offline stuff and b) finding ways to handle the 20% functionality that the online offerings don't cover.

Funny us Accountants playing software developer.
Kinda similar though, logical and procedural.

Having a hard time giving up any piece of my baby. Its been suggested to me in the past to buy something off the self but.... its my baby.

 

[strive4peace]

you're welcome, Fran. The procedures to list forms and reports are nearly the same but instead of making a parameter for object type and writing to a text file so they didn't have to be done in batches, I spent less time on the code. I've got lots of documentation tools but none of them work in an ADP! The definition tables in SQL Server are wonderful though -- and if you link to them from Access, you can make some great queries.

 

[The_Doc_Man]

Fran Lombard, no funnier that a PhD chemist started developing databases for computer security applications and genealogy applications. The problem with home-grown projects is that they tend to grow in various directions in a semi-controlled manner at best. The way I would try to manage your problem would require some serious analysis.

Since you are concerned about the number of objects, there are ways to approach this using a "divide and conquer" method.

I would look for divisions based on number of interactions. See which parts of your front-end interfaces are divisible from other parts, and perhaps split the various FE files apart along those divisions. Don't forget that you can link to tables in more than one back-end file at a time.

Also look at the back-end tables to see if there is a simple division of tables one could make based on minimum or no relationships. Then I would put up a fairly "thin" layered app in which the top layer was a menu/control panel item that offered you a punch-list of TOPICS like labor costs, contracts, assets, etc.

Once you picked a punch-list item (always broad-brush in scope) you could launch a second layer that had a default opening menu that gave you individual forms for controlling specific topics within the selected punch-list topic. The second layer would contain the low-level forms and reports for that topic as well. The idea is that a massive system like that is a pain to manage (as you have commented). But splitting it up into smaller topics reduces the clutter factor by a lot.

Let's be honest - with your description, you are probably cognizant of the fact that you either ARE, or FEEL that you are, losing sight of the forest because of the trees. So thin out the forest into manageable pieces-parts.

 

[Uncle Gizmo]

This I know, but really, how does this help??

You asked if there were any gotcha's? And I don't know what you know, or don't know.

 

[Gasman]

Is this of any use to you.?

ADP+

 

[Fran Lombard]

You asked if there were any gotcha's? And I don't know what you know, or don't know.

Uncle Gizmo, sorry if my comment came accross as disrespectful. I've read many of your posts and know you always help out other members with your valuable insight and experience. Truthfully, i was at the end of a long day, feeling stressed, and just reacted without thinking. Please forgive my imprudence.

Fran

 

[Fran Lombard]

Is this of any use to you.?

ADP+

Wow - This something I need to check out. Many thanks

 

[CJ_London]

Having a hard time giving up any piece of my baby. Its been suggested to me in the past to buy something off the self but.... its my baby.

I know the feeling - but I know of one small business that failed because the owner spent so much time developing and refining their application they neglected the business. And selling new enterprise wide software in today's market is very difficult (as the saying goes, nobody gets fired for recommending SAP) - but what does sell is small apps that can be add ons.

 

[Uncle Gizmo]

long day, feeling stressed,

No need to apologize,
It's the unusual, unpredictable and slightly threatening environment we are all experiencing at the moment.