is microsoft access still used at your company (1 Viewer)

[danieloxi49]

I work in IT and i'm supposed to not like access, but it holds a place for me because it is what got me into databases about 10 yrs ago. In spite of all the heat it takes, it def has plenty of use cases but takes a lot of heat from developers. I've seen bad and good come out of access, but more good than bad. Is it still used at your company and is it generally accepted or are people fighting to turn them off and move to other solutions?

 

[theDBguy]

Hi. Welcome to AWF!

We still use it all over the place.

 

[AccessBlaster]

My experience with Access and IT is like oil and water. When it comes building and deployment IT isn't very helpful. They were ok with you developing Access as long as kept it local or on a shared drive.
Putting it on a SQL server requires top level permissions. I was lucky to be granted a small space on the server.

Once the developer moves on or retires that's when the fun begins.

 

[Isaac]

My last job was at major bank in the IT division. They didn't allow any use of Access within our division (we were a sql server shop).
They still allowed Access outside our division, but were starting to "try" to clamp down on use of it. Too many people creating Access database at the hobbyist level of skill and causing trouble. (Like when you see a post: "I'm brand new to Access, but about to deploy a Sales system for my company"--I can understand why IT hates Access).

At my current company, also major bank but not IT division, it is all over the place, but they are trying to persecute the use of it by essentially making everyone who uses a desktop tool homegrown (not Systems group), go through a rigorous documentation process. This has trimmed its use significantly.

I use it a lot.

 

[CJ_London]

