What was/is your "largest" access project

[HillTJ]

To date, my projects have been limited to a split database with maybe up to 6 or so concurrent users. I'm keen to know the 'scope' of some of the applications running an access front end. If you feel inclined i'd like to know what you've done. Obviously no propriatory or intellectual details, but just an overview. Cheers.

 

[XPS35]

You can express the "size" of a database in all kinds of quantities. Number of tables, number of records (all tables), number of users, number of changes and probably even more variables. So your question is ambiguous.

 

[HillTJ]

Yes, I probably should have used 'scope' instead. I'm interested in number of users, number of records, "importance" (I can't think of a better description), years in use etc. Really, anything of interest that describes access' capabilities.

 

[Pat Hartman]

I had one application that wasn't all that big object wise (~ 400) but the tables contained millions of rows. There were about a dozen concurrent users with a SQL Server BE. There were probably about 30 authorized users. Some were local in our Hartford, CT office and the rest were spread halfway around the world from San Francisco to Paris. So, we covered a lot of time zones. The off-site users logged in using Citrix which we hosted on our servers in Hartford so the server was local with the database and all the word documents that the application filled.

The most users app, had about 125 authorized users but rarely more than 50 concurrently. They were all on the same LAN and the BE was also SQL Server. Most tables were in the 10's of thousands of rows range but a couple were over a million. This app actually was split into three applications because they were used for different purposes. One handled batch processing which involved collecting data and sending it to the State for processing overnight. Another linked to a database at the state so we could have a view into our data on their server and the third was our local daytime workhorse. The biggest of the three has ~ 900 objects. 170 forms, 120 reports, 25 modules, 80 tables, and the rest queries.

 

[CJ_London]

I wrote an app to calculate commissions for sales and support staff. It involved extracting data from six corporate systems - hr, legal, payroll, invoicing, general ledger and crm. Managing business rules, paying around 2000 staff on turnover of around £4 billion. All run on a spilt access db and managed by 2 staff. Included sending each employee an excel summary of their commission each month.

By clarifying the business rules and applying them correctly and providing an easy resolution process I reduced commission queries from 6000 a year to less than 100 consequently I was able to reduce staff from the original 5 to 2 and saved around £2m a year in overpayments of commission.

another I identified where a company was losing £200m a year due to overclaims by agents - again heavily integrated with corporate systems

then there was the app that was used to help manage the build of a nuclear power station handling around 1m rows of data every month.

and another that recalculated the wages of 15,000 carers as a result of a change in the law regards how pay and expenses should be treated

Most concurrent users (using a sql back end) was 600 - another sales commission app

 

[HillTJ]

Wow, pretty amazing stuff. The scale of these projects & implications scare me! I take my hat off to you both.

 

[The_Doc_Man]

My biggest project was for the U.S. Navy's Naval Enterprise Data Center New Orleans (NEDC NO). We were a hosting center that sort of "grew in the telling" (as is the case for most tales). When Hurricane Katrina hit, we had maybe 25 projects - but all of them stayed up and running because we had a fully functional backup site. We only went down for about 3 hours to switch functions to the alt site. Kept the systems running for from 6 to 8 months (projects switched back piecemeal), but in other disasters, other sites failed. We were successful and suddenly had more business than we initially knew how to handle. Total chaos - and proof of the old adage, "No good deed goes unpunished."

The system administrators for the hosted machines (some physical, some virtual) had to apply certain security patches and other fixes to each server individually. We had to be able to answer the status of every machine at any time if the project manager asked for a report. At its peak, we had 80 customer projects and about 1500 servers, more or less split between development systems and production systems. The complex systems would have an app/web server, a data server, and a behind-the-scenes/batch server.

