It Just Works
-
Comments posted to this topic are about the item It Just Works
-
I think that a large part of the reason why Access won't die is because it has everything it needs and you don't actually have do any programming to use it. It has a database. It had a GUI development tool. It has a reporting tool. It easily interfaces with Excel (and we all know that despite all of the innovations in this world, the real world is based on "Shadow IT" and their Excel spreadsheets). It can import and export spreadsheets, Real CVSs, Real TSVs, and data from the files of about a bazillion other tools and databases.
If you get into some of the functionality, you'll find out things like it has a "PIVOT" bit of functionality that makes SQL Server's Pivot functionality look more like an accidental bed wetting rather than smart planning and implementation. There are a lot of similar aspects to Access.
I've even seen it used as a very rapid POP tool (Proof of Principle) for the design of GUIDs and the related data flow.
There are only two reasons why Access seems to have problems, and sometimes does. The first is, MS is coasting on the product. They're not really spending any time on it because, like the title of this article states, "It Just Works" and... it fills a niche that MS might not want to spend a whole lot of time on.
I also believe that the other reason why it seems to have problems is for the very same reason that a lot of people have problems with T-SQL and SQL Server. They don't even know what they don't know never mind what they may be missing.
I think that Microsoft would be making a big mistake if they ever decide to not support it in the future. To me, it would be like outlawing paint brushes just because someone thinks they can do more with a spray gun. Different tools and, sometimes, different an even very specialize people for different things.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
For small operations, that is, fewer than 5 people using it concurrently, Access (or FileMaker Pro) is excellent. It's relatively cheap and easy for someone with some experience to set up, usually free and usually neither too difficult nor too expensive to find someone to help with problems, which don't really tend to occur unless someone has been messing with it. My mother works in a surgeon's office and the office uses Access to manage customer data.
My favourite combination, though, is an Access front-end to an SQL Server backend (and often SQL Server express edition). We have that at work so that team-leaders can enter in their relevant monthly statistics. It was reasonably quickly set up (within a week or so during normal work) and we DBAs look after the backend DB. It's not especially glamorous but it works and rarely gives trouble.
- Jeff Moden wrote:
If you get into some of the functionality, you'll find out things like it has a "PIVOT" bit of functionality that makes SQL Server's Pivot functionality look more like an accidental bed wetting rather than smart planning and implementation.
True.
I learned Access 2.0 and the app I wrote was of such use that the company I worked for sent me on the two SQL Server 6.5 courses to be able to upgrade it. It launched my SQL Server career.
I've seen commercial apps that are principally read-only that use Access as a back end with no problems whatsoever. It's concurrent read/write that presents the problems.
The beauty of Access is that it lets a non-IT person say "look, let me show you what I want". It can be a great conversation facilitator. For an Access developer the VBA programming language had a good object model and was easy to learn.
As Jeff said, everything you needed for a data driven application was in the product.
- Jeff Moden wrote:
If you get into some of the functionality, you'll find out things like it has a "PIVOT" bit of functionality that makes SQL Server's Pivot functionality look more like an accidental bed wetting rather than smart planning and implementation. There are a lot of similar aspects to Access.
I've even seen it used as a very rapid POP tool (Proof of Principle) for the design of GUIDs and the related data flow.
Quite so.
Where I've found it really useful is developing something to transform legacy data from awkward, mixed ODBC driver type sources into a database under development. That's a one shot task but you need to be able to repeat and refine it, maybe breaking tables apart and using supporting tables to get the right results in the target db. Being able to develop functions in VBA means this is a quick to implement task and generally you don't mind if it's a bit slow. I find it like a sort of database Swiss army knife.
I've developed SQL backed Access front end databases that work with 40 odd users that have been going for many, many years and perform their function very reliably. It's not actually super easy to do that well but it's certainly an option.
-
As someone who started their database existence in Access, I wholeheartedly agree with the sentiments above.
When I started looking at SQL Server as an actual replacement for Access - rather than just a back end for a database application - a whole lot of questions arose. How did you implement linked tables? If you didn't want to use Access for the front end, what development environment was best? Visual Studio, I suppose, but which language? VBA.NET? C#? Which object-relational mapping tool? Which middleware technology - ODBC, or OLE DB, and why? If you wanted to get data into or out of SQL Server there were several approaches too. (I quickly got fed up with trying to use SSIS. It was nice and visual, and sophisticated - and tended to break with an detailed yet inscrutable error message about 95% of the way through my particular process. Some combination of network, file rights permissions and not-quite-rounded-off development of SSIS, was what it felt like. I am just about ready to give it another chance, but my experience so far has been pretty unrewarding.) All in all, a whole string of new learning curves.
To sum up, Access "feels" like one product. There are several technologies, but they are well integrated. SQL Server, on the other hand, feels more like an archipalego of products that will happily work together provided that a number of sometimes-hard-to-discover conditions are fulfilled.
Pehaps I am being unfair. Concurrent database access and update is an inherently hard problem to solve well, and SQL Server does mostly make a seriously good job of it.
I have to say I am very thankful for sqlservercentral, and for the ethos of willing mutual assistance to be found there.
MarkD
- sean redmond wrote:
For small operations, that is, fewer than 5 people using it concurrently, Access (or FileMaker Pro) is excellent. It's relatively cheap and easy for someone with some experience to set up, usually free and usually neither too difficult nor too expensive to find someone to help with problems, which don't really tend to occur unless someone has been messing with it. My mother works in a surgeon's office and the office uses Access to manage customer data.
My favourite combination, though, is an Access front-end to an SQL Server backend (and often SQL Server express edition). We have that at work so that team-leaders can enter in their relevant monthly statistics. It was reasonably quickly set up (within a week or so during normal work) and we DBAs look after the backend DB. It's not especially glamorous but it works and rarely gives trouble.
I enthusiastically agree with your last paragraph here. On the first, though, we have used it on some fairly large stuff (if you consider 30 or so users to be "fairly large") with, as you say, some edition of SQL Server as the backend.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - call.copse wrote:
I've developed SQL backed Access front end databases that work with 40 odd users that have been going for many, many years and perform their function very reliably. It's not actually super easy to do that well but it's certainly an option.
Agreed and that's actually one of the points I brought up as to why some people look down their noses at it. It's just like any software. You do have to learn to use it properly to use it well. Too many people try to use it "intuitively" and either never realize the full potential of the product or end up in deep Kimchee because they don't know what they're doing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
you guys have it easy - I grew up on FoxPro and now I have maybe inherited a few hundred users connecting to SQL using an access client (it worked when we were smaller) - but I've seen first-hand how MS access can hit you hard
the first 3 high CPU items (on the left) are all access - 2 and 3 are all where it is constantly refreshing object definitions or checking permissions
the permission checks are really bad - if you are a sysadmin it doesn't really check.. but if you are a normal user in multiple security groups... it just sucks
I guess that my main sql server has 50% traffic that is not functional for the end user.. just noisy traffic
MVDBA
-
I love Access, and always have, but I will admit to it having two critical problems that killed it, both Microsoft's fault.
First, Access stores its built-in security system passwords in the clear. You can download password revealers for Access off the web. (I nearly had a heart attack when I first found that out).
MS knows about this and REFUSES to fix it because they want to sell you SQL Server for beaucoup bucks. (Multi-thousands vs a couple of hundred.) From an MS bean counter POV this is a no-brainer.
Second, the Jet engine does not scale because it's file based not server based like SQL Server. This too is a deliberate design decision. MS could easily integrate the free version of SQL Server into Access as a drop in replacement (API identical) for Jet--but they made the $$$ decision not to.
As a result the 100k line Access application that ran our SMB had issues with approximately 30 concurrent users. As a result we replaced it with a VB.Net/SQL Server which does basically the same job, only it takes 695k lines to do it, split evenly between VB.Net and SQL Server. Oh, and while the Access app took 2 years to develop (and refine over the following decade) the replacement took *9* years to develop (admittedly while doing other stuff too). Keep in mind our company has exactly 1 person for IT, development and everything else.
From a developer standpoint Access STOMPS Visual Studio. It seamlessly integrates forms, sub-forms, reports (and sub-reports), a programming language, database development tools, and more. If MS had done with SQL Server what they'd done with Jet we'd be saying "Oracle who?".
And while purists may scoff at any form of Basic, VBA was a wonderful version of Basic. Coded correctly, it was not slow, our old Access application had sub-second response times, even for most reports.
I mourn having to move on. SQL Server *is* far more scalable, no question. You can throw tons of hardware at it and it will take advantage, where Jet couldn't. It's far more secure, yes, and stored procedures are certainly easier to secure than tables.
But T/SQL *sucks* as a language, and having to learn two distinct languages to do a single project is insane.
Ah well, the road not taken and all that...
-
I agree with Jeff:
I worked with Access way back when. I've used it as a database, as a front end to a SQL database, and as a quick development/modelling tool.
Put simply: it's useful; it's easy to learn; it's easy to deploy; it works.
The only fly in the ointment is it's a bit too simple and too tempting at times. That and when the database gets corrupted... most often the DB is on a PC somewhere and not backed up...
Yup, I've also been called in to fix an Access DB that got corrupted more than once...
Fun times!
-
I started in Access too. Its SQL version of queries was a great assistance in learning to write queries in T-SQL, though the formatting is atrocious.
Conversely, learning SQL made me better in Access because it made it clear why some things didn't work as well as they should, particularly how it makes HAVING criteria if you limit on a field with a GROUP BY.
Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am. - roger.plowman wrote:
But T/SQL *sucks* as a language, and having to learn two distinct languages to do a single project is insane.
Wow. One can do nearly everything except build windows (forms) in T-SQL. In Jet, however, one can barely write a SELECT * FROM table query.
I guess it's a question of preferences....
- John N Hick wrote:roger.plowman wrote:
But T/SQL *sucks* as a language, and having to learn two distinct languages to do a single project is insane.
Wow. One can do nearly everything except build windows (forms) in T-SQL. In Jet, however, one can barely write a SELECT * FROM table query.
I guess it's a question of preferences....
And the Jet engine sucks at evaluating CSV imports - "hey lets evaluate 200 rows and try and figure out our data types" - to be fair DTS and SSIS are just as bad, i'm not sure - but I think DTS on sql200 was based on the Jet 4.0 engine
but as for 2 different languages for a single project.. gotta disagree - I use T-SQL where it fits the right solution. I use C# and when I need to do some crazy stuff then I might switch to unmanaged C++ to write a CLR.. then I need to maybe write a monitoring system and need to understand basic MVC
We're recently using R (which I hate) and if you ever have to use MDX you will cry
Then It's not all the little foilbles of each language (do I use DIM or declare) but if you ever have to code in multiple languages - polish, French, English and german with the occasional bit of Russian then comments are not your friend.
especially Spanish - TODO means "all" - don't go hunting round to find all of the "to do's" 🙂
MVDBA
- John N Hick wrote:roger.plowman wrote:
But T/SQL *sucks* as a language, and having to learn two distinct languages to do a single project is insane.
Wow. One can do nearly everything except build windows (forms) in T-SQL. In Jet, however, one can barely write a SELECT * FROM table query.
I guess it's a question of preferences....
Um, what? 🙂
In terms of SQL language (as opposed to flow control and other non-SQL elements) Jet supports nearly as rich an array of language elements as SQL Server does.
This includes all the basic SQL clauses, joins, etc. In addition you can include VBA functions in the select itself. And VBA functions in SQL are *fast*, unlike functions in T/SQL.
In addition to SQL you also have the ability to directly access data via one of the ODBC methods or DAO or ADO...
You can even create pass-through SQL to talk to SQL Server or other databases. So I'm not sure why you say Access's SQL language is limited.
The only element that's really missing from Jet is stored procedures. Which I admit make security so much simpler.
T/SQL, on the other hand is glacially slow for anything that isn't DML or DDL. Which is why Jeff Moden always rants on RBAR. 🙂
Viewing 15 posts - 1 through 15 (of 70 total)
You must be logged in to reply to this topic. Login to reply