What are some robust alternatives to Microsoft Access
I have a combination of Access applications currently in use. They are NOT garden variety. There are no bound forms or controls, and basically we are really just using Access as a platform from running VB. We have over 1600 stored procedures (or "Queries" if you prefer), 360 tables, 200 forms and something approaching 700 pages of code. This total is held down significantly by the use of data driven query assembly and report generation.
Unfortunately, Access simply can't work with remote data servers. Our back end databases were moved to a remote location as a precaution during Hurricane Irma and we couldn't even open them. Attempts to run Access over VPN connections are a frustration clinic. I have even created a local Job Queue to run jobs requested by users in remote locations.
Equally unfortunate is the fact that at some point we will lose our local server. So between now and then, I need to find a development platform that will still allow all of the robust functionality we have in Access, but will also us to use back end data files at remote data centers. Most of the advertised "Access Alternatives" appear to be fine if you are replacing an app with 5 tables, three forms and 10 queries. We need something that will continue to allow us to build powerful applications for a non-developer user population. And the whole team is 2 people!
Suggestions welcome!
Unfortunately, Access simply can't work with remote data servers. Our back end databases were moved to a remote location as a precaution during Hurricane Irma and we couldn't even open them. Attempts to run Access over VPN connections are a frustration clinic. I have even created a local Job Queue to run jobs requested by users in remote locations.
Equally unfortunate is the fact that at some point we will lose our local server. So between now and then, I need to find a development platform that will still allow all of the robust functionality we have in Access, but will also us to use back end data files at remote data centers. Most of the advertised "Access Alternatives" appear to be fine if you are replacing an app with 5 tables, three forms and 10 queries. We need something that will continue to allow us to build powerful applications for a non-developer user population. And the whole team is 2 people!
Suggestions welcome!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I would like to thank both of you for these replies. Obviously, with a question like this it is going to be a bit thorny to figure out how to assign points.
Mr, Dettman: This sounds like a compelling option, but I have to roll it up to the people who are in charge of the servers. I may well contact you again to employ you personally to assist.
Mr. Ebro. Thank you for appreciating the scope. This has, indeed, been years in the making and an option for MORE years is not the preferred solution.
Unfortunately, I am about a 7 iron from age 68, and for reasons surpassing understanding, I remain by far the most skilled Access developer anywhere in the surrounding area. I hope to work another 4 years (it is, after all, a new century) and I would like to leave a company that has been very good to me with a viable solution. Unfortunately, our IT department - who seems to make things worse every time the announce an improvement - views "Access Databases" with the same eye one would use for lepers.
In any event, I will follow up on these suggestions and be back to both of you. Again my thanks.
Mr, Dettman: This sounds like a compelling option, but I have to roll it up to the people who are in charge of the servers. I may well contact you again to employ you personally to assist.
Mr. Ebro. Thank you for appreciating the scope. This has, indeed, been years in the making and an option for MORE years is not the preferred solution.
Unfortunately, I am about a 7 iron from age 68, and for reasons surpassing understanding, I remain by far the most skilled Access developer anywhere in the surrounding area. I hope to work another 4 years (it is, after all, a new century) and I would like to leave a company that has been very good to me with a viable solution. Unfortunately, our IT department - who seems to make things worse every time the announce an improvement - views "Access Databases" with the same eye one would use for lepers.
In any event, I will follow up on these suggestions and be back to both of you. Again my thanks.
In regards to points: Give it to Jim. I just agreed with him, and added some commentary :)
Its not un-common for IT departments to view access that way. Some can claim its an "old" product, while others find virtue in a piece of software that just had its 25th anniversary. Show me any other technology that can claim that, while being so useful?
Sadly many IT departments insist on wanting the "newest and most shiny (and likely to be obsolete in <5years) technology"
Best of luck with your project.
Its not un-common for IT departments to view access that way. Some can claim its an "old" product, while others find virtue in a piece of software that just had its 25th anniversary. Show me any other technology that can claim that, while being so useful?
Sadly many IT departments insist on wanting the "newest and most shiny (and likely to be obsolete in <5years) technology"
Best of luck with your project.
Show me any other technology that can claim that, while being so useful?The rest of Office :)
IT hates Access because they don't understand it. Plus it gets a lot of bad press because Jet/ACE are not as robust as server based RDBMS'. Of course, they don't pretend to be anything but what they are which is best of breed DESKTOP database engines. Access however is not "Access" in this context. The people who pan Access are actually panning Jet and ACE and have no clue that Access is actually a separate product that can use any RDBMS that supports ODBC as its data store. Access only needs Jet or ACE to store its own objects. So in reality, Access, the RAD tool is infinitely scaleable. It is limited only by the number of RDBMS licenses you have since proper structure of an Access app has each user opening his own personal copy of the FE and only sharing the BE that holds the data. When the BE is SQL Server or that ilk, the server handles the sharing. The weakness of Access are:
1. It doesn't work well over the internet. You really need to use something like RDP or Citrix. A VPN won't cut it and a direct connection to Azure, although possible, won't work well unless you built the app from scratch with that in mind AND you have an excellent server with a lot of bandwidth.
2. Because all application objects are stored in the same physical database, creating applications that are large enough to require multiple developers is problematic. Source Safe helps but there are still many issues with multiple developers in a single Access app.
3. Distribution is awkward, especially if you've used ActiveX controls. You have to get the FE distributed to each user when there are changes made to the FE. This means that you need to develop a batch file that can distribute the FE or build or buy a specialized tool.
You don't mention how many users you have.
If a dozen or so, the Remote Desktop option is certainly an option for a reasonable license fee. The main advantage is, that it can be implemented right away - it's an installation task, not a development task. You can even create Remote Apps where you from a shortcut not opens the remote desktop but the - what it appears - the application only. This means the users will se a window on their local desktop holding the application only. In other words: It looks for the user as the application runs locally.
If many users, you may consider Citrix which - in short - is Remote Desktop for the enterprise, and so are the license fees.
In both cases, you may take advantage of my script for distributing the frontend:
Deploy and update a Microsoft Access application in a Citrix environment
Or you may consider moving the backend to SQL Server. As your database isn't that large, you may even be able to use the free Express edition. No special hardware is needed, just a decent production quality (virtual) server at the remote location.
This will, however, take some work, though not difficult, just boring. If you connect to the SQL Server via ODBC, your frontend should work right away, but most likely some tweaking may be needed.
A free tool will do the major work for you:
SQL Server Migration Assistant (SSMA) v7.3
One advantage is, that you check this out in a test environment in a few days, and quickly get an overall impression if this is the way to go.
/gustav
If a dozen or so, the Remote Desktop option is certainly an option for a reasonable license fee. The main advantage is, that it can be implemented right away - it's an installation task, not a development task. You can even create Remote Apps where you from a shortcut not opens the remote desktop but the - what it appears - the application only. This means the users will se a window on their local desktop holding the application only. In other words: It looks for the user as the application runs locally.
If many users, you may consider Citrix which - in short - is Remote Desktop for the enterprise, and so are the license fees.
In both cases, you may take advantage of my script for distributing the frontend:
Deploy and update a Microsoft Access application in a Citrix environment
Or you may consider moving the backend to SQL Server. As your database isn't that large, you may even be able to use the free Express edition. No special hardware is needed, just a decent production quality (virtual) server at the remote location.
This will, however, take some work, though not difficult, just boring. If you connect to the SQL Server via ODBC, your frontend should work right away, but most likely some tweaking may be needed.
A free tool will do the major work for you:
SQL Server Migration Assistant (SSMA) v7.3
One advantage is, that you check this out in a test environment in a few days, and quickly get an overall impression if this is the way to go.
/gustav
A few things:
<<Mr, Dettman: This sounds like a compelling option, but I have to roll it up to the people who are in charge of the servers. I may well contact you again to employ you personally to assist.>>
As gustav has said, doing RDP (Remote Desktop Protocol) is a "Plug and Play" type thing. It's a matter of buying a server, buying some licenses, doing some config work, and then using it.
There are some minor tweaks that may be required on the app (all users would have the same C drive for example, so if you create temp files in something like C:\Temp, you might have conflicts). But in general, apps will run very well with this setup, even in low bandwidth situations. The reason is that only KVM (Keyboard, Video, and Mouse) is running across the internet. The app is running remotely on the RDP server, which avoids the issue with JET/ACE databases because as far as its concerned, it's on a local LAN.
There are many that can help you with that as it is a very common thing to do not only for Access, but other applications as well. As for myself, I am booked up work wise for the foreseeable future and not accepting any new clients or work.
<<Unfortunately, our IT department - who seems to make things worse every time the announce an improvement - views "Access Databases" with the same eye one would use for lepers.>>
and that's very typical. Access does have some downsides in certain situations and some out right faults, but not as many as some make it out to be. Many stories you hear are the result of "developers" using the product in ways it never was intended to be used. I once answered a question here where a company wrote a commercial product with it and were in a total panic on the day of release because they discovered that JET locked on pages rather than records (this was back quite a few years ago). Of course they blamed Access for not having record locking rather than themselves for choosing it, or not developing their software differently.
<<Or you may consider moving the backend to SQL Server.>>
As has been discussed, you can change out the data store. Moving to SQL in a local LAN situation is a fairly easy task and most apps work better with SQL right out of the gate than they do with JET/ACE back ends.
But for remote situations, you'll need to do work to make it happen as Ander's said. Most Access apps are not written well in the light of true client/server. They can be changed (use views, stored procedures, triggers, etc) and can work, but often it is a total re-write of the app and that being the case, you'd be better off just to move to something else.
If you do decide to try it though as gustav suggested, have a look at "the best of both worlds" here:
http://www.jstreettech.com/downloads.aspx
There are some good tips in there on how you might change an app to work better with SQL over a WAN and these are along the lines of what Ander's was referring to in terms of writing an app from the ground up for client/server (the basic design of how things are done). If you do go this route, you may want to talk to Armen directly as he has done a lot of work in this area (taking existing apps and getting them workable with a SQL back end over a WAN).
But almost always, a RDP server is the fastest, cheapest, and most reliable way to setup an existing Access app for remote use.
Jim.
<<Mr, Dettman: This sounds like a compelling option, but I have to roll it up to the people who are in charge of the servers. I may well contact you again to employ you personally to assist.>>
As gustav has said, doing RDP (Remote Desktop Protocol) is a "Plug and Play" type thing. It's a matter of buying a server, buying some licenses, doing some config work, and then using it.
There are some minor tweaks that may be required on the app (all users would have the same C drive for example, so if you create temp files in something like C:\Temp, you might have conflicts). But in general, apps will run very well with this setup, even in low bandwidth situations. The reason is that only KVM (Keyboard, Video, and Mouse) is running across the internet. The app is running remotely on the RDP server, which avoids the issue with JET/ACE databases because as far as its concerned, it's on a local LAN.
There are many that can help you with that as it is a very common thing to do not only for Access, but other applications as well. As for myself, I am booked up work wise for the foreseeable future and not accepting any new clients or work.
<<Unfortunately, our IT department - who seems to make things worse every time the announce an improvement - views "Access Databases" with the same eye one would use for lepers.>>
and that's very typical. Access does have some downsides in certain situations and some out right faults, but not as many as some make it out to be. Many stories you hear are the result of "developers" using the product in ways it never was intended to be used. I once answered a question here where a company wrote a commercial product with it and were in a total panic on the day of release because they discovered that JET locked on pages rather than records (this was back quite a few years ago). Of course they blamed Access for not having record locking rather than themselves for choosing it, or not developing their software differently.
<<Or you may consider moving the backend to SQL Server.>>
As has been discussed, you can change out the data store. Moving to SQL in a local LAN situation is a fairly easy task and most apps work better with SQL right out of the gate than they do with JET/ACE back ends.
But for remote situations, you'll need to do work to make it happen as Ander's said. Most Access apps are not written well in the light of true client/server. They can be changed (use views, stored procedures, triggers, etc) and can work, but often it is a total re-write of the app and that being the case, you'd be better off just to move to something else.
If you do decide to try it though as gustav suggested, have a look at "the best of both worlds" here:
http://www.jstreettech.com/downloads.aspx
There are some good tips in there on how you might change an app to work better with SQL over a WAN and these are along the lines of what Ander's was referring to in terms of writing an app from the ground up for client/server (the basic design of how things are done). If you do go this route, you may want to talk to Armen directly as he has done a lot of work in this area (taking existing apps and getting them workable with a SQL back end over a WAN).
But almost always, a RDP server is the fastest, cheapest, and most reliable way to setup an existing Access app for remote use.
Jim.
ASKER
Thanks to all for the education! I am going to explore the RDP/Terminal Services option and will let you folks know the results.
Thanks again!
Thanks again!
ASKER
Sorry I didn't get around to awarding these points sooner. Still working on the suggested option (year end close supervened) but still plan to move forward. Thanks again!
But, as Jim has pointed out, the recommended approach is to use a remote desktop solution. If you are looking to replace access as a frontend, for something that sounds very complicated, I would guestimate that you are looking at several several months, if not year(s) of development.