We would get from 15 to 30 notices every two weeks and EVERY NOTICE had to be matched up with EVERY SERVER to track any of about 15 states. Since we had multiple operating systems among the 1500 servers, we had states like "Does not apply" (Windows patches don't work on UNIX servers), "Applied Successfully", "Waiting for Vendor to Publish Proprietary Version of Patch" (most often because the UNIX vendors had their own way of doing patching), "Waiting for Scheduled Down Time", "Patch Failed", "Waiver Granted", etc. If you work that out, we would have a record of action for 1500 servers x up to 60 notices per month = 90,000 records per month. We could not archive anything until a particular patch was in one of the "Closed" states for EVERY SERVER - and there was another time limit as will for how long a report had to be online for querying. We had literally dozens of reports showing every server's status with respect to every patch, broken out in various ways including by project, by machine type, by operating system, by the nature of the patch, etc. Because of the several "Waiting" states and the fact that sometimes we needed a proprietary patch rather than a generic Windows patch, it was not uncommon to have things open for 6 months. So yes, something like 0.5 million records online.

It got worse since we needed to know WHO patched the servers. We had 40 people who might do that. We had to have tables to show the project to which each machine belonged and a designation for the administrator team responsible for that project (among others), because only the correct team's members were allowed to record a status other than "OPEN" - the status of any patch that had just been entered and which was marked for the correct O/S. The action logs showed every attempted action for each server/patch combo, and that was usually a minimum of two entries: Initial OPEN entry and final "Applied Successfully" entry. But it wasn't unusual to have two or three more action entries when a system had a down-time limit.

I cannot show you anything from the project because it was (and still is) owned by the U.S. Navy, though I think they have since replaced what I wrote with a commercially available package. I wasn't allowed to keep any of that code.

 

[HillTJ]

Well, i'm in the latter stages of writing a beekeeping database!. Although small, unimportant & insignificant, i'm passionate about writing it to the best of my ability & best practices. Hence, my questions on other threads.

 

[HillTJ]

So, how did you people start out in this industry?

 

[CJ_London]

I’m an accountant by training. My first app was an erp system for my employer - a manufacturing company - written in pascal over a couple of years on top of my day job. Back then no software existed and no rdbms’s - at least not for ‘micro’ computers as they were called then so I had to write my own routines to store data on disks, create and maintain indexes etc. ended up with 8 apple II e’s connected via a 40mb Corvus hard drive covering all aspects of stock management, production programming, despatch and invoicing and cost of sales plus providing all the data required for payroll. Scale - around 150 staff, 700 products, 5000 movements a week.

I was one of the first people to network micros in the UK and Apple persuaded my boss to allow me to travel the uk talking to business groups about my experiences in developing the network and comparing it with mini computer networks. did that for a year or so.

 

[HillTJ]

@CJ_London , very cool. Thanks for sharing. And what about programming skills?. Did you just read & study?.

 

[CJ_London]

back then there were no training courses other than signing up for a year or two at a training college. There was no internet and libraries (at least those near me) did not cater for books on 'new technology'. But there were books in bookshops. My technique was to read from cover to cover, not trying to absorb everything but to get a feel for terminology and process. Then as I started developing I would be reminded I had read 'something' about what I was trying to do, and then try to find it in the books. So for example I didn't understand the importance of indexing until I found the app was starting to run slow. There were also pascal user groups that met 4 times a year around the UK where novice developers could meet, and share experiences. No laptops, smart phones so all done with overhead projectors, some printed notes and prolific note taking.

 

[GPGeorge]

My most complex project was for a university which conducts performance testing for baseball and softball equipment. They generate test results in their lab, which is literally in the basement of the building. I always found that interesting. "Let's go down to the lab." <Queue the scary music as you descend into the basement.>

We built a system using Access as the front end to two instances of SQL Server, one on-premises, one hosted "in the cloud" behind their public facing website.

The Access interface imported test results from their lab equipment, which was output as csv files. The Access FE then crunched the data and pushed the summarized results to the second SQL Server instance where people interested in finding "approved" softball and baseball bats can search those results. It also amazed me to learn how many different manufactures are in that field. After our .net developer left the company, I was tapped to maintain the web application as well as the Access side. It turned out no one else really knew enough about the business side and they decided it would be easier for me to learn basic .net coding than to try to hire someone and train them.

Arguably the largest project I ever worked on was for one of the most prominent sports clothing and equipment manufacturers in the world. I was brought in when another Access dev left or something (in retrospect, I should have realized what that implied). Again, Access FE/SQL Server BE, with data coming from nightly extracts from their Oracle-based ERP system in the SQL Server for the segment of the business in which we were interested. The goal was to use prior sales and other factors to predict future sales out over 3, 6 and 9 month periods. The tricky part of that project, which baffles me today, years later, was that their factories ran on a "week" that started on Wednesday, but their warehouses (or whatever other unit interfaced with them, I can't recall) ran on a "week" that started on Thursday. So "Week 26" could mean the same week for both units on some days, but two different weeks on other days. When you are trying to write reports for "weekly production forecasting", that is utterly confusing. I remember one night sitting at my computer until 3:00 AM or so, trying find a reliable way to make that weekly dance consistent. I finally came up with the equivalent of a Talley Table that listed "WeekStartDays" for both units going out over the next five years. If you picked a given date, the table returned the correct week numbers -- sometimes the same, but sometimes not. More than once I wanted to be CEO for one day, just long enough to tell the two departments heads to coordinate their operations and make everyone's life simpler.

Wow, it felt good to get that off my chest.

 

[The_Doc_Man]

So, how did you people start out in this industry?

Zig-zagged my way in. I trained as a chemist and got into the oil-and-gas pipeline business doing computerized instrumental analysis. My graduate degree related to having a computer monitor an experiment in real-time and present the results within seconds of the actual experiment. So with my first "real" job I wrote device drivers for proprietary pipeline monitoring equipment, and later I helped in the design and implementation of a "real-time" database-centric pipeline control system. "Real time" in this case was tempered by the fact that the flowing product was crude oil or some heavy fractions thereof. For instance, a flow valve took 2 minutes to close and lock. So responding in seconds to an event was great for that situation even though my college experiments monitor millisecond events. Stopping an oil leak from a ruptured pipeline in less than five minutes was considered EXCELLENT in the industry at that time. I even implemented a hierarchical database management system with virtual demand paging of database elements and a least-recently-used, priority-based virtual memory management scheme. Rose through the ranks, came within one step of being company vice president - but had to decline that chance due to the events at the start of the next paragraph.

Time passed, my mother became ill (Alzheimer's), and I was stressed to the max, having to put her in a nursing home. My employer got bought out. The new owner said "Everyone relocate to Baltimore." I might have gone except that by then, Mom was extremely fragile and I was her sole responsible caregiver. She was in a nursing home and was stepping through the stages of Alzheimer's. I couldn't move her because the stress would probably have killed her, and there was no one else to check on her on a regular basis. So I declined the transfer and found a local job (for a 25% pay cut) in the oil-and-gas industry, doing navigation software for companies doing seismic exploration of the Gulf of Mexico. No chemistry involved, but I wasn't afraid of the math.

Time passed, Mom passed, and after cleaning up her estate, I was totally free to go anywhere. A blind ad kept me in New Orleans and led me to a contractor company serving the U.S. Navy Reserve. I had picked up a copy of Access because I was totally disenchanted with Borland's Paradox for Windows. Used it for a couple of at-home projects. Then a fellow left the office after having started an Access project that the boss needed. He asked around for folks who understood Access. Two of us answered, but the other guy was already hip-deep and time-saturated in another project, so I took it over and found out WHY you should study normalization BEFORE you start a project. (The departing employee had not studied squat, just tossed stuff together.) The guy who was hip-deep finished his project but then got a better job offer, non-government, and HE departed. Which made me the department "expert" in Access. My big "security actions" database (described above) came later. I retired after 28 1/2 years with contractor companies but always with some branch of the Navy.

 

[Pat Hartman]

So, how did you people start out in this industry?

I was 19 and working as an assistant buyer for an upscale women's clothing manufacturer and was dating a sailor who was a nuclear power engineer. His sub was in port for the fall session and he wanted to take a class - machine language for the 1620 at the local community college. I said -"that sounds interesting, I'll go too". His buddy was also going and since I was going the buddy's girlfriend decided to go also. Nancy quit after the first class. The buddy quit after 3 classes. My guy lasted 6 weeks. I was the only one to finish and I got an A. It just made sense to be but in 1967 computers were only million dollar machines in basements not on everyone's wrist so I just kept on with my day job.

My boyfriend and I parted ways and about 6 months later I decided I needed to move away from home. Of course at that time, good Catholic girls simply didn't move out of the family home to live on their own in the same town. What would the neighbors think? My aunt being more progressive than my mother convinced her that it was OK if I moved to Hartford. It was 40 miles away and so they could justify the separation that way. So, I applied at the Travelers Insurance Co. Colleges weren't producing programmers yet so most large companies had their own training programs. The lady I interviewed with was very excited about my class and asked me if I wanted to be a programmer. I said "What's a programmer?" She told me that they would send me away for training and what they would pay me (2.5 times what I was earning at the time) so I said "Sign me up". I moved to Hartford and lived at Wolvertine Hall which was the YWCA residence for young women The rest is history. The "Y" housing today is quite a different program than it was in the 60's. It was like living in a college dorm. No men allowed, shared bathrooms, breakfast and supper in the cafeteria m-f.

It was COBOL, IMS, CICS, DB2 until I discovered Access at a consulting assignment for Readers' Digest in the early 90's. I thought I had died and gone to heaven. I could point and click and with remarkably little code, create a beautiful application that connected to the DB2 database on the mainframe. I found a new home. It took about 5 years to fully transition to only Access assignments but I did it.

 

[spaLOGICng]

My largest Access Application was an ERP for a Manufacturing and Servicing Business that had 80 Branch Plants that manufactured anything from Household Products to High-end Furniture. It handled A/R, A/P, Bill of Materials, Work Order Production, Sales, Automated Inventory, Monthly, Quarterly, and Annual Reports, Budgeting, Contracts. It had FIFO, LIFO, Weighted Average, and Standard Cost Accounting Methods. It also had a Unit of Measure converter so that Products could be Purchased, Issued, and Sold in different Measures. It integrated with designing software such as AutoCAD and MasterCAM. It maintained seven different Inventory Types, Capital Assets, Fixed Assets, Finished Goods, Raw Materials, Consumables, Maintenance and Repair, Small Tools and Equipment.

My experience with Microsoft Office began with Access 1.5 in 1991. The Application began with MO 3.0 in 1993. My background as a bean counter led me to simplify our accounting for manufacturing and was the beginning of IT aspirations.

The Application had morphed and evolved over the years and through the next 17 years to Office 2010 and was still running when I retired. The Data was split in 1997 and moved to SQL Server in 2000. At my retirement it had migrated to SQL Server 2008r2. Initially the Application ran separately in each Branch Plant. With the Internet and SQL Server, a Data Server was setup to harness all Branch Plants. Corporate maintained a Master Inventory and Master Contracts separately until the data was merged with SQL Server.

Initially updates were delivered via Floppy Disks. Once the WAN was setup, I created an Update System similar to modern day Updating methods. It became a fairly seamless operation.

The Application tracked Screen/Object usage, where all Open Methods were sent through a single handler, that recorded a history of the object, and who was using it. This allowed us to see who was doing what and it enabled us to a create a custom menu system tailored to the specific User.

When I retired, the application had over 1,500 Screens and 3,000 Reports. Tables were highly efficient and most of the Queries in Access were migrated as VIEWS in SQL Server. With the power of Triggers and Stored Procedures, a lot of bloat could be removed from the Access Front End. When we migrated the Data to SQL Server, the Access BE was 500mb. That would be the average for all 80 Branches. I think all total was around 50gb. When I retired in 2010, there was over 600gb.

Some of the tricks of making the Application run Faster:
1) Strip out Commenting in the Production Version
2) Remove Code Nesting in VBA in the Production Version.
3) Remove as much Whitespace as possible in the Production Version
4) Convert the Production Version to an MDE/ACCDE.
5) Make VBA Code as re-usable as possible.
6) Avoid writing unnecessary VBA Code when the same function could be performed in an ACTION Query or Stored Procedure.
7) Make sure all VBA Code has an Error Handler and Logging System.
8) One of the biggest over-rated features in Access is the ability to create Class Modules. In my opinion, they are not worth the time and effort. I would only create them when I felt something could not be accomplished easier or faster.
9) Disable Auto-name Tracking and Logging in the production version.

