Reading books won't get you anywhere unless you do stuff that needs to be done with what you are reading about.
Does an Access query negate the benefits of utilizing SQL
- Thread starter gojets1721
- Start date
I should understand what the author is talking about to be able to experiment what he says.
Minty
AWF VIP
- Local time
- Today, 02:18
- Joined
- Jul 26, 2013
- Messages
- 10,522
@KitaYama I'm in agreement with @Galaxiom , unless you have a specific task to accomplish learning new stuff can be difficult.
We do almost all the processing of data on SQL Server because we are on Azure platforms, and the connectivity makes the performance acceptable. So we do create a lot of Views, to avoid doing multi-table joins in Access, it's simply more efficient in our environment.
In some applications, we have complicated queries with dozens of calculated fields, these are much quicker in operation when done on the server. We also make extensive use of row_number ranking and lag functions to produce financial reports, these are just not possible without creating inefficient sub-queries or even temporary tables in Access.
If your applications aren't needing these types of things at the moment then the advantages of using SQL probably aren't obvious at the moment.
We do almost all the processing of data on SQL Server because we are on Azure platforms, and the connectivity makes the performance acceptable. So we do create a lot of Views, to avoid doing multi-table joins in Access, it's simply more efficient in our environment.
In some applications, we have complicated queries with dozens of calculated fields, these are much quicker in operation when done on the server. We also make extensive use of row_number ranking and lag functions to produce financial reports, these are just not possible without creating inefficient sub-queries or even temporary tables in Access.
If your applications aren't needing these types of things at the moment then the advantages of using SQL probably aren't obvious at the moment.
- Local time
- Today, 02:18
- Joined
- Feb 19, 2013
- Messages
- 17,093
In my experience, transitioning from an access BE to a sql server (or other rdbms) BE for performance reasons alone is the wrong reason simply because for a well written FE and a well designed BE you won't get better performance, it can even be worse if the network is not particularly effective. I had a client who transitioned and then transitioned back because the sql server was prioritising other applications so the server resources available were poor to say the least and IT were not interested in investing in increased resources or redistributing the resources available.
A poorly written FE will suffer even more.
By all means transition for reasons of data storage volumes and enhanced security although I agree SQL Server does have some more efficient processes when required to manipulate large amounts of data. For one of my clients I developed a 'year planner view' of resource usage (people, places, equipment, type of usage and more). In Access this was achieved using multiple crosstabs and took around 6 or 7 seconds to load. This was migrated to sql azure because the CEO thought this was more 'modern'. Just linking to the sql azure tables and running the same multi crosstab query the time increased to over 30 seconds. I rewrote the query to take advantage of sql azure functionality and got the time down to 3-4 seconds. A slight improvement on the access original.
A poorly written FE will suffer even more.
By all means transition for reasons of data storage volumes and enhanced security although I agree SQL Server does have some more efficient processes when required to manipulate large amounts of data. For one of my clients I developed a 'year planner view' of resource usage (people, places, equipment, type of usage and more). In Access this was achieved using multiple crosstabs and took around 6 or 7 seconds to load. This was migrated to sql azure because the CEO thought this was more 'modern'. Just linking to the sql azure tables and running the same multi crosstab query the time increased to over 30 seconds. I rewrote the query to take advantage of sql azure functionality and got the time down to 3-4 seconds. A slight improvement on the access original.
- Local time
- Yesterday, 21:18
- Joined
- Feb 19, 2002
- Messages
- 45,431
You are entitled to your opinion of my opinion just as I am entitled to my opinion. I "find" because I talk to people to understand why they think they need to use pass through queries and unbound forms and the answer isn't performance because they have no other version to compare with so they have no clue whether or not linked tables and bound forms would perform adequately. It is usually because they want to control how/when records get saved. If they knew how Access worked, they would know how to prevent bad data from being saved.
Isaac
Lifelong Learner
- Local time
- Yesterday, 18:18
- Joined
- Mar 14, 2017
- Messages
- 9,907
Highly, highly recommend this book. Pay no attention to the reference to "2012".
Trust me, minor changes since then are fluff in comparison to the impressive, rock-solid knowledge this will give you.
This book is gold and if you mastered it you would be Joe Celko (without the unpleasant personality).
If you follow along with its examples, any required downloads and Northwind. If you just make any progress following this book's content, no matter how slowly, you should be very proud of yourself!
Covers enough to make an entire SQL Server development career.
Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 (MCSA) (Microsoft Press Training Kit): Sarka, Dejan, Ben-Gan, Itzik, Talmage, Ron: 9780735666054: Amazon.com: Books
Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 (MCSA) (Microsoft Press Training Kit) [Sarka, Dejan, Ben-Gan, Itzik, Talmage, Ron] on Amazon.com. *FREE* shipping on qualifying offers. Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 (MCSA) (Microsoft Press Training Kit)
www.amazon.com
@Isaac
I just wanted to thank you one more time for your suggestion. Though it took a long time, finally it arrived this morning.
Going through the index and several first pages, I can understand why you suggested it. Thanks again.
Edit:
I'm also disappointed at US Amazon. Now, I think I can believe whatever I saw in numerus clips on social media about the bad quality of delivery by US Amazon.
Last edited:
ChrisDaSwiss
New member
- Local time
- Yesterday, 18:18
- Joined
- Jan 22, 2018
- Messages
- 8
I don't like writing SQL in Access, SQL-Server (Management Studio) is much more comfortable. The "inteelligence" is on the server with us. And we usually fill the fields in access with vba, not link to actual tables.
- Local time
- Yesterday, 21:18
- Joined
- Feb 19, 2002
- Messages
- 45,431
You know, if people want to do considerably more work to use unbound forms and not use the RAD features of Access, I really don't care. In my opinion, an expert should know how to use his tools correctly and then use them that way. You don't use a screwdriver to drill a hole (although my husband did just that early in our marriage before we actually had a tool box). Just because you can avoid the best features of a tool doesn't mean that it is right and the "best" method. I do object to them pushing people who are clueless to avoid using the RAD features of Access. When Access can't do what you need to have done, it is time to move on to a new tool.
That said, there are reasons for using unbound forms (not updateable) and pass through queries and stored procedures, etc. They should just never be the STARTING point of any development effort. Access is quite capable of using SQL Server, Oracle, DB2, etc via ODBC even with millions of rows in the tables (assuming you actually understand how to use bound forms). If you like writing code instead of using a RAD tool, chose a different FE platform that has a smaller footprint.
That said, there are reasons for using unbound forms (not updateable) and pass through queries and stored procedures, etc. They should just never be the STARTING point of any development effort. Access is quite capable of using SQL Server, Oracle, DB2, etc via ODBC even with millions of rows in the tables (assuming you actually understand how to use bound forms). If you like writing code instead of using a RAD tool, chose a different FE platform that has a smaller footprint.
piano_playr
Member
- Local time
- Yesterday, 18:18
- Joined
- Oct 31, 2014
- Messages
- 30
@Pat Hartman is correct in pointing out the benefits of Access development. Having the ability to bind a dataset to forms and reports is a miracle. SQL Server is not a threat to Access. It cannot do what Access does. On the other hand, Access cannot do what SQL Server does. SQL Server is meant to manage enterprise data. It is capable of managing databases up to 524,272 terabytes in size. Access begins to exhibit bad behavior for anything over 200 mb. I know the documentation says the upper limit is 4 gb. Fine, let's say that's true. Please take note of the comparison, Access vs. SQL Server - 4 gb vs. 524,272,000 gb. Add to that the features of an enterprise database like the creation maintenance plans to automatically perform backups with options for full, incremental, or differential backups. SQL Server gives the enterprise an opportunity to maintain replicas of its database at the enterprise's disparate locations. There are many more SQL Server features that Access simply cannot do. Why? It's because Microsoft targets Access for end users along with the other application in the MS Office suite of products. It was never meant to be a tool for enterprise developers.
The current trend that dwarfs both Access and SQL Server are all the internet technologies. There are so many and I won't bore you by giving you a list. MySQL has long been a favored database platform for web apps. I have a feeling this forum stores its data in a MySQL instance. Frameworks like PHP, Python, C#, etc are needed to create backend functionality, Client-server applications have drawbacks. Remote desktop protocol (RDP) is needed to make these applications available to distant branches. One needs to work through setting up a WAN and much more to distribute a client-server application. In that sense, I think of Access of an end-user application (only) and SQL Server as a dbms for desktop client-server applications and web apps. They all have their purpose.
The current trend that dwarfs both Access and SQL Server are all the internet technologies. There are so many and I won't bore you by giving you a list. MySQL has long been a favored database platform for web apps. I have a feeling this forum stores its data in a MySQL instance. Frameworks like PHP, Python, C#, etc are needed to create backend functionality, Client-server applications have drawbacks. Remote desktop protocol (RDP) is needed to make these applications available to distant branches. One needs to work through setting up a WAN and much more to distribute a client-server application. In that sense, I think of Access of an end-user application (only) and SQL Server as a dbms for desktop client-server applications and web apps. They all have their purpose.
- Local time
- Yesterday, 21:18
- Joined
- Feb 19, 2002
- Messages
- 45,431
ACCESS DOESN'T DO WHAT SQL SERVER DOES AND SQL SERVER DOESN'T DO WHAT ACCESS DOES. Comparing the two shows no understanding of either tool.
You persist in comparing Jet and ACE to SQL Server. Go ahead but please, please stop calling them "Access". Jet and ACE are NOT Access. Jet and ACE are desktop database engines. They have a life outside of Access and can be used by any application development platform that supports ODBC. As such, they have significantly less power and reach than ANY major RDBMS. No one who knows anything about RDBMS would ever think that Jet and ACE are competition for SQL Server so why people persist in such comparisons is beyond me. Jet and ACE are intended to work on a LAN with a small group of users and a file size limited to 2G. Row count can still be in the millions but total file size has an absolute limit. User count has an absolute limit. Jet and ACE are NOT expandable beyond those limits. These desktop database engines are the best at what they do and are excellent database engines if your requirements do not exceed their specifications AND you don't have to actually secure your data.
Access is NOT a database engine. Access is a Rapid Application development (RAD) tool. Access is a platform for building APPLICATIONS NOT DATABASES. People are completely flummoxed by the relationship between Access and these two database engines because the relationship is tight, almost symbiotic. Access is totally dependent on Jet/ACE in order to store its application objects. This is a weakness, not a strength because it ties Access to Jet/ACE permanently and prevents it from ever creating an actual .exe file. Access does NOT require its application data to be stored using Jet/ACE. Access can use ANY RDBMS that supports ODBC. Jet and ACE are dependent on Access to provide a GUI tool similar to SSMS to build database objects interactively. Jet and ACE objects can be build with DAO or ADO scripts or by using MS Access as a GUI. Access was shipped without Jet in the box until version 2.1 Prior to that time, you needed to install Jet separately if it was not already installed.
piano_playr
Member
- Local time
- Yesterday, 18:18
- Joined
- Oct 31, 2014
- Messages
- 30
@Pat Hartman, you are hard to please. I compliment you and you dump on me. And you're meant to be a friendly moderator. 
I know all about Jet and ACE. They're built into the Access product. Whatever. Been that way for a long time. I was not comparing the two products. Reread my post. I was contrasting them. I hope you were preaching to others with your oft-repeated explanation of what Access is. Baby, I've heard it all before. I question your grasp of Access history. There was NO such thing as Access 2.1.
I know all about Jet and ACE. They're built into the Access product. Whatever. Been that way for a long time. I was not comparing the two products. Reread my post. I was contrasting them. I hope you were preaching to others with your oft-repeated explanation of what Access is. Baby, I've heard it all before. I question your grasp of Access history. There was NO such thing as Access 2.1.
Last edited:
GPGeorge
George Hepworth
- Local time
- Yesterday, 18:18
- Joined
- Nov 25, 2004
- Messages
- 2,458
"Access begins to exhibit bad behavior for anything over 200 mb. I know the documentation says the upper limit is 4 gb. "@Pat Hartman, you are hard to please. I compliment you and you dump on me. And you're meant to be a friendly moderator.
Two inaccurate claims in consecutive sentences. I have to admit that there is some ambiguity about the actual size where Access is not stable, but I question the claim that it is only 200 mb. I have several accdbs on my hard drive right now over that size. All are stable indeed.
I have never seen any statement anywhere that Access has a 4 GB upper limit. Stating that tends to undercut anything else said therefore.
I also fail to grasp the significance of the statement "There was such thing as Access 2.1." Perhaps you rushed over the actual statement Pat made?
" Access was shipped without Jet in the box until version 2.1 " Yes, indeed, there was such a thing, but that wasn't the point being made. The point being made was that "until version 2.1" JET was not integral to the product. I.e. in prior versions, JET was installed separately. Frankly I didn't actually acquire Access prior to 2.1 so I never had to deal with that, but questioning Pat on that basis is a reach.
- Local time
- Yesterday, 21:18
- Joined
- Feb 19, 2002
- Messages
- 45,431
@piano_playr I'm sorry I have to keep repeating what Access is. Some people just don't get it. They keep posting things comparing "Access" to SQL Server and other databases and Access is simply NOT A DATABASE. Virtually all the bad press "Access" gets is directed at Jet/ACE. The confusion has to stop somewhere. What better place than one where we actually know the difference between Jet and ACE and Access. Or do we?
This is a place where we provide solutions and advice regarding Access. It is important that we understand what our tools do. Otherwise, how can we give members correct advice. If you think that Access is a database engine then you keep telling people they can't use Access when they have too many users or too much data but they CAN use Access. They just can't use Jet/ACE.
All I asked was that when you are talking about Jet or ACE, you don't blame their failings on Access like everyone else does. This is a place where "we" actually know what "Access" is and what it isn't. Comparing Access to SQL Server is just plain wrong. It's like comparing a car to a couch. They both start with c and you can sit on them. That doesn't mean that one can substitute for another. I could pan SQL Server because it doesn't support bound forms. Does that make sense? Well it doesn't make sense to pan Access because it can't hold as much data as SQL Server either. Especially since that is a total misstatement because when Access is linked to SQL Server, it CAN support databases of that size (if you know how to use bound forms and queries).
Access can support thousands of concurrent users and gigabytes of data. Just not when linked to Jet/ACE. You wouldn't use Access to support thousands of users and gigabytes of data because there are better tools that are easier to distribute and more suitable for large scale development. Access' big limitation is that it is suitable for single developer projects only and that makes it not useful for really large applications. It takes an enormous amount of discipline to have more than one developer at a time working on an Access application. In addition, the bright shiny thing these days is the cloud and Access can't play there by itself. It needs Citrix or something similar. So, pan Access if you must for the things it does badly. NOT for the things it doesn't do at all.
This is a place where we provide solutions and advice regarding Access. It is important that we understand what our tools do. Otherwise, how can we give members correct advice. If you think that Access is a database engine then you keep telling people they can't use Access when they have too many users or too much data but they CAN use Access. They just can't use Jet/ACE.
All I asked was that when you are talking about Jet or ACE, you don't blame their failings on Access like everyone else does. This is a place where "we" actually know what "Access" is and what it isn't. Comparing Access to SQL Server is just plain wrong. It's like comparing a car to a couch. They both start with c and you can sit on them. That doesn't mean that one can substitute for another. I could pan SQL Server because it doesn't support bound forms. Does that make sense? Well it doesn't make sense to pan Access because it can't hold as much data as SQL Server either. Especially since that is a total misstatement because when Access is linked to SQL Server, it CAN support databases of that size (if you know how to use bound forms and queries).
Access can support thousands of concurrent users and gigabytes of data. Just not when linked to Jet/ACE. You wouldn't use Access to support thousands of users and gigabytes of data because there are better tools that are easier to distribute and more suitable for large scale development. Access' big limitation is that it is suitable for single developer projects only and that makes it not useful for really large applications. It takes an enormous amount of discipline to have more than one developer at a time working on an Access application. In addition, the bright shiny thing these days is the cloud and Access can't play there by itself. It needs Citrix or something similar. So, pan Access if you must for the things it does badly. NOT for the things it doesn't do at all.
piano_playr
Member
- Local time
- Yesterday, 18:18
- Joined
- Oct 31, 2014
- Messages
- 30
My mistake. The theoretical upper limit is 2 GB. Here is the cite. https://support.microsoft.com/en-us...gigabytes, minus the space,be as large as 2GB.
Really, my intention was to contrast the theoretical capacity of each database engine. The JET/ACE capacity is minuscule contrasted to SQL Server. It’s neither good nor bad that SQL Server’s capacity dwarfs Jet. It just does. Different platforms, different uses. MS Access (Jet) is intended for workgroup productivity. MS SQL Server is meant for enterprise applications. That was my point.
Really, my intention was to contrast the theoretical capacity of each database engine. The JET/ACE capacity is minuscule contrasted to SQL Server. It’s neither good nor bad that SQL Server’s capacity dwarfs Jet. It just does. Different platforms, different uses. MS Access (Jet) is intended for workgroup productivity. MS SQL Server is meant for enterprise applications. That was my point.
- Local time
- Yesterday, 21:18
- Joined
- Feb 19, 2002
- Messages
- 45,431
I don't have any objection to comparing Jet/ACE to SQL Server. But you can't call Jet/ACE "Access" when what you are talking about is the database engine. That's all. Access is NOT a database engine and calling Jet/ACE "Access" simply confuses people into thinking that "Access" is bad. Access is NOT limited to 255 users or 2G of data. PERIOD. Those limitations are strictly Jet/ACE and you were blaming the limits on Access.
I would never argue that SQL Server wasn't head and shoulders "better" than Jet/ACE feature wise. Almost all of my applications use SQL Server as the BE. Some start out as Jet/ACE and get converted later. Others never get upsized. Others use other RDBMS' from the start like Oracle or DB2. And they all work fine with bound forms and tables of millions of rows. You have to know how to use your tool to make that happen though.
I would never argue that SQL Server wasn't head and shoulders "better" than Jet/ACE feature wise. Almost all of my applications use SQL Server as the BE. Some start out as Jet/ACE and get converted later. Others never get upsized. Others use other RDBMS' from the start like Oracle or DB2. And they all work fine with bound forms and tables of millions of rows. You have to know how to use your tool to make that happen though.
- Local time
- Yesterday, 20:18
- Joined
- Feb 28, 2001
- Messages
- 28,687
Just to clear the air here... JET/ACE is not limited to 2 GB. It is limited to 2 GB per individual file. In fact, with an FE file and exactly 1 BE file, you can theoretically reach 4 GB (though I definitely agree that as you approach 2 GB in ANY Access file, performance starts to get dicey pretty quick.) Given that you can have not less than 16 files (and I'm having trouble finding the actual upper limit in the online documentation), you could have close to 32 GB under control of an Access+ JET/ACE configuration. If so, better have a hot CPU, a lot of RAM, and a fast disk such as an SSD. And the most efficient backup system you can find.
Of course, if you DO split up your data that way, you end up with the inability to push relational integrity across the multiple files. There is also some history in this forum of having "resource" issues when the number of simultaneously open files starts going up.
As to whether there was an Access version 2.1, a web search does not easily confirm its existence. But I know there was a 2.0 - and the next "official" version of Access was released with Office 95, which I believe normalized everything at version 7. If someone claims to have had a beta release of the version that followed v2.0 and it was called 2.1, I won't deny the possibility. However, I think it is more likely that the numbering beyond 2.0 was actually SP (service pack) numbers, which got up to 2.50.0127 for version 2 of Access (according to FMS web sites that I could find.)
Of course, if you DO split up your data that way, you end up with the inability to push relational integrity across the multiple files. There is also some history in this forum of having "resource" issues when the number of simultaneously open files starts going up.
As to whether there was an Access version 2.1, a web search does not easily confirm its existence. But I know there was a 2.0 - and the next "official" version of Access was released with Office 95, which I believe normalized everything at version 7. If someone claims to have had a beta release of the version that followed v2.0 and it was called 2.1, I won't deny the possibility. However, I think it is more likely that the numbering beyond 2.0 was actually SP (service pack) numbers, which got up to 2.50.0127 for version 2 of Access (according to FMS web sites that I could find.)
