Forum Discussion
Moving Away from MS Access
The organization I work for is wanting to move away from Microsoft Access databases where possible, and we have started exploring what tools our current Access databases could be moved to. Is there a standard for what works best (Powerapps, Sharepoint, etc)? What have you found to be a good resource for guidance on this topic? Thanks!
Hi all,
since the AUG Access Pacific Presentation on Alternative solutions at the beginning of August 2024 did not go through (I think), may I present the progress we have made with moving the Access templates to the Web?
Personal Account Ledger from MS Access template
NorthWind Traders from MS Access template (wip)
NorthWind Traders from MS Access template v7 (wip)
Assets Inventory Application, V7 (wip)
And some apps from our users:
Resourcing and Billing Application from MS Access DB (wip)The ERP POC Demo with Italian and English translations
Auto Parts Sales for Brazilian Market (Portuguese)
Job Positions tracking App from MS Access DB (wip)
Pls find Help with notes about the "migration".This apps are now running for quite some time, like a year or two, with absolutely no issues, and zero downtime (except PA maintenance). This are simple apps to get one going. The RFM analysis on NWT is not that simple though, but just a few lines of code.
Also, NWT v7 has API call to Account Ledger. This is the only table not read only. Change something on Account Ledger, and NWT will fetch the amount.The Pivot table is a drag/drop report engine, it is literally no code report solution. Fantastic for "smaller" data. We also presented the server side SQL for "big" data.
Apps can contain History/roles/privs etc. and authenticate against OAuth, SAML, db passwords, etc.
Hope this will help to someone. Pls shout out any questions. I do not benefit from this at all, since all is Open Source.
Oh, and all v7 apps are mobile devices ready since using Boostrap 4. So just hook up this sw to mssql, etc, and off u go.
ThanksThis is probably the discussion that will never end
The case is that from time to time several attempts were made to easily migrate Ms Access applications to either .NET(Winforms) or Web.
Probably older would remember MUST the application that would undertake this migration semi automatically....it failed, vanished into thin air due to lack of interest.
Personally some years ago I was doing PHP and I simple loved it...so at this time I was all fired up to create an application that would the application, time of involvement estimated around 1 - 2 years....did a market research , well nobody cared, so I simply let it go.
Now I am on the Python boat, especially with the help of AI it would be a breeze to create such an application, time estimated around 6 months (sample) ,but again who would pay for that.
So the problem is not that it cannot be done ,but that pretty much everyone is expecting that it would be a free feature, just because they paid for the license for Ms Access they anticipate that it would be served on a plate free of charge.
Probably if nothing else drags me , I will go on with the development just to have something to keep me going when daily work load is minimum but I will only provide it as a paid service.
So its perfectly doable ,its just a matter of commitment and reward.Why reinventing the wheel?
The time spent was just a few hours with this framework for the above apps.
This IS Python/JS SPA, so nothing beats the speed of the SPA.
As mentioned, we are getting hundreds of databases to migrate. Any user can "migrate" it on their own in the matter of minutes for the Web presentation.
So really, no need to reinvent the wheel, but to join us if u wish.
In reality, if someone is into developing something similar, look 5 years plus. The AI does not cover for upgrading DB schemas, or importing metadata into a different OS.
This solution works on RasPi or a Samsung mobile phone as a server. Yes, it is possible to have the Samsung phone as a complete development environment.
But sure, many ways to skin the cat. I'm showing here what is possible in the matter of hours with minimum coding. And this is not the Access migration tool at all. It is the RAD framework specifically used in the health sector where the security is a must.
- I have built more Access databases in the last 20 years than I would like to admit. For some projects Access can be a quick and easy solution. When the Access database becomes difficult to manage - size is too big and too many users needing to access it at the same time - I have been converting them to Oracle APEX web applications which look and feel like an Access database in many ways - tables, queries, forms, reports, data exports, data imports, low code, etc. but with the benefit of accessing the database from any device connected to the internet. I found that pricing is pretty competitive as well. A 250 megabyte Oracle APEX database in the cloud using shared hosting runs about $50.00 a month and can be used by around 15 people. From there it can be easily scaled up.
ITLouis69 et al.
I just re-read most of this discussion. It was suggested to me that this might be a good topic for my Access User Group.
I run one of the Chapters of the Access User Group organization. I'm always on the lookup out for interesting topics that may not be on the beaten path. Would any of you be interested in joining us for a discussion of your concerns and possible alternatives?
I'm scheduling now for later in the summer this year.
At this point, it's kind of an idea, not a plan, but if there is interest, we can make it happen.
Hey George,
if i create an app in Access and end my subscription, can i still run the apps or are the apps useless if i do not re-subscribe?
I’m 82 years old, retired from it and have created many access apps for the company I worked for. Subscriptions were not available then, so that’s why I’m asking.
thanks,
rudy
The following online database software are structured like Access
I'm not sure what "structured like Access" might mean.
Any relational database application requires three components:
- A Data Layer: the tables in which data is stored.
- An Interface Layer: the forms and reports through which users interact with that data.
- A Logic Layer: the code which automates interactions with, and manipulation of, both the data and interface.
All relational databases work this way, so "structured like "Access" might mean only that these products have those three components. Perhaps your intention is to suggest deeper similarities?
Of the three mentioned products, the only one I some knowledge of is Caspio.
To use Caspio you must first migrate your data out of Access tables into Caspio's proprietary database, hosted on their site.
Then, you open the Caspio developer site and create new web objects (screens, code, etc.) to interact with the data.
You can continue to use ETL approaches to pass data back and forth from a local accdb to the Caspio tables, but that's not an intended scenario.
I suspect the other two products approach their solutions in a similar manner.
To properly evaluate them as alternatives, it would be very important to decide what your organization requires from your relational database application, and then independently verify how, and even whether, those alternatives provide acceptable features to meet those requirements.
It's essentially a one-way street, so be sure you are ready for that before you plunge in.
Are we still discussing Access? Sounds like very serious proprietary app used for Big 500 with Terra bytes of data.
Here, while you guys still thinking what is possible or not, I've moved 100 tables with POC data for the customer, and build the App in about 5 hours. ETL? My goodness, what is seen is a production data for a small company looking to migrate. None ETL was needed in a sense of manual work. All table and fields Captions preserved in the DB, all ~1300 of them.
Ridiculously easy. ZERO CODE, except wip Dashs! This is probably the first and only app seen on Arabic language for many - myself included.
https://arabic.pythonanywhere.com/
How to move Docs:
https://jampyapplicationbuilder.com/tips/migration/examples/index.html
This thread is now going into a third year. I would really encourage people to start learning some basic JS and ie Python.
No war, just the discussion.
With any RAD, including Access, no one knows what is happening in the background either.
It is beyond my understanding to read that replacing one tool with another and somehow people would understand low level bits and pieces. Nope. Who actually knows how Git works? Azure?
Do we care? Absolutely not. Can't care less.The automated or semi-automated tools exist exactly for that reason. Not to make anyone dumb, but productive. Access was born on that premise, just like FoxPro before that. But, we passed that point, no matter of the problem someone is trying to "solve". We are not solving the fundamental issue here, which is the Internet.
With non-Access database, the db responsibility is moved to the DBA in a medium to large organization. This move still does not solve the fundamental issue.
Finally, it does not solve the fact that we are getting old and tired. We, people who started with FoxPro, then moved to VisualFox, then Access, then Oracle, etc etc.
My suggestion: don't over complicate. People with no previous skills with any JS or Web technologies successfully built huge Apps. Desirable apps. Portable Apps. Yes, portable as click-and-run. Which complete app size does not exceed a few kb. Yes, that is a kb.Here is the PWA App, so click-and-run product from Microsoft:
https://apps.microsoft.com/store/detail/dynamics-365-business-central-online/9PGZQJ12JV75
It looks fantastic. It's a killer app, allright.
Here is my super small 113Kb migrated (yes, passed through the free tool), Access template from the above pic:
https://msaccess.pythonanywhere.com/wip:
https://northwind.pythonanywhere.com/
The exact App can run on Azure in a few minutes, as demonstrated above with git command.
Put that into the context, the App was built in 3 hrs. It is almost the same time as for Access template.
If one moved to Dynamics from the start, ultimately the fundamental issue would be solved. Dynamics is not my tool of choice, btw. It is great tho.PS
Edited in Jan 2023 with: Regarding Northwind Traders featured template in Access 2019, it is still in wip on the above site. Here is the interesting idea, the Export of this app can be used with any database created from the same template! Providing the database is uploaded and migrated just like this one was. Of course, if the template DB structure was not changed.
And the extra benefit, the App can use SQL Server or any other supported database. The actual data can also be automatically migrated to a target database.
The complete migration process is documented on Help page at the above links.
I am replying as a longtime Access developer, DBA and vendor.
1) Firstly, you need to migrate your data and structure to a SQL server of your choice. The natural choice for most Access developers is SQL Server. It is important to make a concerted effort to clean up your database structure as you migrate.
This goes a long way towards helping you slip off the Golden Handcuffs that Microsoft Access sometimes feels like. You can now run your Access application in parallel to any other solutions and properly compare the results.
In addition, this allows you to centralize your data (No more data Silo) and virtually eliminate any corruption issues.
2) Secondly, you need to take a clear look at the problem you are trying to solve. There is a mind-boggling array of tools available. None of them is a perfect match for Microsoft Access, but many can solve specific problems much better than Microsoft Access.
Finally, as a vendor, I would like to suggest that you look at dbFront. With dbFront, you can quickly create powerful web applications based on your existing databases. dbFront applications have a deep knowledge of the underlying data and structure and know how to show the data in context.
Interesting how my plugs were promptly deleted by admins and some plugs are going under the radar.
People avoid to tell the expense of hiring the DBA, AND the db license cost. Are we forgetting that the Access is literally free to use with it's own db? Imo modernising Access App is not using some other db engine, since other tools do this anyway. We do not modernise a car by replacing the engine to hydrogen, I think. Silly comparison I know. We just buy a new car.
Similar approach to running Access on the Web is suggesting Citrix, VPN or RDP. That is a nonsense imo. The cost of running all of mentioned infrastructure is high, plus the hidden cost of system downtime. There is no downtime of running Access with built in db, except install/reinstall.
The whole point of Access App is running it in the office environment, over the local LAN, with all Windows desktop bells and whistles. Desktop!
The biggest obstacles for any Access dev going to Web might be this:
- The Desktop bells and whistles are not there any more.
- You'll be completely lost in the Ocean of information.
- The security is in your domain, and that's not Desktop add/remove permission.
- The security involves SSL certificates, which cost money. And your time.
- The authentication is the whole new league, there is an Ocean of options.
- There is no more password expiry from AD, to name one example. The Web
app can easily log any username/password, and send it who knows where.
- The knowledge of Web servers is needed, add the cost and time.
- The load balancing knowledge is needed, same as the above.
- Also Web threats are not to be taken lightly. I witnessed a complete server take over in few seconds.
- And finally, the lawyers or similar can't just take the App into a locked and off-line room. Like they do.
I'm sure there are more obstacles.
And I did not even touch the JavaScript, which is a huge topic! One example, Boostrap and JQuery.
It is not possible to just take some random jQuery library and "plug" it in the Web app.
Just like it is not possible to replace Boostrap with more modern one. Nope. Takes significant effort.
So now you know what I went through 😉
And when shared my work, which I even created on .azurewebsites.net to show it to the World, it was deleted.
So basically, we created the similar as a plug from the above. With a huge difference. Its Open Source.
Which can be taken in the off-line, locked room 😉
Would you like to see it? Politely asking for a permission to add a link. ThanksPS Not sure why my reply went to above contributor tho, sorry.
For me is all about money.
You pay you do the moving/migration....someone else will do the job and will deliver the desired output/
You don't pay and you try to "evade" the cost....all you will see is obstacles and huge walls.
Web is just another platform and it has its own pros and cons, you just have to do your homework even if you are just to hire someone and just make your choices.
Let me give you a scenario:
Suppose someone builds an application that does the hard job of translating an Access application ...how much would you pay for this functionality (?) .....probably you wouldn't ..so why build the application in the 1st place.
- I've just finished reading this thread and it's triggering some PTSD from my previous encounters with similar nonsense.
1) People trying to get their jobs done come up with tools to make it easier.
2) Leadership identifies a problem which is caused by people doing dumb things, and expects a technology solution to this problem.
3) Leadership finds something via a google search or some salesman at a conference who tells them that a magical product will solve every problem.
4) The project either
A) dies on the vine because nobody actually even knows what they need or
B) is implemented at enormous cost and ends with a disastrous mess that creates far more problems than the original solution because the people entrusted to implement it don't listen to the people actually doing the work.
Moving "away from" a product requires moving towards another product. You can't just slowly back away from MS Access and expect some other solution to hop into your arms like a lonely kitten.
It's nice to know I'm not alone out there. Sorry you had to deal with this nonsense. Since people are still replying, almost three years latter, please allow me to share my view. To be fair, the interest in this is huge. How I know? By people who are contacting me every now and then.
There are automated tools to convert Access to Web. One was discussed on reddit, search "Moving MS Access to Web" - it did hit the nail with admins though, but comments are still there.
.NET and VB is a bugger. Impossible to move the business logic and even so, it would not run on all different Web servers.
What I would suggest is to modernize the App paradigm. Hence, moving to something which is supported widely. And something which is proven that runs light speed faster, and can scale, etc.
My organization is still using 20 years old technology. And it is not the only one I'm sure. They will never, ever, move from it, not because they can't, but because of politics and zillion hours spent on something which can't be modernized any more without completely changing the paradigm. Web changed from dial-up connection, and it's changing rapidly.
With the above reddit mentioned tool, one can demonstrate the POC in minutes. One can convert Access with >100 tables in a single day. And the App can be packaged into a single executable file if needed, so no fiddling with the Web servers. Just run the file, and point the office Web browsers to App network IP. So deployment is literally none. Security is also a top notch. It can even support SAML. Sounds too good to be true. Well, the App developed by this 7Mb large (!) tool or platform is supporting 2000 pharmacies, hence about 15000-20000 users 24/7/365.
Hope this reply will help you a bit to make a proper decision.
Dean
PS
The Access migrated to the Web is 100% safe from stealing the IP, or the data by application users. That does not include SysAdmins, as we know. Even if the data is on MSSQL server, the IP is not protected on many many applications. FE and BE does not solve this issue. Only the Web app does imo.
It is not about liking but managing. Try to manage 5000 Access apps scattered around the globe. Or better, try to find the VBA devs this days. It's getting like Cobol, they will be paid in gold. Which was proven during Covid.
Ben "The Hosk" Hosking calls this a technological debt. Just like Cobol.
Good discussion. Cheers- The powers that be are working on how to change the technology from Access to something else, still. No progress has been made, but it's not on my plate any more. I am left to support the working Access databases until end of life, which may never happen. I'm okay with that.
I really appreciate all of the responses and discussion. If I find out what technology they end up going with before I retire, I will update you all.
Thanks for a good discussion.
Cheers!Frankly, that doesn't surprise me at all. I started with Access in the mid-90's, as what was then called an "information worker" and what is now called a "citizen developer", meaning I had no idea what I was doing, but I realized the power of Access and wanted to pursue it. Over the next couple of decades, I saw this scenario more than once. The Powers That Be want to rid themselves of Access. They spin their wheels in futile project after futile project to do so. They end up discretely looking the other way while the people getting their day-to-day work done with Access keep on doing what they've always done.
Sometimes, enough institutional muscle is thrown behind alternatives to make them succeed. When that happens, it takes years to accomplish and a lot of money. Smaller organizations with less discretionary budgets, though, don't often have that luxury.
- Depends what you're trying to accomplish. You shouldn't choose tools first and projects second. Figure out what you need to do, then find the best tool for the job. For example, where I work we have tons of people struggling to manually combine, wrangle, edit, modify, process and otherwise tinker with files from a vast range of business software systems, and they need to mix, match, combine and crunch data from Excel and text files into coherent output (usually Excel files...after all, these are mostly accountants) We have found that the most flexible and efficient tool is MS Access. There isn't much we can't do with creative VBA and SQL, and we use some SQL Server databases for the situations with a lot of data and complex queries. Access also happens to already be installed on all of our employees' computers, so deploying solutions is a cinch. We also don't have a lot of multiuser issues, not is much of the data is ever edited by the users, it's mainly just manipulating data from other sources (PeopleSoft, SAP, mainframe systems, and so on) into forms they can use, or automating long, tedious manual labor.
Of course, you have to determine if that's the kind of thing you need to do. If you have hundreds of people simultaneously entering and editing records, or you need complex pretty reports and graphs or a web-based interface, then you might not want to use MS Access. Well this is an interesting "question"...but as stated is incomplete.
First you have to put down what you are using the Access for and what complaints you have that forces you to abandon ship.
The real answer is "how much money" is Access costing you and "how much money" you are willing to spend to get away...if someone just said "just leave Access is crap" ... let him/her show some alternatives...there are plenty but always there is a cost...
tsgiannis you know, I came here for help and all I've gotten is different responses telling me I either am wrong for wanting to move away from Access, or people telling me I asked the question "wrong". I thought this was supposed to be a support and help community. I'll just take my questions elsewhere. Good day.
Just because you don't like the "answers" doesn't mean they are wrong.
Lets take a simple example.
Some years ago i read a book about migration of a Simple Access Application to a Web equivalent (ASP.NET i think i can't remember) .
It was around 300- 400 pages where the author took step by step the whole procedure from migrating the tables to SQL using Entity framework, designing the web "forms",the reports and so on...the surprising fact was the Access was a simple few forms - few reports application nothing heavy by any means...just a simple just over the beginner level template ...yet it took its effort ...it took its time...
SO yes you can move away from Access any time and there are great alternative...you want desktop ..then .NET or Java fills the blank perfectly ..you want web ..even better PHP/JS/.NET and a ton of frameworks are here to help for the UI...for the BE is much "easier"..just make a pick MSSQL/MySQL/Oracle/IBM/Firebird ...there are just too many options.....either paid or free.....
Some years ago my boss was discussing the transferring of the BE of a huge Access application to SQL...i replied NP...just "close" the IT department for 1 year ( i was the only "real" developer and i was also the system administrator)...let me transfer the table/queries and then optimize the whole application in order to use SQL efficiently...of course he denied ..saying that i was exaggerating and others have done it just by using one of the utilities that are in the "market" ...and its so "simple...we just migrate and we fix problems as they pop up".....i refused to take this lonely road.the whole idea along with the proposed planning was dumped...a few years ago i left the company...till today they are using Access and unless something magical pops up they will do for the next 20 years for sure.So as i said in my previous "you didn't liked" post...nothing is holding you back ...the word "impossible" simply doesn't apply in these cases... if you are the manager you just have to train/enhance your team to do the job...if you are the boss you just have to hire some people to do the job...if you are the developer/administrator you just need to calculate your extra hours you are going to cost to your company to take such a task .....so as i said is all about money...either in explicit or implicit form but is money ....if you are not ready to spend/charge or whatever simply you will come back here or in another "friendlier" forum and find out the same fundamental truth.