Some of the Tricks of making an Application Smarter:
1) Implement a Global Variables Table that is maintained from the Server
2) Create VIEWS to see all System Objects in SQL Server connected to your FE
3) Create a method to automatically Link to Tables and Views, that will also create a Query and a basic Form. This will reduce your development time by 15 minutes for each.
4) Create a Chat System.
5) Reduce dependency on external Systems by integrating them into the Application.

I am a professional freelancer in my retirement... ha ha.... retirement. I do not have much time to comment in the forums. My skills are highly sought after. Since my retirement in 2010, I have earned more than double in just 12 years than I did in my 20 years before retiring.

Access is by far the most cost-effective software for just about any business. Even if a Customer pays $15,000 for an Application, it belongs to the Customer, it is not full of unnecessary bloat and has all the features that the Customer wanted, unlike most anything else that is purchased off the shelf of subscribed to.

 

[MsAccessNL]

My largest Access Application was an ERP for a Manufacturing and Servicing Business that had 80 Branch Plants that manufactured anything from Household Products to High-end Furniture. It handled A/R, A/P, Bill of Materials, Work Order Production, Sales, Automated Inventory, Monthly, Quarterly, and Annual Reports, Budgeting, Contracts. It had FIFO, LIFO, Weighted Average, and Standard Cost Accounting Methods. It also had a Unit of Measure converter so that Products could be Purchased, Issued, and Sold in different Measures. It integrated with designing software such as AutoCAD and MasterCAM. It maintained seven different Inventory Types, Capital Assets, Fixed Assets, Finished Goods, Raw Materials, Consumables, Maintenance and Repair, Small Tools and Equipment.

