What was/is your "largest" access project

[The_Doc_Man]

As I have said, splitting it is out. Ask Colin. It just ain't going to happen on my watch.

Yes it will... IF you realize that by splitting the DB, you can break the 2 GB barrier in virtuo IF there are sufficient points of division between the tables.
If you have two non-overlapping subsets of tables that never have to involve a relationship between them, then you can have multiple back-ends, EACH of which can be over 1.8 Gb. AND the FE can also get big, with your "thousands of reports and forms." But by splitting, you negate the problem of space occupied by forms and reports that take away from table space in a monolithic database - because you can have up to 16 DB files linked together - 1 FE and a bunch of back ends. Meaning that with careful splitting, and if the app allows it, you can reach as many as 16 x 1.8 Gb or about 28.8 Gb total space. (This is an idealized limit.) No table could ever exceed 2 Gb either way you cut it. You cannot have a single query with a recordset bigger than 1 Gb (according to the Access specifications) so your queries will necessarily limit your size. Other limits tell me that you really don't have much code in your system because you can't have more than 1000 modules - and that includes class modules associated with forms and reports, which you say number in the thousands as well.

And as to commercializing this monster, I will say "good luck to you" but anyone who understands database design KNOWS that you need to split things up because that gives you the ability to optimize layouts and minimize data loss. If multiple disks are available, splitting the back ends over more than one disk allows you to have parallel seek latency.

The only MAJOR thing you lose if you split a database into multiple back-ends is that you cannot establish a relationship between two tables in different files, because the system relationship table doesn't have a "back-end" number in it. Which means no relational integrity between tables in different files. But with thousands of tables, I would have trouble imagining that ALL of them have RI enabled anyway.

 

[DenverDb]

I send an updated FE - if changes to table design/relationships etc in the BE are required I include code that runs on first start to update the BE using DDL - obviously the code includes routines to make backups before that code runs, just in case something goes wrong. I have a method which is a script to create a BE, be it Access or SQL Server. It is just a series of DDL statements and append queries and a relatively simple vba function that executes them in turn.

If you are interested, take a look at my Access Studio - you can download it from Colin's website

Access Studio

Access Studio adds the functionality of SQL Server Management Studio to Access. Used as a reference library, it allows users to quickly switch between Access apps, use formatted SQL, run parts of more complex SQL code, add comments, see results in the same development window etc.

isladogs.co.uk

This is some DDL code from one of the sample templates provided- it creates a very simple table that can be used as a counter. Each line is run separately or create a simply bit of vba code to run it all in one go

CREATE TABLE   usysCounter ( num long);
    INSERT INTO usyscounter (num) VALUES (0);
    INSERT INTO usyscounter (num) VALUES (1);
    INSERT INTO usyscounter (num) VALUES (2);
    INSERT INTO usyscounter (num) VALUES (3);
    INSERT INTO usyscounter (num) VALUES (4);
    INSERT INTO usyscounter (num) VALUES (5);
    INSERT INTO usyscounter (num) VALUES (6);
    INSERT INTO usyscounter (num) VALUES (7);
    INSERT INTO usyscounter (num) VALUES (8);
    INSERT INTO usyscounter (num) VALUES (9);

The vba code would be along the lines of

dim sql() as string
dim i as integer

sql=split(DDLScript,";")
for i=0 to ubound(sql)-2
    currentdb.execute sql(i)
next i

then create the following query to to generate a number list from 0 to 9999

SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Counter]
    FROM usysCounter AS singles, usysCounter AS tens, usysCounter AS hundreds, usysCounter AS thousands;

May seem like lot of work but using code you can deconstruct an existing table to generate the appropriate DDL. and you can easily get the sql code for a querydef - That is something that will be in Access Studio Professional


- So you send them empty tables - what happens to their existing data?
and if fully populated because they don't add or change data you have provided - have you tried sending a 2gb file via email? You might want to look at FTP or similar file transfer methodologies.

I'm intrigued by your app - the graphics look interesting but trying to understand the concept of your offering when you sell it. If you are providing a stand alone machine, it presumably does not interface with the clients existing corporate systems and is designed for a single user?

Sorry, my neighbor came to the door and I didn't finish responding to you.

trying to understand the concept of your offering

