4600 forms, 3200 report, is a real Access file monster
2 Gb for the file, but wich part for data (table) and how many Mbyte for form, report, macro, module?
At this time I have one ACCDB file with all of the tables, queries, forms, and reports in it. One day I may split it into a front end and a back end. I won't do any splits until I have separated the database into multiple files by domain. Currently I am the only user and I learned in the past few days about a security problem with split databases that concerns me.
I really have no interest in managing both a FE and a BE. In my opinion, it would be a waste of my time and effort. I make too many changes to the tables, forms, and reports to waste time on FE and BEs.
And I am working with three Bodies of Knowledge. The project management is the largest BOK and I have a lot more to do with it, particularly with regards to Agile project management.
I really have no interest in managing both a FE and a BE. In my opinion, it would be a waste of my time and effort. I make too many changes to the tables, forms, and reports to waste time on FE and BEs.
The real limit is exactly 2^31 bytes, or 2,147, 483,648 bytes, which is half of the address space available on a 32-bit address bus. (In fact, because of the memory layout of compiled Windows tasks, Access reserves the high half of the address space for any DB requirements because the low half includes MSACCESS.EXE code, various mapping tables, stack space, heap space, and room for library references.
Access WILL NOT grow to expand their address space to because if they did, Microsoft would have two different products in competition with each other, and the SQL Server competitor EASILY would win any legit competitions with the JET/ACE engine. Access was designed as a small business tool that could transition to become the front end to their high-end SQL product. There is no profit in expanding the address space. It lacks some features that are found in SQL Server precisely because MS wants to avoid the competition in the first place and (gently?) shove you towards their mainstream product.
Using a split database requires two files. To me that means twice as much work and managing a split database could also mean using special tools. I don't use and I don't want to use anything other than Access.
Using a split database requires two files. To me that means twice as much work and managing a split database could also mean using special tools. I don't use and I don't want to use anything other than Access.
For the benefit of those unfamiliar with David's work (@DenverDb), it is a huge and very idiosyncratic database which contains a lot of features that most developers avoid such as MVFs and TTS. David uses a lot of graphics in his forms and also takes pride in using as little code as possible.
Several of us have spent a lot of time and effort trying to convince David to split the database over the past few years but we go around & around in circles.
David is (wrongly) convinced it is extra work and is VERY unlikely to change his opinion no matter how many of us explain why it would significantly benefit his application, both during development & once it is completed and released.
@DenverDb (David)
You claim that an article by Mike Wolfe described .... "a security problem with split databases" that concerns you
I assume you are referring to this article: Microsoft Access vs. SQLite: Security Comparison (nolongerset.com).
If so, I think you have significantly misunderstood the point of the article. Access can never be 100% secure because it is a file based system.
The security is definitely not weakened by splitting a database. In fact, it becomes slightly more secure - partly by separating the end user from the backend data tables stored on the network
However, the data would be much more secure using a RDBMS such as SQL Server
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
I really have no interest in managing both a FE and a BE. In my opinion, it would be a waste of my time and effort. I make too many changes to the tables, forms, and reports to waste time on FE and BEs.
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?
I learned in the past few days about a security problem with split databases that concerns me.
Using a split database requires two files. To me that means twice as much work and managing a split database could also mean using special tools. I don't use and I don't want to use anything other than Access.
No special tools, Access does this natively. To split the database, you just do this: Ribbon > Move Data > Access Database > Split Database
Done.
Your users are going to have problems if all of them use the same file! Oh, but you as the developer will have the biggest problems.
Just give each team the frontend required. If this is for a construction company, for example, you can give a specialized frontend to:
construction team: construction progress, contractors management, material consumption, etc
costing team: budget control, estimates, cost analysis, etc
human resources team: contracts, social security, leaves, payrolls
And all of those teams could point to the same database and not have the same tables available.
You will see no benefit until you have active users working with your app and you need to maintain it while they're working. Trust me, you don't want to wait for disaster, your client probably knows no better, but you should.
Colin said it correctly. I have a real problem with the concept of a split database.
I learned from Mike's article that someone in the organization can change the data if he has access to a split database. I don't want that to happen.
In other words, I do not want my database to be used by multiple people. That's not what all other developers want when they develop a database. I want one person to own the database and for that person to generate reports that are distributed as PDFs. That's totally contrary to everything we try to achieve with Access.
I have my reasons for thinking this way. It is mainly about the information being company sensitive and so no one other than the owner should have access to it. In some cases, the data will be classified and in other cases, exposure could result in jail time. These are situations that 99.999% of Access developers never have to deal with. Sometimes PCs used to store the data will be housed in a SCIF where access is limited to specific people. Again, probably 99.999% of Access developers have never been inside a SCIF.
And you don't want to hear about a friend going to jail. Been there, done that.
As I have indicated many times in the past, I will not split my database without getting a signed affidavit from the client. I occasionally suggest that I may split just to keep people off my back. And I am not about use SQL Server, DB2, Oracle, or Informix.
Going "around & around' means that folks refuse to understand that I am not splitting.
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?
I am still working on it, so I am fluid. The only thing that I don't want to deal with is splits. I have a partner who wants us to use them. We are like Steve Jobs and Steve Wozniak.
Colin said it correctly. I have a real problem with the concept of a split database.
I learned from Mike's article that someone in the organization can change the data if he has access to a split database. I don't want that to happen.
In other words, I do not want my database to be used by multiple people. That's not what all other developers want when they develop a database. I want one person to own the database and for that person to generate reports that are distributed as PDFs. That's totally contrary to everything we try to achieve with Access.
I have my reasons for thinking this way. It is mainly about the information being company sensitive and so no one other than the owner should have access to it. In some cases, the data will be classified and in other cases, exposure could result in jail time. These are situations that 99.999% of Access developers never have to deal with. Sometimes PCs used to store the data will be housed in a SCIF where access is limited to specific people. Again, probably 99.999% of Access developers have never been inside a SCIF.
And you don't want to hear about a friend going to jail. Been there, done that.
As I have indicated many times in the past, I will not split my database without getting a signed affidavit from the client. I occasionally suggest that I may split just to keep people off my back. And I am not about use SQL Server, DB2, Oracle, or Informix.
Going "around & around' means that folks refuse to understand that I am not splitting.
You built an app on top of an insecure platform to be used within a very secure environment. I don't know, man, this is not going to end well for you, it's almost like storing money under my mattress.
You built an app on top of an insecure platform to be used within a very secure environment. I don't know, man, this is not going to end well for you, it's almost like storing money under my mattress.
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?
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.
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.
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 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
Code:
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
Code:
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
Code:
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?
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.
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
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.
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 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
Code:
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
Code:
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
Code:
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?
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.
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.
