Does an Access query negate the benefits of utilizing SQL (1 Viewer)

[CJ_London]

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.

 

[Isaac]

I've been trying hard with no significant progress. The first book I bought was too sophisticated that I had to stop trying following the writer in the middle of the first chapter. The second one is not helping more than the first one.

Can you guide me to a good start point? Any on-line reading material, helpful site, blog, book whatever is OK.
Yes, I know I can google it. But I prefer to hear it from someone experienced rather than myself picking something random.

Thanks.

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

 

[KitaYama]

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.

 

[ChrisDaSwiss]

Full disclosure, I'm completely new to SQL so I apologize if this is a silly question.

I am using SQL as a backend and access as a front end. In general, does using a query to filter data for a form negate some of the speed and performance of SQL?

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.

 

[KitaYama]

And we usually fill the fields in access with vba, not link to actual tables.

If you mean unbound forms, I hope @Pat Hartman doesn't see your post.

 

[piano_playr]

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

 

[piano_playr]

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

Access was shipped without Jet in the box until version 2.1

 

[GPGeorge]

Spoiler: etc

@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 such thing as Access 2.1.

"Access begins to exhibit bad behavior for anything over 200 mb. I know the documentation says the upper limit is 4 gb. "

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.

 

[CJ_London]

think this is just two responders saying much the same thing, they are both on the same side of the line, just saying things slightly differently

 

[piano_playr]

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.

 

[The_Doc_Man]

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

 

[isladogs]

Hi Pat
Please can you give me the source for the image in your last post. I would like to include it in one of my Web articles. Thanks

 

[The_Doc_Man]

Colin, I found an image like that under a web search for "Access Version History" and some side-wise finagling.

 

[isladogs]

OK. A quick search led to Microsoft Access - Wikipedia

 

[isladogs]

I realised it was different but its actually even better for what I wanted.