My application is an end-to-end proposal management system. The screenshot below is a form that shows part of the proposal management lifecycle that is built into the application. Each button launches a different form. Each form is a main form and a subform. The main forms have buttons that launch other forms and report/subreport pairs. The process is circular. Users can view/use almost 8000 forms and reports that have customized graphics and speech.

The optimum situation would be for my company to provide the database and the PC to customers. In other words, it would be System-as-a-Service. Special features include Failure Modes and Effects Analysis (FMEA), OODA Loop (Observe, Orient, Decide, Act), Six Sigma Pricing, and Contract Protests, to name a few. Again, take a look at Modern Access Designs on LinkedIn.

 

[DenverDb]

Yes it will... IF you realize that by splitting the DB, you can break the 2 GB barrier in virtuo IF there are sufficient points of division between the tables.
If you have two non-overlapping subsets of tables that never have to involve a relationship between them, then you can have multiple back-ends, EACH of which can be over 1.8 Gb. AND the FE can also get big, with your "thousands of reports and forms." But by splitting, you negate the problem of space occupied by forms and reports that take away from table space in a monolithic database - because you can have up to 16 DB files linked together - 1 FE and a bunch of back ends. Meaning that with careful splitting, and if the app allows it, you can reach as many as 16 x 1.8 Gb or about 28.8 Gb total space. (This is an idealized limit.) No table could ever exceed 2 Gb either way you cut it. You cannot have a single query with a recordset bigger than 1 Gb (according to the Access specifications) so your queries will necessarily limit your size. Other limits tell me that you really don't have much code in your system because you can't have more than 1000 modules - and that includes class modules associated with forms and reports, which you say number in the thousands as well.

And as to commercializing this monster, I will say "good luck to you" but anyone who understands database design KNOWS that you need to split things up because that gives you the ability to optimize layouts and minimize data loss. If multiple disks are available, splitting the back ends over more than one disk allows you to have parallel seek latency.

The only MAJOR thing you lose if you split a database into multiple back-ends is that you cannot establish a relationship between two tables in different files, because the system relationship table doesn't have a "back-end" number in it. Which means no relational integrity between tables in different files. But with thousands of tables, I would have trouble imagining that ALL of them have RI enabled anyway.

Thanks.

Let me say this in a different way: if you offered me a million dollars tomorrow, I would not split the application. I have been saying this for years.

 

[Edgar_]

Thanks.

Let me say this in a different way: if you offered me a million dollars tomorrow, I would not split the application. I have been saying this for years.

Don't split it. There are apps not meant to be split. The ones I have seen have an export/import functionality for when there's a change in versions.

 

[The_Doc_Man]

Thanks.

Let me say this in a different way: if you offered me a million dollars tomorrow, I would not split the application. I have been saying this for years.

Let ME say this in a different way. When this project of yours gets big enough, you will have two choices... abandon or split to make room. Status quo will not be a 3rd choice. This is not to downplay your abilities, but to simply apply two predictive rules I have found to be the case.

Rule 1: Living, evolving projects grow indefinitely.

Rule 2: The address space of a single Access file does not grow indefinitely.

You keep on going and I wish you luck, but your reluctance is likely to get you into trouble at some point.

 

[Josef P.]

I am just wondering how I would organize 4600 forms in my application to keep track of them.
I think I would need quite long form names for this.

On the subject of security and splitting:
An Access application with tables in it is just as insecure as a spitted application.
During development, I believe David that an unsplit application is easier to use with so many tables.
However, when the application is shipped, I find it hard to imagine how an update will run without splitting to keep the data.
You can't put modified or new forms or reports into an accde. But an accde is a must if the application is to be reasonably protected.
Then the only thing left to do is to transfer all necessary tables (or the data in them) of the old accde to the new accde.

 

[NauticalGent]

If your app is limited to a single PC, in other words no portion of it is on a LAN (either NIPR or SIPR), than splitting it and keeping the BE on the same PC would be the Exact. Same. Thing.

The benefits are many, the risks (in your case) are non-existent.

 

[DenverDb]

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.

Sorry, HillTJ, that my comment led the conversation astray, but you mentioned a split DB.

I started working on my DB circa 2005, and I have grown it since into a comprehensive business development application for different industries. In the early days, I would package it on Fridays and email it to my counterpart on the other side of the US for his use. In later years, I put it on a server for others to use in an unsplit configuration.

