I say "baloney". If the most effective way to "get the right data into the right hands at the right time" is via a quick and dirty Access download and export to excel, than that is the right way to do it.
I can write SQL queries, but it is orders of magnitude faster for me to develop in Access via "drag and drop". And, since Access optimizes the query before passing it on to the SQL server for execution, I believe there is very little additional overhead executing the query on the SQL server.
Access is not aware of the metadata the way the linked database is and therefore cannot make the best decisions on how to efficiently retrieve it.
And I've also encountered a "one size fits all" mentality that says "all the ERP screens and reports must look the same in every division because that is what's best for IT."
This is insanity at it's finest. Each division started out as a separate company and has different products and needs. To say that they should all use exactly the same program in exactly the same manner is as idiotic as saying that everyone should wear 8EEE shoes because that is what I wear.
Agreed, but in my experience, that's not been an IT decision.
IT should be responsible for the physical security and integrity of the data only. They should have people on staff and qualified in the enterprize package, and also have individuals that understand how users can take advantage of tools such as MS access/Excel for ad hoc information requirements. This means understanding how to set up ODBC connections, etc., and know how to kill errant processes (such as open join queries) if necessary.
They should also create an end users training in powerpoint, etc. on how to use tools such as access for quick and dirty reporting or exporting to Excel. They should also train "Key Users" in each division that could then work with that division's users to develop the quick and dirty apps. We all know of an in house "expert" in MS word, Powerpoint, and usually Excel. Why not have the same type of person for Access?
This would then relieve IT of virtually all of the tedious "one off" requests from end users and allow them to focus on truly business wide needs.
It would also broaden the base of knowledge so that there is less likelihood of a "developer" leaving and IT getting "stuck,"
Data isn't information until you can make sense of it and use it in a meaningful manner. Users are the ones that do that. Realistically, IT doesn't. Therefor it makes sense to provide users with tools that allow them to access and manipulate data in whatever way is most effective for them.
Just my NSHO...
I think your mention of "integrity" is key here to the discussion. At what point is the integrity compromised? Certainly from the master table where the data resides, but what about once it leaves the database?
Actual case: Business group has need. IT is not staffed to handle that need as a priority because management has not made that need a priority. But since everyone has Office tools and the business group has demonstrated to management that they can do it themselves in MS Access, they get the OK. Enter scope creep and lack of IT foresight (e.g. scalability and data recoverability), and it becomes a major IT project.
Is IT the problem?
Actual case #2: Business has decided that users need ad-hoc access to ERP data, but has not enforced training requirements for those users. One user decides to make a "temporary" data silo in MS Access for many departments that makes the data look prettier than the monsterous ERP system because IT doesn't have time. However, the Access user is not privvy to business decisions enacted by IT as to the nature of the data, say for example, inventory part usage. So when the definition of inventory part usage changes to exclude certain business activities, suddenly the data silo numbers are grossly inflated. And users are making business decisions (purchasing, forecasting, etc.) off of this data silo and damaging the business.
There's a reason companies have IT departments. And it's up to us to make sure they know why.