My experience with Microsoft Office began with Access 1.5 in 1991. The Application began with MO 3.0 in 1993. My background as a bean counter led me to simplify our accounting for manufacturing and was the beginning of IT aspirations.

The Application had morphed and evolved over the years and through the next 17 years to Office 2010 and was still running when I retired. The Data was split in 1997 and moved to SQL Server in 2000. At my retirement it had migrated to SQL Server 2008r2. Initially the Application ran separately in each Branch Plant. With the Internet and SQL Server, a Data Server was setup to harness all Branch Plants. Corporate maintained a Master Inventory and Master Contracts separately until the data was merged with SQL Server.

Initially updates were delivered via Floppy Disks. Once the WAN was setup, I created an Update System similar to modern day Updating methods. It became a fairly seamless operation.

The Application tracked Screen/Object usage, where all Open Methods were sent through a single handler, that recorded a history of the object, and who was using it. This allowed us to see who was doing what and it enabled us to a create a custom menu system tailored to the specific User.

When I retired, the application had over 1,500 Screens and 3,000 Reports. Tables were highly efficient and most of the Queries in Access were migrated as VIEWS in SQL Server. With the power of Triggers and Stored Procedures, a lot of bloat could be removed from the Access Front End. When we migrated the Data to SQL Server, the Access BE was 500mb. That would be the average for all 80 Branches. I think all total was around 50gb. When I retired in 2010, there was over 600gb.