I can't say how many records there are now because of the number of tables, many of which are lookups used by the multi-value fields. Some of the objects have as many as 10 MVFs. In my opinion, they are one of the best things in Access. They have limitations; they don't work well with text-to-speech and certain calculations. But I can add an MVF in about 55 seconds without using any code.

The other thing about Access is that I can quickly and easily add detailed graphics to the forms and reports. Probably half of the forms and a third of the reports in my application has a graphic. I use them to brand the application but, more importantly, to help users understand the information that will enter into the application. I then use speech to give the user confidence in using the information for decision-making purposes.

High-level statistics, such as logistic regression, is another thing that Access can do well. All the developer needs to do is understand the statistical model(s) and have test data at hand.

Finally, you can link to any website on the net. My application launches YouTube and plays my favorite music. Years ago, I stored geospatial data in Access, and I used the data in Intergraph GeoMedia and ESRI's ArcView. I have also connected PowerBI to data in my Access databases and launched MS Teams from my application.

My graphics are created in PowerPoint and then copied into Access using Snip&Sketch. Two years ago, instead of using PowerPoint for a presentation for Access DevCon, I used Access. I don't use Excel but once or twice a year and then only as a calculator, so all of my math is done in Access forms and reports.

Access' big value is that it stores all the data and information a person needs to make good business decisions. If a person wants to develop a robust decision support system, then Access is the way to go. It is inexpensive and easy to use because of all the Access textbooks and resources such as Utter Access and Access World. And the many user groups such as Access Pacific, Access Europe, Access LunchTime, Denver Area Access Users Group, Access DevCon, and Access Eastern Time help folks learn how to use the tool. I am not too big on YouTube videos, but sometimes they are also very useful.

 

[jdraw]

DenverDb,

Because of your familiarity with MSAccess and preference for Macros and MVFs, you might consider a presentation to one of the User Groups. Macros and MVFs are frowned upon by many. As one who uses them extensively in such a focused application, your knowledge and experience on this topic could be helpful and a reference to many.
In addition, the sheer size of your application 4000+ tables, forms and reports is rare, and would make for an interesting presentation.

 

[DenverDb]

DenverDb,

Because of your familiarity with MSAccess and preference for Macros and MVFs, you might consider a presentation to one of the User Groups. Macros and MVFs are frowned upon by many. As one who uses them extensively in such a focused application, your knowledge and experience on this topic could be helpful and a reference to many.
In addition, the sheer size of your application 4000+ tables, forms and reports is rare, and would make for an interesting presentation.

Thanks jdraw.

I gave a presentation to DAAUG about MVFs circa 2008. One developer in the group tried them. He said later that he abandoned using them for some reason that escapes me.

Last year I gave presentations to Access LunchTime and Access Pacific about my use of graphics and Kanban boards in Access. If you are interested, both presentations were recorded and posted to YouTube by AUG.

If your applications are not designed to manage projects then the Kanban presentation won't be of interest to you. Many people use apps in the cloud for Kanban boards. I don't do the cloud.

The graphics presentation won't be of interest if your users are not visual thinkers. Some say 30% of the population is visual thinkers and others say 80%. If your applications do not use graphics then you may be keeping information from folks.

I frequently show examples of my forms and reports on in my LinkedIn group (Modern Access Designs). One of my LinkedIn threads is solely about MVFs. My way of adding MVFs to an application uses main tables to store information. For example if you need to assign multiple functions or services to a project, a MVF works great.

Similarly, if you have 100 people in a department and you only need to assign a few of them to a team/project then a MVF is good way to do that.

As I mentioned above, I can add an MVF to an object in less than a minute.

A limitation with MVFs is that you can only have 100 items in a list. MS may have increased that number recently but I have never needed more than 30 items in a list.

The most MVFs that I ever had in an object is ten. Unfortunately I can't show the form with all of those MVFs displayed at the same time.

Yes, some developers do not like MVFs because when MS introduced in A2007 the company did not explain them to folks and so developers thought they broke Codd's rules. After MS explained how they worked most developers still had a problem with what had been hidden from them. I tried to read Codd's paper once but it is over my head. I have never had a problem with MVFs and I have been using them since 2007. But understand, I don't code and I don't write queries. Over the past two decades I have found other ways that work great for me.

