Forum Discussion
Moving Away from MS Access
Yes, but with limitations.
First, you must have an Office 365 account which includes PA. I am not an expert on that, though, so I'll leave it at a general comment.
You have the option of many Datasources. Here's a partial list. There must be 50 or 60 in total.
Note that SQL Server is designated as "Premium", which means there is a per app/per user cost in addition to the basic O365 account. SharePoint lists are not and therefore incur no additional cost.
I recently saw that MS plans a price drop soon, though. Licensing issues are always opaque with MS I'm afraid.
That said, there is also a data issue to contend with.
PowerApps has a limit on the number of records that can be retrieved from a data source, depending on which data source and whether the function that retrieves them can be delegated (sent for processing on the server side, somewhat analogous to a passthru query, in a way). I'm going to make a hash of delegation, so again, a general comment. The default limit is 500 records, either in total in some situations, or in iterative calls to the data source in others. It can be increased to 2000 records. With SQL Server, that's more significant. Make sure you investigate this before committing.
I was able to finesse this by limiting the records I select to the most recent x days, say 30 or 90. That way I can see current data, but not historical data. For a data entry operation in a mobile app, that's generally adequate. I want to add new exercise sessions while at the gym, for example, and I really don't need to see any sessions from two years ago on the smart phone; I have an Access FE for that when I get home.
That's kind of a good illustration, as a matter of fact, of how I myself see their role. An extension for remote situations where limited processes are needed, but full data history isn't critical.
Yes, indeed, the importance of KISS is something we all need to keep in mind.
Perhaps this meeting, which starts in a few minutes, would be interesting.
If you miss it, DAAUG posts videos of their presentations on YouTube later.
Brian_Hayes
In principle I could retire next year. If I wanted to live on mac & cheese and sleep on a park bench.
Good luck!- BrianWS1O I'm eligible to retire in 2026, but we'll see. Thanks for your insight. Appreciate it.
Brian_Hayes
I feel ya...I've been in very similar situations, and I'm trying hard NOT to visualize your circumstances too vividly because it makes me cringe and clench to imagine having to again go though those struggles with IT Creatures. Been there, done that, ready to retire early if I have to do it again.
The thing to remember is, people often over-engineer Access apps, and you should resist the urge to go nuts building fancy forms, reports and so on. KISS, as they say. Learn VBA; it really is fairly easy and quite powerful in the right hands, and some VBA skills are sorta transferable to newer, trendier languages like C#. Try to learn TSQL and let the server do as much of the heavy lifting as possible, it's much faster and someday if you do find some other mythical new technology to "get them out of Access" it will probably play nice with SQL Server, so if the server is already doing most of the work, converting should be less painful.
Other than that, all I can say is that I hope you're not too many years away from retirement when you can blissfully forget about all this BS!
- No worries about resurrecting an old thread. Grateful for all of the conversation, insight and experiences you all have shared.
A little more background is the databases reside at an agency I used to be part of. There were a team of maybe six people who were responsible for any issues with the databases. Eventually I was asked to help out with that. Outside of taking a few Access classes, I'd had no experience with Access and to this day have never built a database from the ground up. Around 2014, I and the others who assisted with those databases were absorbed into another agency. The thought being why should the agency with all of the Access databases have their own "IT shop" (it wasn't really an IT shop, more of a first line of defense Help Desk type area). So at my new agency, the decision was made by the "powers that be" that the crew of six people (self included) who "maintained" the Access databases would continue to do so. Eventually all of them retired or moved onto other jobs, including the one who loved using VBA (nothing against VBA, but it's the devil when you know nothing about it). She was always on disability for health issues and eventually just went from that straight into retirement after bilking the system for as long as she could. (Another story in and of itself). So that meant the support of all of those databases (sixty-some at that time) fell in my lap. Because I was able to somehow keep them running smoothly and keep the users happy, they kept it that way. Fast forward to 2019, where I joined a team that supports O365 products, including Access databases. They came with me. Fortunately on that team is a person who is very knowledgable and savvy with Access and VBA and she helps me when I need it. But now that we're at the real IT shop, and are part of it, the visibility of these databases is no longer in the garage with the door shut, but out on the front lawn for all to see. And the "powers that be" are still saying "get them out of Access". So we're slowly working on converting the tables and data to SQL Server back-ends as a start, until someone finally decides what new technology they want to use to "get them out of Access". Where I work, Access is viewed as old technology. I have yet to hear a good reason to get things out of it other than security/encryption. Also auditors wanted some sort of logging feature to be able to tell every move made in each database. Apparently SQL Server accomplishes the logging and encryption of data (that's what I'm told anyway).
The main issue I'm having has been touched on several times. The people pushing to get things out of Access know nothing about the data contained in Access. An example would be an Access database developed in the early-to-mid 1990's that is still being used today. The consultants who developed it did a very nice job, and it has plenty of VBA (which I am unfamiliar with) and suited the users very well at the time. But as users, needs and developers changed over time, the database has as well. And there have been "several hands in the pot" so to speak. One developer loved VBA so used it very heavily behind the scenes. Others didn't know VBA at all.
So at the present time, our push is to start moving all data currently in an Access database into SQL server back-ends, while leaving the front-ends as they are. Obviously it depends on what the purpose of each database is, where the data is coming from, and many other factors to determine what technology will be used to ultimately "replace" Access. But the people tasked with that project are those that know little-to-nothing about the databases. I don't know if I'll ever see the end of this project while I'm still actively working.
All of you are appreciated and I can tell have vastly more knowledge than I do about the inner workings of Access, VBA, SQL Server, etc. For now, I'll just keep plodding along trying to learn SQL Server and trying to get the data into it. At the shop I work at, the "database" people create the SQL Servers and are responsible for them. But my coworker has familiarity with SQL Server Management Studio and is starting to show me the ropes (slowly but surely). But she is trying to wash her hands of Access support and it's like pulling teeth to get information from her to get all of this done. I honestly don't know why she just doesn't do it herself. But she has many new projects and is pushing towards retirement, so that probably weighs into it.
I hope this finds you all healthy and well and enjoying your 2022 so far. Your story supports my point about the relative security of the data depending on how its stored.
The analogy I like best is to compare Access to a locked safe. Stored in a closet inside your house where casual observers don't see it, that safe is reasonably secure, but if you haul it out on the front lawn where everyone walking or driving by sees it, you actually entice the bad guys to come back with a truck and haul it away. And once they have it under their control, they can open it at their leisure. Accdbs and xlsx files can be copied onto a thumb drive, or sent out as an email attachment, and opened at the leisure of the bad guy.
On the other hand, I also like the narrative presented by a VP in a company I worked at many years ago. She was fond of claiming that most employees drive to work thinking along these lines: "I hope I can do something really good today so I'll get noticed and maybe earn a raise." Very, very few employees drive to work thinking, "I really plan to screw up today so I can get fired."
Sure, there are bad actors, but if an organization structures itself around the belief that employees are evil and have to be treated as suspects, it has bigger problems than Access accdbs on its hands.
AdamGaffney96
You're preaching to the choir, buddy!
And by the way, speaking of security kills me; how is the data in an Access database file more or less secure than the data in a SQL Server table? All of our users have to login to Windows to use their computer at all, and if you have permission to use a folder an Access database is in you can see it; otherwise you can't. Also just this morning we were talking about how IT has added some dopey Add-In to Excel to mark a workbook--and it's in Outlook, too--to mark content as Restricted, Role Restricted or Highly Restricted. So for laughs we made a nonsense Excel file, marked it Highly Restricted then using Outlook marked it Highly Restricted there too, and sent it to one of our outside personal gmail accounts. It went through just fine and the label had absolutely zero effect on any security. So what is it for? Someone's making money selling our company useless garbage "security" products.- I absolutely agree, and I would love to have everything on a SQL server the main issue is the timeframe. Even just normally that process could take a while never mind the extensive bureaucracy required just to get all the approvals needed.
And the thing about lack of control is likely not incorrect. The thing is the IT team are just that, IT. They have no knowledge of the data we carry, what it's use is, or what it even means. It's our data collected, filled and used by us and so you'd expect that with having the skills and will to control the data on our own SQL server we would, but alas. I've been pushing for years to get many of our data lists moved to a SQL database with no dice, hence my trepidation in being forced into that being the solution. I think it's mainly people that don't use Access just hear that it's bad and a web server is better, with zero understanding of what that means in terms of development. "...reduce it to just a front-end and store no data in them."
That's not an entirely bad situation, to my way of thinking. Access, after all, is capable of connecting to variety of data storage types from Oracle to SQL Server to Excel worksheets.
The problem, of course, is the pace of conversion.
It's ironic, in my way of thinking, that Access is so hugely popular in part because the big IT departments can't be bothered, or perhaps more charitably don't have time and resources, to provide adequate support to the smaller, more niche requirements that Access so easily handles. Yet, when given a choice, IT would also chop off that outlet that otherwise relieves pressure on them to perform.
The main argument against storing data in Access is security, or rather the lack of it. That's resolved by moving that data into a more secure storage location, i.e. a server-based database.
The real problem, though, comes down to lack of control. If you, being very close to the end user, can control your own data, then IT is not in control. And that, I suspect, isn't acceptable.
AdamGaffney96
We mainly use MS Access as a platform to run VBA and some queries, with SQL Server as the backend database to stash tables, views and stored procedures. We don't really consider our Access files "databases", they're really applications. It's a great tool to use as a platform to write and test code, and it's easy to make quick changes, and super easy to deploy because nothing has to be installed, everyone in the company has Access on their computer or the Remote Desktop Server they log into. Very efficient and productive. A few corporate IT schmoes have made noise about getting rid of Access (mainly because they're control freaks), but the business folks won't ever let that happen because they don't want to have to wait 6 months and go to 10 meetings for some design committee to decide whether or not they're going to change a label on a form and or write a new query for a report they need this week. We're still struggling to get the IT goons to give us the ability to create and modify SQL Server databases; that part is still something they're trying to guard jealously because if we start providing solutions without them, the IT guys feel threatened.
