Forum Discussion
Transitioning From Access To Web Sites
The WHY - I have been asked for years to convert it to a web app so that we are not reliant on Access, but have it run entirely through a browser. There is a software/licensing that would require a user to have the software installed on their machine in order for the application to run.
From my standpoint, if the application was built in pages, new update should not have to be deployed to each user vs pages being updated on the site, unlike updating the app using Access
There's also a speed issue - currently users are connected to the db with Access in various office/remote locations. There is noticeable speed degradation based on using a vpn vs local connections. Using a website should eliminate the need for the vpn and local connections, if I'm not mistaken.
The audience will be expanded from the number of users using the local version and a web version.
Since I'm not a web developer, I have to learn from scratch (just like I did with Access MANY years ago). Starting with the forms would be a great place to start. I would like to take the existing forms and transform them as close as possible to the originals, since the users are already using them as they are. And, there is VBA code behind some of the fields that will update or fire something off based on what is entered. I'm sure that's all possible, but I don't know where the best place is to start. I know there are many options out there, but I need something that is not going to break my brain trying to make this conversion.
Hopefully this explains a little more behind the methodology of the project.
Thank you.
So, one motivation is to move away from Access. It might be interesting to know why that is the case, but not really informative as to the approach needed to "use a browser interface".
The more relevant factor, IMO, is that you have a dispersed work-force, some users remoting in via VPN to use the database application. I don't know if the assumption regarding the use of a browser-based application necessarily would address that, but it certainly is important.
Updating a website, page by page, is different from updating an accde front end.
I don't know if the comment about expanding the audience applies to your own employees, or to anonymous -- i.e. public -- users. If the latter, of course, a web application is better suited to that anonymous audience. Anyone can get to your data.
You should not think in terms of "starting with forms", though, as I see it. All relational database applications start with the data, in tables, in a database. That's the starting point for a transition.
You have numerous options for a server-based database that would support a web application, ranging from SharePoint lists at the simple end of the scale, through remotely hosted SQL Server and SQL Azure. Other databases such as Postgres and MySQL among others are available. Your learning curve with any of them will be a factor in how smoothly you could transition.
After that, you'll end up creating new screens to replace the Access forms, using whatever web development platform your organization finds most appropriate. There are numerous options to choose from. It's not my strong suit; I'll leave that to others.
And, of course, you'll have to learn a new programming language to replace the VBA. Again, not my area of knowledge. You have a plethora of choices there. Investigate the ones that seem most accessible to you.
Once your new web application is designed and tested, you can conduct a final migration of your existing data to the new database and deploy the app to your users. After that, of course, you can retire the Access application.
- There is a couple of items that are motivation. Speed of searching records. One database that pulls data in has well over 2.5 M records and growing. Searching those records to pull in maybe 150 records is sometimes a slow process. I've always heard Access is a good front end, but it doesn't do well with large amounts of data. Another motivation is to get away from a specific front end. If it were browser based, it would hopefully solve some of the speed issues (maybe) and updating the clients. If it were web based, update the pages, test and, and deploy.
Using the VPN method, which I already am, the biggest issue is the speed. The way I'm getting around that is to have the users use RDP.
Security would be a very important part of this. Anonymous users would not be permitted to log in. It would need to log in with UID and Pass.
In terms of the data, it is already laid out in SQL, with an on prem SQL server. My preference would be to keep it onsite for the time being until I learn more about Azure SQL.
I totally agree with update an Access front end vs a web page. But, I wouldn't have to deploy a whole new client if I update a web page.
In terms of what the organization finds appropriate to use, that rest solely on my shoulders. I've been working with this and a handful of other databases for almost 18 years. If I'm going to overhaul this database app (again), I'd like to move in the web version. We've all heard Access isn't going to be around forever. I'd like to be on the front end instead of behind the 8 ball.
Sorry for the ramblings....Once again, maybe a little more clearly. Moving away from Access to a web application means recreating everything, with the possible exception of the tables in the back end. Access, of course, can link to remote SQL Server or SQL Azure, which can also support a web app. That means anything relating to the Back End--security, performance, etc. -- is comparable for the most part.
What I would recommend you do, before committing to a full transition, is create a proof of concept application using the newly selected alternatives for a web app. Pick a browser based development language and recreate a few pages for that app. Deploy it for a test group. Find out where your assumptions proof out and where they need to be adjusted.
- You've read my mind.
your suggestion is exactly what my plan of attack is. But, my question is...what language is the easiest to learn/maintain from someone who is used to working with VBA?