One thing about MVFs that I love is having the ability to show items one way in a form and a different way in the related report. In forms, I usually set the Separator Characters property to System Separator. In reports I set the Separator Characters property to New Line.

System Separator uses commas to separate items on each line. New Line expands to place each item on a different line in a report. These property setting allow me to conserve space in forms. My reports then to automatically expand downward to space out the items in MVFs. No code is needed, these are simple property settings.

I also like the checkboxes. They are automatically placed in forms.

Hope this helps. If you try using MVFs in your applications may need to adjust to how they work. After three attempts, you should have them down pat. You are welcome to ask me about them on LinkedIn.

I am still learning to create macros in Access. I use them mostly to open forms and reports. I love to replace all of the code in my application with macros. But once again, I am not your typical developer.

 

[DenverDb]

Why are you modifying tables every day? And how with others using the database? As I mentioned earlier, your schema is obviously not normalized and that is what is causing the need for all the forms and reports as well. Seems more like a collection of spreadsheets masquerading as tables.

Reasons for Splitting the database
1. minimizes the potential for corruption. When you have a monolithic application, all users must open the same physical file. When Access opens, parts of the database are loaded into memory on their PC. As other procedures are executed, other parts get loaded. Access has a tendency to save things. That would mean that all the other users have a different version of the object. If one of the users has a problem that locks up their PC, it can affect everyone because their lock on the file could prevent another user from doing something.
2. It makes distributing changes to the FE that do not affect the BE trivial. You just replace the FE with the new version. The change is totally transparent to the users unless they were waiting for the change. It happens automatically when they next open the database.
3. Allows you to make changes to the application without having to worry about disturbing production data. This is pretty important. When you are updating a live database no one else can be in the database so you must be disrupting the users frequently. You also must have trouble testing because some changes update data and all you have is production data.

Normalizing your application to reduce all the moving parts to more "normal" counts would probably be a big job. But splitting the database is pretty trivial. Access renames the exported tables and leaves them in the FE. Once you have tested and are happy with the change, then delete the old tables and compact the FE ro regain the space. If you split on your local PC, the link will be to a local location. You will need to move the new BE to a server location and relink to that version. Then you have a one time effort to set up the distribution method for the FE. You can look here for options. I've posted the very simple batch file that I use. I create a shortcut to run the app. The shortcut runs the batch file which is saved on the server in the same folder as the master copy of the FE. The batch file downloads a copy of the FE. Once this is in place, all you need to do to distribute a new FE is to back up the old master and replace it with the new version. The next time the users run the shortcut, they automatically get the new FE. To be sure things are connecting right, I add a version table to the BE and a local version table to the FE. When the FE opens, it compares the two version tables. If they are the same, then the app opens normally. Otherwise, display an error and close.

So, distribution is pretty easy. Or you can use one of the database options you will find here that do the same thing. in that case, the shortcut would open the update database. It would check the versions and open the app or not depending on the versions.

Once the DB is split, you can use the same method you currently use to make BE changes whatever it is. I make a separate database to update the BE only for clients where I am not on site and need them to do the update for me. I don't embed the code in the new FE because that will cause the first person to open the database to initiate the changes. I prefer to do that in a controlled situation where I know that no other user has the current BE open. For changes where I am on site, I still might make the DDL queries for my own convenience but I run them myself when I update the BE. For minor changes that don't affect anything that is currently working, I manually make the changes during off hours. ALWAYS after backing up the database.

One day, your database is going to stop working and who knows if you will lose data as part of the collapse. At that point, you will be forced to split the database but you'll be doing it under duress.

Thanks Pat,

1. Is not applicable because this is a one-user application.
2. Is not applicable because this is a one-user application.
3. Is not applicable because this is a one-user application.

The next paragraph reads like work to me.

No code.
No server.

I see absolutely no reason to do a split, especially when I need to create 40 new tables, 10 new forms, and 10 new reports each day.

Yes, the application will stop working one day, so I am off to Office Depot to buy another case of USB sticks for the backups

 

[DenverDb]

I am just wondering how I would organize 4600 forms in my application to keep track of them.
I think I would need quite long form names for this.

