July 21, 2014 at 8:28 am
Dalkeith (7/18/2014)
I personally like MS Access. Its sweet spot is definitely complicated internal systems with limited distribution and users but that covers a great deal of systems.I would prefer to use SQL Server as the back end and just use Access for UI design.
I also believe it is an excellent tool for teaching database design principles and UI design principles.
It is probably undervalued as a UI design tool.
I'd have to agree. There was a lot of power built-in, if you only know where to go to tap into it. Interestingly enough there were also a lot of tricks to "dummy-proof" applications, if you only went looking for those: the ability to use external MDE's as data and UI "libraries" made it easy to deploy ongoing changes to everyone, without giving the end-users unfettered access to every product table.
Of course - you had to WORK to leverage some of these items: it's easier to build a giant security violation thant is it not to 🙂
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 21, 2014 at 8:32 am
Matt, I think that's true of a lot of tools. Few have security baked-in in a way that leads/forces users to build something secure. Security is almost always something you have to know to do, rarely something you're prompted for. Imagine if every create table launched a security settings dialog - maybe the world would be a safer place!
July 21, 2014 at 12:08 pm
Agreed! Any time you're in a dev tool, you can ultimately make something secure or insecure.
That said - I do think that some of the newer VS improvements (especially in some of the "drag and drop" type UI controls) would tend to yield a somewhat higher level of security than those you get out of the comparable wizards in Access. Might be a good time to revisit those functions in Access and "help" the users along more; If you believe the adage that devs by default will follow the quickest and easiest path, simply making it harder to make something NOT secure would increase the overall security of the application 🙂
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 21, 2014 at 1:07 pm
GoofyGuy (7/17/2014)
The problem I have with Access isn't Access itself, but the way in which non-IT types use it building their own data stores and 'apps': without any knowledge of good IT practises, nor IT oversight.
I'm also an Access MVP but I wanted to comment on this point in particular. I totally agree with it. And therein lies the problem. IT in particular has had a disdain for Access, for just that reason, because it takes control away from them. But the answer is NOT to disdain Access, but to embrace it. To allow and support its use by controlling how its used.
The days when a user can come to IT with an application request and IT can take it under advisement and return a 12-18 month time frame for implementation are gone and IT needs to realize it. If they tell the user this, the user can think well I can build this in Access in 12-18 DAYS. And so what do you think they are going to do?
As many here have acknowledged Access is a great tool, when used properly. With best practices forced on the user and IT oversight, it can be a very workable tool that can rapidly develop applications that can make workers more productive.
Scott<>
July 21, 2014 at 1:21 pm
scottgem (7/21/2014)
GoofyGuy (7/17/2014)
The problem I have with Access isn't Access itself, but the way in which non-IT types use it building their own data stores and 'apps': without any knowledge of good IT practises, nor IT oversight.
IT in particular has had a disdain for Access, for just that reason, because it takes control away from them. But the answer is NOT to disdain Access, but to embrace it. To allow and support its use by controlling how its used.
Scott
In an ideal world, I'd agree with you, Scott.
The problem is, it's not an ideal world - particularly when business users 'develop' in Access, without first informing IT. This happens even to engaged, valued IT departments - not just those sorry ones which lost the business users' trust and confidence.
Ergo, it's a little hard to control how it's used. Our IT director has let other department heads know that if they want to use Access without our support, they're on their own.
July 21, 2014 at 1:27 pm
GoofyGuy (7/21/2014)
scottgem (7/21/2014)
GoofyGuy (7/17/2014)
The problem I have with Access isn't Access itself, but the way in which non-IT types use it building their own data stores and 'apps': without any knowledge of good IT practises, nor IT oversight.
IT in particular has had a disdain for Access, for just that reason, because it takes control away from them. But the answer is NOT to disdain Access, but to embrace it. To allow and support its use by controlling how its used.
Scott
In an ideal world, I'd agree with you, Scott.
The problem is, it's not an ideal world - particularly when business users 'develop' in Access, without first informing IT. This happens even to engaged, valued IT departments - not just those sorry ones which lost the business users' trust and confidence.
Ergo, it's a little hard to control how it's used. Our IT director has let other department heads know that if they want to use Access without our support, they're on their own.
The organization that I am working with has had so many access issues that they have banned any new work in access, will not be installing access on any new images and have told current access users that they are now on their own for assistance.
The org wants everyone off of access ASAP due to the years of headaches caused by access (really caused by lax Management/IT and staff with no IT training trying to build access apps).
July 21, 2014 at 2:08 pm
I'll start with the disclaimer - I am, like Armen, a MS Access expert and a former Access MVP so I am somewhat biased. I came to Access from a COBOL/CICS/IMS/DB2 background and thought I had died and gone to heaven. I read through this thread and am pleased to see that not all of you hate Access. However, it is clear from many of the comments that Access is not well understood.
The first point I would like to make is that you are condemning the wrong piece of software. Access isn't a database engine, Jet (mdb) and ACE (accdb) are the database engines. Jet and ACE are the competitors of SQL Server, not Access. Access is a rapid application development (RAD) tool that is used to create applications. It can keep its data in any ODBC compliant database or even a spreadsheet or text file, not just Jet and ACE and it compliments SQL Server rather than competing with it. The confusion occurs because Access, the RAD tool, requires Jet/ACE to hold its OWN objects. Jet and ACE act as the container for the forms/reports/queries/macros/code you write to create an application but the actual application data can be stored anywhere. If you create a web-based application, you are not linking to an Access database no matter what you think. You are linking to Jet or ACE and Access may not even be installed on the server. If it is, it is only there to work as the "SSMS" to the database so you can define tables and relationships. The same goes for VB, VB.Net, Java, etc. Only the developer would ever need "Access" and then only to work as the "SSMS". The clients would have only Jet/ACE installed. SQL Server is not a complete solution, Access is because Access includes the ability to create an application as well as to store data. SQL Server only stores data although it does have a reporting tool.
The second point is one that others have also made. The problem is not Access per se but with how it is used. Some of that we can blame on MS and how they market Access. They sell it as a tool for dummies. Of course, if a dummy creates an application, you get something really dumb. However, if a professional creates an application, you get something that works, is efficient, and is stable and probably easily convertible to a "real" RDBMS. I always build my apps with client/server in mind so the conversion rarely takes more than an hour.
The third point is how "Access" processes queries through ODBC. The ODBC driver makes every attempt to pass through EVERY query to the server. That is a stated objective. The user can defeat this by not understanding some minor technical points. One of them is that SQL Server and other RDBMS' do not understand VBA and so any function that does not have an equivalent in the target RDBMS must be processed locally. So depending on which clause in the query contains the VBA or UDF function, the ODBC driver may request entire tables to be brought down from the server for local processing. The other big issue is filtering. In a typical Access app based on Jet/ACE, forms are bound to tables or to queries without any selection criteria. The app then uses filters to reduce the data shown to the user. When switching from Jet/ACE to ODBC, it is imperative that the forms be bound to queries with criteria that select the minimum columns/rows to satisfy the user request so the technique for populating a form is slightly different. Taking an old style Access app and upsizing to SQL Server will in almost all cases result in the app being slower. The only way to take advantage of having a "real" server BE is to make the server do the heavy lifting and the key to that is queries with criteria. If I send a query to the server requesting a single record, that is all the server will ever send back!! That point seems to be muddy in the minds of most "Access" haters. Yes it is true that Access is very chatty and has long conversations with the server but when it comes to actually asking for data, the where clauses are sent to the server and processed there. BTW, this is not new behavior, this goes back to at least Access 2.0 when I moved to Access.
A fourth point. Since you folks don't develop Access front ends, you are probably not aware that MS has offered since A2007 a FREE runtime edition of Access that can be distributed to your clients. So the developer needs the full retail version because he is the one creating all the forms/reports/etc. But each user only needs the FREE runtime engine because he is only changing data. The users never (or in a well constructed database they never) modify objects.
In my 20 years of developing exclusively with Access, I find that the majority of my applications end up with SQL Server, DB2, or Oracle as the BE. Occasionally I have an outlier such as Pervasive, MySQL, or Sybase but rarely do I use ACE. I think this is probably because once a client pays a professional to develop the app, he wants a professional, stable installation and I happen to agree that Jet/ACE leave something to be desired in that regard. I do however, start applications entirely in Access as a self contained database (benefit) because that makes it quite easy to send to a user for review. Only once the schema has stabilized to I switch to the target BE format. I have some apps that can switch at will. In practice, I've never had a client go from SQL Server to ACE but many start with ACE and then switch to SQL Server if their concurrent users or row counts get large enough. This is also a tremendous benefit of Access that you get only if you use "Access" SQL. Obviously, if you used pass-through queries and stored procedures, you would need to convert all of those. But for Access, the ODBC driver handles the conversion. Yes, this adds a certain amount of overhead so you might occasionally want to create a view to speed up a frequent join or a stored procedure to speed up an I/O heavy batch process but for the meat and potatoes interactive FE, linked tables and querydefs actually perform quite well. I have BE tables as large as 11,000,000 rows so I can speak from experience here.
Also mentioned by several people is the use of "Access" as a database teaching tool. This is widely practiced by universities in the UK although it is much less popular in the US. Why make a beginner learn how to work with a command line interface or a complicated GUI when what you are trying to teach is the principles of normalization? Access is excellent for this type of teaching and with the right guidance can give the student a good sense of how an application can be built with a minimum of fuss.
And lastly to the people who hate Access because they think it somehow "less" than other programming tools. Yes it is less and that is exactly the point. That is why it is a RAD tool. It does ONE thing (create data-centric business applications) but it does it extremely well. I harken back to my days in corporate America before the inmates took over the asylum, no IT manager would ever consider developing a business application in a low level language such as BAL or C. They would always use a high level language such as COBOL because of the developmental efficiencies and ease of maintenance. If they needed specific power to do something, we would write subroutines in the appropriate language and pass off work to them. Today, everything is being developed in low level languages and that has driven up the cost tremendously and consequently led to the outsourcing of American IT jobs to offshore markets where labor is cheaper. It has also spawned a myriad of RAD tools that generate apps because of the tedium of creating them in low-level languages.
July 21, 2014 at 2:20 pm
Access users/gurus/MVP's, I'm curious what site(s) you consider to be the ones you use for Access that are similar to SSC, or even your "go to" sites for answers?
July 21, 2014 at 2:23 pm
And lastly to the people who hate Access because they think it somehow "less" than other programming tools. Yes it is less and that is exactly the point. That is why it is a RAD tool. It does ONE thing (create data-centric business applications) but it does it extremely well.
I'd go along with the idea of Access for prototyping purposes, as I'd mentioned in my initial post; but that's as far as I would go, at least for enterprise-wide application development. And I wouldn't ever want any Access 'apps' developed by anyone who doesn't understand the tool thoroughly, and/or isn't working with close IT supervision.
No doubt about it: America is a land of do-it-yourselfers. People here check out their own groceries at the shop, pump their own fuel, even build their own houses and give themselves haircuts (this last bit with varying degrees of success). But despite all that, there are still some red lines. It doesn't make sense for Joe Sixpack to perform his own brain surgery or write his own enterprise business apps.
July 21, 2014 at 2:31 pm
@patricia-2 Hartman: Well spoken and described.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 21, 2014 at 2:50 pm
Pat,
Extremely well put. A good portion for the disdain that Access has endured is from a lack of understanding of what it actually is; a database application development platform.
Goofy,
Yes, my scenario is in a more ideal world. But, again, the reason many users turn to Access, especially without informing IT is because they know they can't get what they need from IT so they turn to other tools.
My company uses Access as its development platform of choice. We are running over 100 applications many of which connect to tables in DB2 on an IBM iSeries. As Pat pointed out, Jet/ACE is only one of many choices for where to store the data. The apps range from those used only by a few users to what I refer to as enterprise wide for the entire company.
podmate,
This is a typical reaction that I have seen in many IT departments. Instead of trying to get a handle on Access development they try to shut it down. The end result is people will still do things on their own and just eschew IT support.
Andy,
UtterAccess.com is the premier site for Access support. I would say its very similar to SSC. Please feel free to stop by and contribute. We also have SQL Server forums there.
BTW, I was pointed to this forum because of your Access Disdain post but we are planning to shift much of my company's database platforms from Jet/ACE to SQL Server (retaining the Access front ends) so this looks like a good place to get some help.
Scott<>
July 21, 2014 at 3:04 pm
Pat has done an outstanding job of outlining the situation and there's really not much to add except, "Me too".
To answer one question about sites for Access answers, as part of the Admin team at UtterAccess.com, I invite you to come on over to get a picture of the kinds of problems we help Access newbies, as well as seasoned veterans, solve every day.
Some are the obvious kind, like trying to query a spreadsheet-style table with numerous repeating fields. My almost universal answer to that one is "Learn and use nomalization," by the way. I fear that more than one newbie has "gone away mad" because he or she didn't get the SQL syntax they wanted and were told to start over right.
Some are less obvious, like retrieving values from a web service with Access VBA. Ben Clothier and I wrote about it in a book we recently co-authored with several other Access MVPs. I've seen live presentations on the topic, too. I've often been surprised at some of the things developers want to do with Access, but I've also learned not to say, "You can't do that with Access," because someone else is going to come along and explain exactly how to do that.
Access is often misused precisely because it has a very low barrier to entry; any fool can use it, and some do. But it does scale up pretty darn well, too, if you want to make that happen.
One other point. Once a person decides to invest in a career as an Access developer, they can go a long with the tool. It would a lot more pleasant, though, if we didn't have to hear how bad Access is so often.:-)
George Hepworth
Access MVP
UA Admin Team
July 21, 2014 at 3:15 pm
... the reason many users turn to Access, especially without informing IT is because they know they can't get what they need from IT so they turn to other tools.
... and then turn back to IT when those tools go kuhflooey.
With sincere respect for Patricia and her twenty years Access experience, we're doing our best to eliminate Access 'apps' from the enterprise production business environment.
July 21, 2014 at 3:32 pm
I'd go along with the idea of Access for prototyping purposes, as I'd mentioned in my initial post; but that's as far as I would go, at least for enterprise-wide application development
Excellent -- because you (please don't take this personally or be offended) are not qualified to develop an Access application. Just because you are an SQL Server expert and an expert in some other programming language doesn't mean you can develop with Access. In fact, the absolute worst Access application that I ever had to modify was created by a competent C++ programmer. Why was it so bad? Because he thought he knew better than Access and refused to go with the flow and use Access as a RAD tool so he wrote thousands of lines of unnecessary code because he had to do it himself. I am an expert with COBOL and VBA along with having 30 years of data base design experience in a variety of RDBMS'. That doesn't qualify me to build websites with ASP and MySQL. It simply gives me enough knowledge to do some real damage. In another life I had to take a BAL (Basic Assembler Language) course as a prerequisite to an advanced COBOL course and the BAL programs I wrote came out looking just like the BAL that the COBOL compiler generated since that is what I had been reading for years.
My point here is - don't buy into the Access is for dummies theory. You have to respect the tool you use and use it as it is intended or you end up with crap. That is certainly understandable and even excusable for power users who have no programming training. It is not acceptable for professionally trained programmers. It was clear from this C++ programmer's effort that he hated Access and was trying to make it over in his image of what it should be rather than accepting it and working within the system.
Access has one huge weakness for widespread use and that is the quirks of installation and the stupid security added with A2007 that does nothing but get in the way. I have many wide-spread installations with clients using both local and Citrix connections. One client with servers based in Farmington, Connecticut has 100 users spread from San Francisco to Paris and all experience excellent performance with an Access FE and SQL Server BE. So, I don't have any qualms about widely distributed apps. Once you get over the installation hurdle the first time, you're all set. I have the app itself distribute updates so each time it opens, it phones home and if there is a newer version, that is downloaded and the old version is replaced. I also have two clients that sell Access applications that I developed. Installing those is more of a challenge since I have no control over their environments. The app does Office automation and we were having so much trouble with trying to support multiple versions that we finally fixed on a minimum requirement.
So, don't count Access out for enterprise use, just don't do it yourself. Hire a pro.
July 21, 2014 at 3:44 pm
So, don't count Access out for enterprise use, just don't do it yourself. Hire a pro.
Thanks, but no thanks. There are many other platform choices for developing enterprise-wide business applications, and we already have the pros making it happen - quickly, accurately, and efficiently.
As I originally wrote, one has to pick the right tool for the right job. Access is fine for prototyping and for limited-scale applications. Elsewhere, and in the wrong hands, it's often a Frankenstein's monster, trampling flowerbeds and generally making life miserable for developers and DBAs alike.
Viewing 15 posts - 46 through 60 (of 113 total)
You must be logged in to reply to this topic. Login to reply