What was/is your "largest" access project

[DenverDb]

been a few - some less successful such as web apps. Now we have dataverse, recent improvments to graphs (appeals to some, not others), update of the web control to name a few.

These two statements are incompatible


How do you plan to maintain it? You decide on a necessary improvement, perhaps as a result of upgrades to Access/Office/Windows, are you going to ask each customer to somehow return the single file (circa 2Gb) to you for you to make the change and return it to the customer and in the meantime, they can't use it?


what exactly? - just answered by Colin.

How do you plan to maintain it?

Strategy 1: Send the user new tables, queries, forms, and reports via email. New code will be embedded in the forms. The objects would be zipped.

Strategy 2: Have a database expert take over control of the user's PC.

Strategy 3: Overnite a replacement PC installed with the application. Let the user copy existing tables to the PC with the help of a help desk specialist.

Strategy 4: Dispatch a database expert to the site with a replacement PC.

How do you maintain your applications?

 

[DenverDb]

Really?
You amend the tables in one.
You amend the everything else in the other.

All done within Access.
Two files yes, but not twice the work.
Same work, just different files.

I suppose it is the concept of 'one' versus 'other' that gets to me. If there were only a few hundred tables and a few hundred forms/reports it would be one thing but 4100 tables, 4600 forms, and 3200 reports is overwhelming at times.

To amend a database module, I have to change a table, form(s), and report(S). So it is open-close, open-close, and open-close. I would love to know about a different way.

 

[Pat Hartman]

I learned from Mike's article that someone in the organization can change the data if he has access to a split database.

A user can change the table data directly if you have a single database also. You secure the database or you don't. You didn't learn the right thing from Mike's article. or all the Access experts AND Microsoft are wrong in their recommendation to always split the FE from the data and only you are correct. What are the chances of that?

How do you maintain your applications?

The developer keeps a master copy of the FE and that is what is in "production" now. The developer also keeps a development copy of the FE and that is what he is working on modifying. The files have different names to minimize confusion. The production version of the FE is linked to the production version of the BE. The development version of the FE is linked to a local copy of the BE which also has a different name from the production BE.

The developer makes changes to the FE. if there are no changes needed to the BE which is very likely in a properly normalized schema, then the previous master copy of the FE is zipped and archived. Only emergency changes are distributed during business hours. In that case, everyone has to log out of the application. I also temporarily rename the BE to prevent users from logging in during the few minutes where I have the database off line. The tested developers FE is renamed to the production name and relinked to the production BE. Then, it is moved to the distribution folder in the server and the BE is changed back to the production name. Email is sent to everyone that distribution is complete. Users open the shortcut and that causes the new version to be downloaded to their PC.

If the BE requires changes, the method for distribution will vary depending on how extensive the changes are. If I am adding new columns to existing tables or adding new tables, that doesn't affect any existing code or queries, I back up the BE and make the changes while the BE is off line. If the change will interfere with current processing, I create a version change database that includes DDL (Data Definition Language) queries to effect the changes. When it is time to distribute the changes, I do it off line whenever possible. Otherwise, I use the same method as I use for FE only changes but with the additional step of running the queries that modify the BE schema. And I run these against the renamed BE just to make sure that if anyone tries to log in while I am distributing the change, they get an error and can't log in.

There are situations where the changes are extensive enough that they warrant user testing. In that case there is a third version of the database that is distributed to specific users for testing. I frequently change the background color of the menu and the major forms to jog the user's memory so he doesn't forget that he is working with a test version of the FE. I do this automatically by checking the location of the BE database. If it is in the UserTesting directory or on my local drive, the color changes. Otherwise, I would need to keep having to manually change these things and that becomes a black hole of problems.

 

[CJ_London]

How do you maintain your applications?

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

Strategy 1: Send the user new tables, queries, forms, and reports via email.

- 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?

 

[Edgar_]

How do you maintain your applications?

I annoy my users:
1. Main form opens
2. HTTP request is made to a firebase realtime database
3. If highest build number is the same as current frontend build, do nothing, otherwise show update form
4. If update form shows up, it uses the link stored on the firebase database that was sent with the response
5. User clicks link
6. Google drive folder shows up in the browser, user downloads it and replaces it manually to get rid of the notifications

Could be improved. But that's for those who use the Access version of that particular app. For other apps, my users call me, I fix the bug or add the feature and then I hand them the new frontend with instructions to replace it. Many times I must do this via AnyDesk for a small fee.

 

[DenverDb]

Thanks Pat, that's the most detailed piece I have ever read about split databases. I need to print and tape it to the wall and put a copy in my wallet. Your terms are especially helpful: development FE, production BE.

Over the years, I have asked professional developers about the advantages of split databases, and I have received very little that I can get my head around. Most people just say split for multi-user applications, and that has no meaning to me because I developed the application mainly for single users.

And with the exception of using code for speech and animation, there is almost no code in my application. I used code 15-20 years ago, but when macros became more powerful, I pretty much abandoned the use of code.

A user can change the table data directly if you have a single database also.

Yes, that's why the database has so many forms. The keyword in your sentence is 'user.' I am only worried about other people who may have access to the application.

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

I create a version change database that includes DDL

There you are, agreeing with me, that's work. I am not about to create 10-20 DLLs every day. Thanks for the warning.

Access experts AND Microsoft are wrong...

What can I say about this? If I had developed a data-focused application I would agree but my application is a different beast, see Colin's comments above. George says the same thing about my application. I wonder how the experts and MS would have built this database. I hope they would have seen it my way if they understood what I am trying to do, why, and how.

Thanks again for your comments. They are very much appreciated.

 

[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?

So you send them empty tables

No. The idea is to only send users the objects they need. I could send a user a hundred tables today and another hundred tomorrow. Whatever is needed. Similarly, I could send a user a hundred forms today and another hundred forms tomorrow to replace existing objects.

I am sorry to say that I know nothing about DLLs. I have seen the term but I don't code.

the graphics look interesting

Thanks. Back in the 70s and 80s, I created diagrams to show the chemistry of volcanic rocks. I worked with an HP computer that I programmed using HP Basic. The graphics I create these days are fancier. I match them to various kinds of business data.

Thanks for the comments. They are much appreciated.

 

[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.

 

[Pat Hartman]

There you are, agreeing with me, that's work. I am not about to create 10-20 DLLs every day. Thanks for the warning.

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.

 

[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.