On the subject of security and splitting:
An Access application with tables in it is just as insecure as a spitted application.
During development, I believe David that an unsplit application is easier to use with so many tables.
However, when the application is shipped, I find it hard to imagine how an update will run without splitting to keep the data.
You can't put modified or new forms or reports into an accde. But an accde is a must if the application is to be reasonably protected.
Then the only thing left to do is to transfer all necessary tables (or the data in them) of the old accde to the new accde.

Yes, Josef P,

My object names are fairly long. They usually tell me the primary, secondary, tertiary and sometimes the quaternary domains that are used in them.

Occasionally, I need numbers for subreports used in Kanban boards.

Thankfully, I foresaw the need to do this decades ago.

I haven't decided yet but there may not be any ACCDEs. That sounds like work to me.

 

[jdraw]

Very interesting info and special application.

Last year I gave presentations to Access LunchTime and Access Pacific about my use of graphics and Kanban boards in Access. If you are interested, both presentations were recorded and posted to YouTube by AUG.

I googled and searched various AUG on youtube, but could not find a presentation by you or the subject Kanban. Do you have a direct link?
Again, since you are "so into" MVFs and macros, and I know various AUG hosts are seeking presenters, I think your application and its focus on these would make a useful presentation. It could be an update to your previous works.

 

[DenverDb]

And what are you going to do when Access stops working because the app is too big? The answer - you're going to split the db because you have no choice.

Apparently you think it is normal to create 40 new tables and 10 new forms and 10 new reports each day. What would you do with your time if you didn't have to do that. You should realize by now that what you are doing is absolutely unnecessary and it is only cognitive dissonance that is keeping you from rethinking the process so that data becomes rows rather than columns and you can use queries to select the period you want to report on.

Pat, I am separating the application now into different domains. So far, I have created domains for Pricing, Award Protests, and Business Metrics. The last domain that I will probably separate out is Proposals. It will be the last one because it is core to most of the others.

It probably is not normal to create this many tables, forms, and reports each day, but I do it often after I read an article in HBR or MIT Sloan. I can't think of anything better to do. I don't like talking and sports. And these days, TV is one commercial after another.

Because of the pandemic, I don't leave the house that often. My wife has gone on vacation twice without me.

 

[DenverDb]

Very interesting info and special application.

I googled and searched various AUG on youtube, but could not find a presentation by you or the subject Kanban. Do you have a direct link?
Again, since you are "so into" MVFs and macros, and I know various AUG hosts are seeking presenters, I think your application and its focus on these would make a useful presentation. It could be an update to your previous works.

That's interesting jdraw. I don't use Google so I can't help you. Try checking the AUG website. Alternatively, ping George Hepworth and Maria Barnes.

 

[isladogs]

I did warn everyone back in post #52!
All the comments made in subsequent posts have been made repeatedly in numerous threads at UA forum over the past two years.

 

[DenverDb]

I would love to see examples of folks' forms if they have embedded graphics and MVFs.

I think I might have had some small impact. @DenverDb is splitting the database. He is using data for the split which is of course wrong and a lot more trouble than doing it correctly, but it was on the edge of failing and the split will at least resolve that problem

No Pat, I am "separating" the application into 25 domains. I will never "split" it. End of story.

 

[DenverDb]

I did warn everyone back in post #52!
All the comments made in subsequent posts have been made repeatedly in numerous threads at UA forum over the past two years.

Yes, Colin, but they ignored you. As you know all too well, I will never split.

 

[isladogs]

I think I might have had some small impact. @DenverDb is splitting the database. He is using data for the split which is of course wrong and a lot more trouble than doing it correctly, but it was on the edge of failing and the split will at least resolve that problem

Sorry to disillusion you but David had already decided to do that before he started posting here: Separating an Access database

 

[Josef P.]

I am "separating" the application into 25 domains. I will never "split" it.

Do I understand you correctly: you want to create 25 individual database files (split by domain, each file has tables, forms, etc.)
Tip: Look at the concept of Access add-ins. You could then open them from a main application and basically have only one application open.
With one restriction: the forms cannot be used as subforms between add-ins.

Note: Since we're pretty much undermining the thread topic "What was/is your "largest" access project" here in my opinion, please open a new thread if you want to discuss the topic of sharing with add-ins.