Some of the tricks of making the Application run Faster:
1) Strip out Commenting in the Production Version
2) Remove Code Nesting in VBA in the Production Version.
3) Remove as much Whitespace as possible in the Production Version
4) Convert the Production Version to an MDE/ACCDE.
5) Make VBA Code as re-usable as possible.
6) Avoid writing unnecessary VBA Code when the same function could be performed in an ACTION Query or Stored Procedure.
7) Make sure all VBA Code has an Error Handler and Logging System.
8) One of the biggest over-rated features in Access is the ability to create Class Modules. In my opinion, they are not worth the time and effort. I would only create them when I felt something could not be accomplished easier or faster.
9) Disable Auto-name Tracking and Logging in the production version.

Some of the Tricks of making an Application Smarter:
1) Implement a Global Variables Table that is maintained from the Server
2) Create VIEWS to see all System Objects in SQL Server connected to your FE
3) Create a method to automatically Link to Tables and Views, that will also create a Query and a basic Form. This will reduce your development time by 15 minutes for each.
4) Create a Chat System.
5) Reduce dependency on external Systems by integrating them into the Application.

I am a professional freelancer in my retirement... ha ha.... retirement. I do not have much time to comment in the forums. My skills are highly sought after. Since my retirement in 2010, I have earned more than double in just 12 years than I did in my 20 years before retiring.

