Is Microsoft Access a 'proper' database?
- Thread starter Jon
- Start date
speakers_86
Registered User.
- Local time
- Today, 05:53
- Joined
- May 17, 2007
- Messages
- 1,919
Is it proper? Define proper.
It is great for some things. I would say it is great for small businesses, for rapid solutions, and to teach people how to code. Outside of this, other solutions are probably better.
It is great for some things. I would say it is great for small businesses, for rapid solutions, and to teach people how to code. Outside of this, other solutions are probably better.
RainLover
VIP From a land downunder
- Local time
- Today, 21:53
- Joined
- Jan 5, 2009
- Messages
- 5,041
No I haven't. I am semi retired so what I can't do in 2003 or 2007 does not have any real interest to me.
- Local time
- Today, 04:53
- Joined
- Feb 28, 2001
- Messages
- 29,188
I'm going to toss another stink-bomb into this conversation because in reviewing, I saw a statement that just ain't so. The comment was made that you might not want to use Access to host a database because of speed considerations. Speed vs. what?
Most systems I know do not compile SQL, they just semi-compile it into some emulation code that looks like, but is not, machine code - because there is no physical machine behind it with that instruction set and also because the "instructions" of this putative machine are not primitives - they are complex macro-instructions - like SELECT or INSERT INTO or UPDATE or DELETE that affect whole groups of data at one time. No, SQL is at most interpreted (not compiled) on even the ORACLE systems. So that leaves us with the question of "which brand-name DBMS interprets text faster?" That depends more on the amount of overhead in each interpreter than on the language being interpreted.
Both Access and ORACLE use tables with indexes for faster lookup. ORACLE's advantage isn't speed, here - it is SIZE. The indexes that "point" to data can be more than 2 Gb of equivalent address space. But the bigger address space actually SLOWS DOWN the way the "big boy" jumps around - because it has to manipulate that longer address a bit more - and in a way that depends on the O/S. The speed of searching, selecting, and JOIN operations depends on the efficiency of the search, selection, and JOIN code routines behind the scenes. ORACLE might do more (in the way of triggers and such), but if you want to compare apples to apples, you turn off the extra stuff before you run comparision operations. Odds are, Access will be able to keep up.
Both Access and the "big boys" use ANSI SQL so they interpret the same features. Whether you name something as a VIEW or a SELECT QUERY, it has the same purpose and still needs to be interpreted. The "interpretation" depends on the efficiency of the pseudo-code emulator. (More about that kind of speed in a moment.)
When you design forms or reports, you make lists of items that are visited by an image driver to draw text boxes, labels, combo boxes, toggle buttons, etc. This is common to both systems. When you use Access FE/BE or when you use VB FE/something else BE, you interpret each page / pane / screen of data - because you DON'T hard-code every control. You build a data structure, define the properties, and call subroutines with subject data structures as the actual arguments, and the subroutines (which ARE compiled) examine the object properties in order to display it. You don't use VB to directly draw any control. The "collection enumeration" algorithms in Access do the same thing - visit the collection of controls and dispatch to the correct drawing routine based on the control-type field. This sounds like I am belaboring the obvious - but I am wading through the weeds to make the point that the various methods every system uses all match up one for one at this level of detail. It is all more or less the same thing being done by different vendors' code. Where is the speed difference?
The "speed difference" between Access and the "big boys" is a chimera - a non-existent creature. Think about it: When you think about fast databases, you do you think about Access hosted on someone's PC? but do you at the same time think about "the big boys" on a multi-processor high-end dedicated server? The speed difference is in the platform and the O/S settings, not in the databases. I've seen the "big boys" run on small PCs and they are no faster than Access would be on the small PCs. When you take away the ability of the BE to use parallel processing, you see the REAL comparison and there is where you find the real speed differential, which is miniscule.
In closing, remember that whether you write in FORTRAN, BASIC, PASCAL, COBOL, P/L-1, or some glorified assembler, it takes the about the same number of compiled instructions to move data from point A to point B regardless of your environment. So when you want to tout the speed of your database system, remember that it is the platform, not the software, that gives you the appearance of speed.
Having said that, I acknowledge that if you ran Access on the hottest PC you could find, it would still be single-threaded because it was not written to use multiple CPUs or cores (unless it now does that and I just missed the advent of that option.)
I think I've become a sadistic sodomistic necrophile, because I'm pretty sure I've beaten this horse to death. (Apologies to Woody Allen and What's up Tiger Lily?) But I just couldn't let the "speed" chimera out there without challenging it.
Most systems I know do not compile SQL, they just semi-compile it into some emulation code that looks like, but is not, machine code - because there is no physical machine behind it with that instruction set and also because the "instructions" of this putative machine are not primitives - they are complex macro-instructions - like SELECT or INSERT INTO or UPDATE or DELETE that affect whole groups of data at one time. No, SQL is at most interpreted (not compiled) on even the ORACLE systems. So that leaves us with the question of "which brand-name DBMS interprets text faster?" That depends more on the amount of overhead in each interpreter than on the language being interpreted.
Both Access and ORACLE use tables with indexes for faster lookup. ORACLE's advantage isn't speed, here - it is SIZE. The indexes that "point" to data can be more than 2 Gb of equivalent address space. But the bigger address space actually SLOWS DOWN the way the "big boy" jumps around - because it has to manipulate that longer address a bit more - and in a way that depends on the O/S. The speed of searching, selecting, and JOIN operations depends on the efficiency of the search, selection, and JOIN code routines behind the scenes. ORACLE might do more (in the way of triggers and such), but if you want to compare apples to apples, you turn off the extra stuff before you run comparision operations. Odds are, Access will be able to keep up.
Both Access and the "big boys" use ANSI SQL so they interpret the same features. Whether you name something as a VIEW or a SELECT QUERY, it has the same purpose and still needs to be interpreted. The "interpretation" depends on the efficiency of the pseudo-code emulator. (More about that kind of speed in a moment.)
When you design forms or reports, you make lists of items that are visited by an image driver to draw text boxes, labels, combo boxes, toggle buttons, etc. This is common to both systems. When you use Access FE/BE or when you use VB FE/something else BE, you interpret each page / pane / screen of data - because you DON'T hard-code every control. You build a data structure, define the properties, and call subroutines with subject data structures as the actual arguments, and the subroutines (which ARE compiled) examine the object properties in order to display it. You don't use VB to directly draw any control. The "collection enumeration" algorithms in Access do the same thing - visit the collection of controls and dispatch to the correct drawing routine based on the control-type field. This sounds like I am belaboring the obvious - but I am wading through the weeds to make the point that the various methods every system uses all match up one for one at this level of detail. It is all more or less the same thing being done by different vendors' code. Where is the speed difference?
The "speed difference" between Access and the "big boys" is a chimera - a non-existent creature. Think about it: When you think about fast databases, you do you think about Access hosted on someone's PC? but do you at the same time think about "the big boys" on a multi-processor high-end dedicated server? The speed difference is in the platform and the O/S settings, not in the databases. I've seen the "big boys" run on small PCs and they are no faster than Access would be on the small PCs. When you take away the ability of the BE to use parallel processing, you see the REAL comparison and there is where you find the real speed differential, which is miniscule.
In closing, remember that whether you write in FORTRAN, BASIC, PASCAL, COBOL, P/L-1, or some glorified assembler, it takes the about the same number of compiled instructions to move data from point A to point B regardless of your environment. So when you want to tout the speed of your database system, remember that it is the platform, not the software, that gives you the appearance of speed.
Having said that, I acknowledge that if you ran Access on the hottest PC you could find, it would still be single-threaded because it was not written to use multiple CPUs or cores (unless it now does that and I just missed the advent of that option.)
I think I've become a sadistic sodomistic necrophile, because I'm pretty sure I've beaten this horse to death. (Apologies to Woody Allen and What's up Tiger Lily?) But I just couldn't let the "speed" chimera out there without challenging it.
Is it good enough or should companies be using something else?
It is a very good database for a small businesses, after when your database grows nothing deters to you to migrate toward SQL Server, or MySQL. I love very much Microsoft Access, as I learnt Database, how to build it with Microsoft Access 97. Moreover, it has useful tools and you could use Microsoft Excel.
- Local time
- Today, 10:53
- Joined
- Feb 19, 2013
- Messages
- 17,228
Haven't reread the whole thread but access is two things - a back end database and a front end development environment. The front end does not need to use the access backend, it can use pretty much any rdbms.
with regards only suitable for small companies I completely disagree. With a couple of exceptions all my clients are national or multi national and typically the apps developed fill the gaps the corporate systems can't, usually because of budget or resource constraints.
That is not to say there aren't other options out there that could be used. But one of the benefits of using access is development time. Because many of the controls are preformed they only need the minimum of configuration.
One of the potential disadvantages of access is the app is client based (the program resides on the client machine) rather than host based (the program resides on the server). So maintenance of the FE becomes a bit more problematic if you have 600 user machines to update with a revised FE - note 600 assumes the BE is sql server or similar.
Another is the FE really needs to be hardwired to the BE rather than using wireless. But there are ways round that. In some respects it can be seen as an advantage because the requirement for hardwire simplifies potential security issues.
with regards only suitable for small companies I completely disagree. With a couple of exceptions all my clients are national or multi national and typically the apps developed fill the gaps the corporate systems can't, usually because of budget or resource constraints.
That is not to say there aren't other options out there that could be used. But one of the benefits of using access is development time. Because many of the controls are preformed they only need the minimum of configuration.
One of the potential disadvantages of access is the app is client based (the program resides on the client machine) rather than host based (the program resides on the server). So maintenance of the FE becomes a bit more problematic if you have 600 user machines to update with a revised FE - note 600 assumes the BE is sql server or similar.
Another is the FE really needs to be hardwired to the BE rather than using wireless. But there are ways round that. In some respects it can be seen as an advantage because the requirement for hardwire simplifies potential security issues.
- Local time
- Today, 04:53
- Joined
- Feb 28, 2001
- Messages
- 29,188
amo12oo - My personally biggest db was for the U.S.Navy (from which I retired as a contractor after 28 1/2 years). About 30-40 users, data set was basically to record all security-related actions for over 1500 servers crossing 80 projects. Security actions were distributed by the government every 2 weeks and might lead to 30 actions per month, multiply that by 1500 servers and we were tracking 45,000 actions per month, or half a million actions per year. Those 80 projects were essentially Navy, Navy Reserve, and a couple of other government agencies that we managed from a data center. While that isn't the biggest db you might have ever seen, if you are worried about the size of the organization, it was the U.S. Navy. Not small potatoes.
But that wasn't the biggest Access db at the site. Another person managed the one in which they had an Access FE and an SQL Server BE for a Navy medical-records system that potentially covered the entire U.S. Navy through Access as the front-end driver. Don't downplay Access as a "small systems" tool for small shops.
But that wasn't the biggest Access db at the site. Another person managed the one in which they had an Access FE and an SQL Server BE for a Navy medical-records system that potentially covered the entire U.S. Navy through Access as the front-end driver. Don't downplay Access as a "small systems" tool for small shops.
ButtonMoon
Registered User.
- Local time
- Today, 10:53
- Joined
- Jun 4, 2012
- Messages
- 305
So that would be a SQL Server database, not an Access database. The question here is apparently about Access databases and not about applications developed in Access.
isladogs
MVP / VIP
- Local time
- Today, 10:53
- Joined
- Jan 14, 2017
- Messages
- 18,614
Agree with the comments by CJL and the DocMan.
I have several databases which have run in a number of large secondary schools for many years. Typically there may be 200 users per school and at peak times there can be over 100 of those running an app simultaneously without issues e.g. doing attendance registers where all classes are done at the same times
To manage large numbers of users, the backend data is in SQL Server. This ensures a stable and secure environment without slowdowns or crashes. After 10+ years, one school will in the next year or so need to make a decision about upgrading as they are getting fairly close to the 10GB limit for the free Express version.
EDIT
I also have many other databases with a Access BE. If the number of simultaneous users is no more than 20 or so, this is perfectly good solution. More than that will cause slowdowns and occasional crashes which no longer happen using SQL Server for the BE.
I have several databases which have run in a number of large secondary schools for many years. Typically there may be 200 users per school and at peak times there can be over 100 of those running an app simultaneously without issues e.g. doing attendance registers where all classes are done at the same times
To manage large numbers of users, the backend data is in SQL Server. This ensures a stable and secure environment without slowdowns or crashes. After 10+ years, one school will in the next year or so need to make a decision about upgrading as they are getting fairly close to the 10GB limit for the free Express version.
EDIT
I also have many other databases with a Access BE. If the number of simultaneous users is no more than 20 or so, this is perfectly good solution. More than that will cause slowdowns and occasional crashes which no longer happen using SQL Server for the BE.
NauticalGent
Ignore List Poster Boy
- Local time
- Today, 05:53
- Joined
- Apr 27, 2015
- Messages
- 6,657
Amo12oo has spent some time answering old threads with insignificant, one sentence answers. I’m an only guess at the reason. Time will tell...
- Local time
- Today, 04:53
- Joined
- Feb 28, 2001
- Messages
- 29,188
ButtonMoon, my take is that if Access is the FE, then the GUI is good enough for big boys in the database industry. And that much of it IS proper. But it is true that we used SQL server because of the security issues as well as capacity issues.
Then again, since Access was intended for small audiences, there is nothing wrong with having to up-convert it once you got the design right. And the fact that you COULD convert it means that it wasn't flawed. Just limited.
Then again, since Access was intended for small audiences, there is nothing wrong with having to up-convert it once you got the design right. And the fact that you COULD convert it means that it wasn't flawed. Just limited.
SachAccess
Active member
- Local time
- Today, 16:23
- Joined
- Nov 22, 2021
- Messages
- 391
I agree with you.