in my experience, access is used because the corporate systems are not fit for purpose in certain cases. Typically a corporate system will deal with 80-90% of all requirements, access does the other 10-20% with a bit of excel thrown in. In a company of 1000 users, perhaps 5 or 10 have a specific requirement which is outside the scope of corporate systems (usually because the requirement was descoped for budgetary reasons or the world has moved on and the corporate systems haven't) - IT are not going to spend (or prioritise) £500k on upgrades for 5 people - even if they impact on £1bn of expenditure.

Access gets a bad rep because IT do not understand it. Even you say 'got me into databases'. Access is not a database, it is a RAD tool which interfaces with multiple databases. Access also gets a bad rep because there are many 'developers' out there who don't have a clue and IT is left to pick up the pieces once the app has moved from single user to multi user and become a key part of the business process.

 

[AccessBlaster]

You have access developers who write applications for sale they are prepared to battle with IT to get their product deployed. There is a financial incentive.

You have the altruistic employee who wants to help other employees. If you fall into the second camp life is going to get harder for you. You may want to reconsider and let the organization buy a canned product.

 

[Gasman]

You have access developers who write applications for sale they are prepared to battle with IT to get their product deployed. There is a financial incentive.

You have the altruistic employee who wants to help other employees. If you fall into the second camp life is going to get harder for you. You may want to reconsider and let the organization buy a canned product.

When I worked for one of the Big 4 banks in the UK as an admin person, they asked me to create a small system, just so we could do our admin work more efficiently. Flow leaders were spending an hour each morning working out the allocation of work for us plebs.

I created that DB and my flow leader could not help but show it off to a manager who was opening another site for the same work and reviewing our processes.
Big boss man asked her if she had seen all she needed to see and had everything she wanted.?
She responded 'I want the Payment Allocation Database'. !
Big boss man said 'What on earth is that'?

After a few words, the directive came down, 'Stop using the PA DB immediately'.

The IT department were required to check it, to make sure it was fit for purpose. This was after they had refused to create such a system themselves, and would have certainly created it a lot quicker than I did.

I had left by then, but it was passed for future use, but the IT dept charged my old dept £11K for this, when any of the experts here, could have done it in a few hours. It did not interface with any bank mainframe systems.

In contrast the IT dept created an Access DB that allowed numerics in first and last names and we were constantly sending cheques to Mr firstname SA11 4GH etc.

Now you will say, why would anyone put numerics in a name.? This was due to my colleagues having to copy from one Access DB to another and had to do about 200 a day, so kept making mistakes by pasting into the wrong control.

 

[pbaldy]

We still have several mission critical apps plus numerous "helper" apps that people have asked me to create over the years. My employer sold their operations in a different city and the buyer slowly replaced my programs with commercial apps, over the objections of the users. The new management didn't care that the users were less efficient and didn't get as much functionality. It didn't seem to dawn on them that we operated with 3-4 clerical people in the office and they needed 5.

My employer is currently trying to replace a vehicle inventory app I wrote that includes repair and parts inventory components. So far the vendor hasn't been able to replicate everything mine does. Theirs has some fancy stuff I couldn't do for sure, but they can't customize it like I can (at least not as fast). Even when they get there, they've realized they have to leave mine running to get some functionality that they can't replicate.

Access is still a great tool. Like any tool, it isn't appropriate for every job.

 

[plog]

Is it still used at your company and is it generally accepted or are people fighting to turn them off and move to other solutions?

Those aren't mutually exclusive. A few of my prior jobs I was hired specifically for my Access skills in spite of IT who was fighting to turn it off companywide. Here's the script for every play I've been a part of for ending Access at an organization:

IT dictates that Access is done, My Dept Head understands and is on board
Dept Head meets with IT about migrating key Access process, Dept Head excited with how awesome it will be
IT comes back with horrible budget requirements and long time frame
Dept Head complains to Execs,
IT responds to Execs
Compromise reached, development starts, Dept head less excited but still on board
Internal IT troubles--fire comes up they shift focus to, key personnel quits, etc.
75% of the time the story ends here only to restart 2 years later
25% of the time development restarts months later usually throwing out all prior work
No word on project for months,
Out of blue horrible prototype presented that does 1/3 of what was expected and incorrectly at that
Dept Head angrily meets with Execs and says this isn't going to work and he's done helping IT with this
I ask for a raise because I just became more valuable

 

[The_Doc_Man]

I was a contractor with the U.S. Navy / SPAWAR Division for 28 1/2 years. When Access became available, the Navy used it for in-house projects. The trick was that contractors are permitted to buy or make their own tools based on the economics of the projects.

In the Navy Enterprise Data Center New Orleans, they used Access to track system compliance with published security alerts - essentially a giant checklist of 20 to 50 alerts per month to be evaluated against servers for over 80 projects. At peak, we had 1600 server-class systems. So we had to write status reports listing every server on project vs. every current alert. That was easily 15-30 machines x 20-50 alerts per month for each of 80 managers. We ALSO had something from BUMED - Navy Bureau of Medicine and Medical Records. They had SQL Server (full version, not express) as a back-end and Access as a front-end. That served the entire U.S. Navy Reserve and was being deployed to the regular Navy just before I retired. Therefore, I can say that the Navy used Access at department and division level as recently as 5 years ago.

 

[Galaxiom]

Access is used at my shop for several reasons. It is a great rapid development tool for interfacing with our SQL Server databases where much of the heavy lifting is done. I have developed some very slick apps using Access. (I also have some crappy ones that I only use myself for very basic stuff.)

The CFO uses Access to query the SQL databases. He writes some pretty terrible queries then I convert them to Views which he then uses in Access and Excel reports. He does great reports in Excel from linked data in views and stored procedures I have built for him.

I am the default database admin and IT generalist in a small IT department so I don't have to beg for access to the server.

There is absolutely nothing wrong with Access in the hands of a developer who knows what they are doing. However hideous things are done when in the wrong hands. We stopped allowing other employees to create their own apps in Access a long time ago but I must say some of the things I have seen them do in Excel both impress and appal me at the same time.

BTW I can tell you problems of poorly skilled people using tools that shouldn't be available to them is not limited to Access. There are paid database professionals who should not be allowed near databases at all. Marketing recently hired a data analyst so I have been finally able to focus a lot more on the database admin work. I have started seriously investigating the performance of the server.

I investigated why many virtually identical queries were producing huge plan counts. For some it was because they had used

EXECUTE strSQL

instead of

EXECUTE sp_executesql strSQL

Only the latter is capable of saving a plan. It is a novice's mistake.

The worst case was a stored procedure that also included a table valued function that accepts a comma delineated string of numeric values which it splits on the commas to return the values in a table. The table is then used in a SELECT as the argument for an WHERE IN() construct. The query is dynamically constructed so the string could simply have been concatenated into the SQL exactly as it was. Doh!

Another web app uses only ad hoc queries with nothing at all done in stored procedures. Its plethora of queries each create thousands of plans a day. The McAfee corporate PC firewall and antivirus is the same.

 

[AccessBlaster]

There is absolutely nothing wrong with Access in the hands of a developer who knows what they are doing. However hideous things are done when in the wrong hands. We stopped allowing other employees to create their own apps in Access a long time ago but I must say some of the things I have seen them do in Excel both impress and appal me at the same time.

Unfortunately there might not be any Access developers or a budget at your organization. Yet the need still persist.

 

[Pat Hartman]

Here is an article written by Luke Chung of www.fmsinc.com It puts some perspective on Access' place in the world. I've worked for a lot of large companies and invariably they hate Access. Mostly because they end up fixing or rewriting Access applications created by novices. They always blame Access for bad decision choices made by unknowing developers. On the other hand, my small to medium sized companies understand the cost benefit if using Access when they want something custom.

Microsoft Access within an Organization's Database Strategy

How Microsoft Access fits within an Organization's Overall Database Strategy by Luke Chung, President of FMS, Inc.

fmsinc.com

Take a look at the other articles while you're on the site.

 

[GSTEEL320]

I have a small tech department and use Access extensively for the creation of Proof of Concepts. If a project takes off, becomes absolutely critical, we may look to replace it with a better "supported" solution. That said, we have Access interfacing with SQL, Caspio and other internal systems and I have to say (touch wood) its arguably Microsoft's strongest product. Personally, I also can't read reams of text written by a software developer for a project plan, so the fact Access can be put together in a very "visual" way, helps me understand the processes and flows involved.

I also have to say, its ability to handle multiple users concurrently, especially when used as a front end, is nothing short of remarkable.

 

[Pat Hartman]

I came to Access from the world of the mainframe and was blown away in the early 90's once I realized that I could link to DB2 databases on the IBM mainframe and actually update them with applications I built with Access. I KNOW how much effort it takes to write and test multi-million dollar mainframe software. For probably a quarter of all the huge applications I have built over the years, Access as a FE to a RDBMS would have been more than sufficient and cost pennies on the dollar. Access would have been adequate for even more except that Access is almost impossible to use in a multi-developer environment. Even when it included hooks to source-safe using it with other developers was just way too clunky. So, the dividing line is not the number of concurrent users you need to support (that depends entirely on the number of seat licenses you have for your RDBMS) but the number of concurrent developers you need to get the project done. I've had two Access applications that I designed and built within 2 months replaced by "real" software at a cost of MILLIONS and instead of one person for 2 months, it took a dozen people 2-3 years. Maybe they could have gotten it done sooner if there were only half a dozen people on the team

 

[zeroaccess]

I'm in a similar situation in that my team needs functionality that we simply are not going to get from corporate due to the investment. That's where Access comes in and with careful development can be a brilliant solution that costs very little.

Of course, they have a lot of apprehension regarding Access but my program has proven itself. I think the main issues are mainly the bad taste they have from other poorly designed databases and what happens if/when the developer moves on, but that also applies to any custom program they have developed. In fact that did happen, so I replaced something outdated that we couldn't change with this Access db that allows us to be more agile. The other main difference is the amount of investment. I pleaded with them not to waste money on another custom thing that will never meet all of our requirements due to the developer not being familiar with our work, and having us end up back in the same situation in a few years when we again can't make the changes we need on the fly.

 

[Pat Hartman]

It makes sense to use off-the-shelf software for things like payroll. You are never in control of the rules and due to the annual rate changes, you have those changes as a minimum. Best to let someone else handle the headaches. Same for things like General Ledger and AR. They are based on standard accounting rules that your company would always want to follow anyway.

For your line-of-business app, if it is not absolutely standard, then you either need to build something yourself or hire someone to do it for you.

Access apps developed responsibly are an excellent solution for any business, large or small. All the bad press you read about "Access" is actually directed at Jet and ACE if you read between the lines and you should be able to convince your IT people of that. People simply do not understand that they are different tools. They talk about "Access" having no recovery or being limited to x users. But those constraints are not "Access" constraints. They are Jet/ACE constraints. Access creates the application, it is a RAD (Rapid Application Development) tool. Jet and ACE are the database engines and they manage the data. The thing that makes Access expandable to support even thousands of concurrent users, is that although it requires Jet/ACE to manage its own objects, it can use ANY RDBMS that supports ODBC. So, if you build your Access app with a SQL Server BE and understand how to deploy it properly, it can handle as many concurrent users as you have seat licenses for SQL Server. None of this "50" or so user limitation. It just isn't real. The actual limitation with Access, is that it is essentially a ONE developer environment. So, you really can't use it to develop applications that are so large that a single developer couldn't handle it.

 

[allanc]

My company still uses it but I think their goal is to get rid off it.

 

[Pat Hartman]

Silly goal. Shows a complete unawareness of what Access can do for a business. Sometimes the new, shiny thing isn't the best option.

 

[zeroaccess]

Yup...the new thing seems to be SharePoint with forms feeding into spreadsheets. One-way, non-normalized, 5000 record limit so they have to manually move rows somewhere else, lack of customization...it's a mess.