Access is by far the most cost-effective software for just about any business. Even if a Customer pays $15,000 for an Application, it belongs to the Customer, it is not full of unnecessary bloat and has all the features that the Customer wanted, unlike most anything else that is purchased off the shelf of subscribed to.

Thats’s quite an impressive cv you build over the years. I have a client who is looking for a serialized bom inventory system. It’s a small manufacturing company wich produces only 6 (highly technical) products. Do you have any tips on setting up a serialized bom structure with components and parts?

 

[spaLOGICng]

Thanks!

While we had an archaic method for traceability. We at least had a Groups and Class of Items, whether they were purchased, manufactured as a sub-component, or as a finished or final product. If the item was purchased, we did have a cross-reference system for available suppliers.

The Group and Class of Items were really helpful because of the so many products that were manufactured or serviced. Groups and Classes were used to separate Furniture from Detergents, for example. A Sub Class could identify Wood from Composite Materials. We typically used a 3-digit code to represent each. Nevertheless, this is a form of serialization.

Since then, there has been an adoption of more streamlined methods, but the two accepted methods are either serialization or lot.

While your client has only 6 products, technical or not, I would still use a Group Class system for its sub-components. While not knowing the nature of the products, let's assume you manufacturing cryogenic products. This involves both purchased and manufactured components of precious metals, welding, hardware, etc. Different raw metals should be classed differently. Sub-components manufactured from those metals would inherit the group identifier, but then should be classed respective to what it is, i.e., a Tube versus a Tank versus a Pipe versus a Flange, etc.

You could have as many attributes as necessary to comprise the serial id. Take a VIN of a motor vehicle. It says a lot about the vehicle, not everything, but a lot. Back in old days, Auto Manufactures placed "Line Setting Tickets" in the glove box or under the hood. The ticket would list all the specifics about the vehicle in addition to the VIN. They still exist, but only at the OEM level, not placed in vehicles anymore. I think Ford has one of the most complex methods for serialization.

So, while the client only produces 6 products, do they envision expanding the product count or even across different industries? Those things need to be considered when developing a serial id to make the growth as easy as possible from a technical standpoint.

I hope that helps.

You can read here for better guidance: Building an Identification System | Traceability Solutions | KEYENCE America

 

[wiktorr45]

I have been in business for a long time and I have always been successful. Especially erp software dubai helps me. By the way, business in Dubai is a very popular thing now. And what do you think about business in Dubai? Is it possible that someone like me has been doing this for a long time? So how did you get started in this industry?

 

[prabha_friend]

Family Tree