X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Alternatives To Ms-access, Any Versions    
post Sep 14 2015, 07:19 PM

Posts: 15
Joined: 13-November 13

I have developed many complex applications with ms-access and my business is reliant on it. This reliance could become a weakness if something happens and I can no longer obtain ms-access or the version of ms-access needed to run my code. For most people this is not an issue but what if you are collecting baseline data that will be needed decades from now. Backing up the data is not such an issue as I can easily port the data to many formats including open text-based formats such as XML. It is mainly the ability to maintain the application that concerns me.

I love ms-access and just happen to think that the best way to ensure that Microsoft looks after it is to invest in the alternatives. I hope some other ms-access developers would do the same and help to ensure that the power of ms-access is perpetuated.

My current strategy is to jump to C# and Visual Studio with a ms-access backend. In time, I will migrate the data to another backend, probably an open source database. Since Microsoft has open sourced dot net core, I feel better about this option, however as of now, it does not seem that the open source community had leapt onto dot net, other than Xamarin for use in mobile applications. Desktop applications remain my interest. I had a professional developer create the C# winforms equivalent of a very simple ms-access application. The original ms-application had 70 lines of VBA and the C# version had 700 lines. Furthermore the C# code is not nearly as readable as VBA. However I will persist with the C# option, mainly because C# is so popular and because C# asp.net pages with razor syntax look just like the VBA code I use to generate html reports. However it does concern me that the razor syntax does not seen be be taking the world by storm. VB.net seems to be fading and is not supported by open source tools like sharpDevelop.

Ms-access is a cosy little world that protects us from having to take care of the complexity of the real world. It is very hard to leave that world, but ultimately to be a better developer, one has to.

There are also many other alternatives and some of the ones that I have considered are presented below. Basically I need a RAD tool for making data-centric applications. I am also a consultant, rather than a professional developer so it has to be a managed environment with a lower learning curve.

All the following alternatives can use open source databases. I am hoping that Firebird embedded takes off as this would be a small engine that substantially more powerful than the ms-access jet engine and is inherently multi-user.

PureBasic is like a minimalist version of Visual Studio and it is cross platform and has native database support for Postgres and SQLite databases. The language is very efficient and it has some very powerful features such as arrays of user defined types. My concerns are that the development team is a bit small and the PureBasic ecosystem is also a bit small. The language is quite mature and is general purpose, however the forms designer is fairly basic. The number of event types is so much smaller so creating sophisticated data driven applications might be challenging. I have not developed a data centric application but imagine that data could be read into a typed array that essentially functions as a database cursor (~ recordset). PureBasic is probably the most gentle introduction to the world of professional programming one could hope for. It opened up a lot of advanced concepts such as pointers in a way that was not scary. PureBasic is good for making small dlls that can extend ms-access. I made a dll for parsing XMP data from photos and it was only 6KB! It can compile to both 32 and 64 bit code and compiles very fast. It also has sophisticated debugging tools that are equivalent to those in ms-access.

This is Delphi reborn as far as I can tell. It is powerful, cross platform and mature. I personally didn't like the IDE for silly reasons such as the text not being anti-aliased. Pascal also seems verbose. It is open source and has a large user community so is a safe set of hand for long term projects. I found the learning curve as tough as visual studio and did not persist long enough to get anywhere and things may have changed since I last looked.

Qt Creator
Qt Creator is a C++ IDE, however it has a good screen form designer that is in many ways ahead of the ms-access screen form designer. It can also save the screen form design to QML files which can be converted into Python. Once this is done, it should be relatively easy to write Python code to complete the application. If they just built the export to Python function directly into Qt Creator this could be a game changer. Currently another open source command line program is required to do the conversion. On my machine Python path issues meant I could not get a lot of python based systems working properly, it was the Python equivalent of dll [censored]. I cause the issue by loading many different variations of Python. If I did want to go down this route in the future, I would set up a development environment in Virtual Box.

The power of Python and Qt Creator is best displayed in QGIS (formerly Quatum Geographic Information System). QGIS is build with Qt Creator and includes a built in forms designer. This forms designer is almost a match for something like FileMaker. With no code it can make quite sophisticated forms.

All the above are git friendly in that they save both user interface code and application code in separate files as text. One of the great pains is that ms-access is not git friendly.

LibreOffice Base
There is a move to make Base a much more serious contender. The firebird engine is being adopted. Also the event model of ms-access is being reviewed and somewhat rationalised. In a few years Base may be a serious contender for application development. As part of LibreOffice it has a huge ecosystem. After the richness of ms-access it is hard to use Base. Base also uses the Basic language which is great for ms-Excel users and to some extent ms-access developers. However I would like to have a language that is general purpose at my fingertips and one that has a life of its own. My preferences would be for something like Ruby, Python or even JavaScript.

This is the one open source application that has set out to take on ms-access directly. It may be about to crest the hill and become a serious contender. In recent months, it has developed the ability to connect with external databases. The biggest drawback is that Kexi is not available on Windows at this time. Kexi looks like ms-access but is more basic or at least there is not always a properties window to show you which events have handlers. It previously allowed scripting in a variety of languages however I think it is moving to JavaScript, which to me looks a lot like VBA.

Visual Ruby
This project uses the ActiveRecord Object Relational Mapper that is at the heart of Ruby on Rails to create desktop applications. I have tried it but it failed to work due to some error that meant nothing to me. I guess that the people who make many of these systems do so to solve their own productivity issues and once they are solved move on. They don't quite create the bridge with safety handles that ms-access users need to move into the bigger world. If you are a VBA developer who has never looked at Ruby, take a look at how Ruby can manipulate strings.

Notable Others - Desktop

Online Alternatives (All OpenSource)
The online alternatives are very powerful but are still rapidly evolving. They target the enterprise market.

A top to bottom JavaScript stack with a serious database company behind it (4D). It had rough edges last time I looked but that was a few versions ago.

This is a very smooth web-based IDE. I looked at it when VM Ware was its owner. I did all the tutorials and built some simple applications which I deployed on TomCat. It sure was not as easy as writing a quick bit of code in ms-access and putting it on the LAN. However in the hosted online environment, then getting an enterprise level application up and running should be a lot easier than doing the same with ms-access. The learning curve for WaveMaker is lower than for ms-access but ms-access users also have an unlearning curve to consider. The unlearning curve has killed me more than a few times as most other development environments do not work the way the ms-access environment does. Databinding is different, the way event handlers are connected to the user interface is different and if fact ms-access is the odd kid on the block in most respects. All the other environments seem to recognise development concepts that are hidden from view in the ms-access environment. Without knowing these other concepts, it is really hard to adapt to new alternatives. Any WaveMaker is even more visual than ms-access and is a simplified environment that requires even less coding. Coding was possible in JavaScript at the front end (client running in the browser) and was Java in the back end but this may have changed. The price of the hosted version is a bit scary. When I last checked it did not have a report builder either and needed to be paired with something like Jasper Reports.

WaveMaker is/was open source but this seems to be gone from the web site. Still it is worth doing a trail to see what it can do.
Go to the top of the page
post Sep 15 2015, 05:10 AM

UtterAccess Administrator
Posts: 10,278
Joined: 7-December 09
From: St. Augustine, FL


For backend data, my suggestion would be to port it from the Access database to SQL Server. SQL Server has a long history and is a big player along with Oracle (incl. MySQL) and PostgreSQL. If I had to pick three database engines that had the least chance of fading from the world in the foreseeable future, it'd be SQL Server, Oracle and MySQL. Furthermore, SQL Server is heavily integrated throughout the MS stack, so you can easily write frontend applications from any MS platforms (Access, WinForms, WPF, ASP.NET) to use it. Additionally, the popularity of SQL Server has resulted in many non-MS resources working with it as well.

For front end work, WinForms is considered old tech, WPF is still used but fairly stagnant, and ASP.NET still holds quite strongly.

There's rarely any platform/language that lasts the life of a developer, so there's likely no silver bullet as far as "do it once and don't have to worry about it in 20 years." Things just change too fast for that. Get it into a stable platform/database and that's the best you can do.

Additionally, when looking that far forward, one should be prepared to drop any personal attachment to the desktop and instead look to the cloud. Azure, AWS, HTML5/JS, etc. I too much favor the desktop, but over the coming years I expect that the focus will drift further and further away.

As for .net's open source, it doesn't surprise me that there's been no frantic adoption... I expect that will take quite some time to get up to levels as the other big open source players.

As for many of your proposed alternatives: I've never heard of most of them, and as such wouldn't count on them holding ground for the long run. Stick to mature, stable technologies with big footholds in enterprises, and you'll be sticking with the safest bets as far as platform longevity goes.

Go to the top of the page
post Sep 15 2015, 09:44 AM

UtterAccess VIP / UA Clown
Posts: 32,217
Joined: 21-January 04
From: LI, NY

First you need to understand something. Access is not a single program. It is actually a database development platform. It provides several pieces that can be used to develop a database application. It provides a UI platform where you can create forms the user can interface with. It has a report write so you can create reports on your data. It has a procedural language (actually 2) that can be used to automate tasks and data processing. Finally it has a database engine (currently referred to as ACE) that allows you to store data in tables. The thing here is that some of these parts are interchangeable. You don't have to use forms generated through Access (though it is much easier). You can use other tools to develop reports. You don't have use programming code at all. And you aren't stuck with ACE.

I don't see Access going away for a long time. But you can certainly migrate your data to SQL Server and continue to use your Access front end.

Most of the things you mentioned are application development platforms that are not specific to databases. Closer alternatives to Access are FileMaker and Alpha 5.
Go to the top of the page
post Sep 15 2015, 03:08 PM

Posts: 1,040
Joined: 26-January 14
From: London, UK

You've had some good suggestions already. If you are looking for an open source SQL DBMS then I would definitely recommend PostgreSQL. It's the best all-round open source DBMS in my opinion.

I would repeat the advice given already about not looking "decades" ahead. You cannot expect to insulate yourself from 10+ years of change. The business world doesn't work that way and the technology world certainly doesn't work that way. Most software projects are expected to pay for themselves in 3-5 years and in a 10 year timescale the technology landscape will probably be unrecognisable from what it is today. For sure some of today's software will still be around but who would want to try and predict that? The best policy is probably to choose what is right for the near term and then expect to adapt and be responsive to changes over the years.
Go to the top of the page
post Sep 15 2015, 06:34 PM

Posts: 15
Joined: 13-November 13

Thanks Jack and nvogel.

Keeping the data around for a long time does not scare me. If I get a really good data model in ms-access, it is at easy as running a wizard to copy the data into a SQL Server Database. Likewise, there are some commercial products can transfer ms-access data to a variety of open source databases.

This is my strategy for making applications maintainable through time and technologies.

Regarding keeping the application around, I dump all the source code out to .bas files, which are text files that can always be read with a text editor. It would be possible to re-implement my code in another language fairly easily if it came to that. I am using ms-access vcs integration which is available on github to export my code. The blessed repository appears to be https://github.com/timabell/msaccess-vcs-integration. For those that have not seen this before VCS stands for Version Control System. Ms-access VCS integration lets me convert a ms-access mdb or accdb file into a set of text files. I have actually exported an entire application with about 20K lines of code to text files then created a blank database then imported the text files to recreate a working copy of the original database. The table definitions, form definition etc are all stored as text, so it not just the VBA that can be saved and later imported, it is the entire database, even the data if this is what you need.

Currently I am using ms-access VCS integration to tell me what has changed between versions of my applications. For example when did I add new fields to a table. It will also detect when I made changes to business logic. As ms-access source code files are encapsulated and their is an export and import step, I am not using git for source code management, rather I am only using it for change detection.

If the data model is good then much of the application complexity disappears and the application code becomes mainly generic functions. In the application that I am currently writing which is an ms-access based photo manager, out of the 50 or so procedures in the application, I can only see about 10 that are specific to photo management. The rest of the procedures are generic enough to be transplanted into another application (checking if file exists, creating new folders etc.). I wonder if that observation is applies to other applications.

Trying to use git with ms-access has also changed my coding for the better. I now have many more smaller modules. Each module is clearly named e.g. modfolders has all the operations which related to folder management (and nothing related to file management). As git detects changes in source code files, if you have large modules, then is it likely that they will be changed every time you do a development session even though most of the code in the module will be unchanged. That sort of defeats the purpose of using git, which is to help me find out what I have changed. The solution is to have smaller modules that contain only a few procedures. Git will then ignore most of the modules as these will contain only code that has not changed. I finally get why open source projects have thousands of tiny source code files.

Yesterday I wrote code that parses my source code files and picks up 'XML comments'. Ms-access does not support XML comments in the way that visual studio does, however it is possible to simulate this functionality in the ms-access IDE. I can't paste the code in here as it currently depends on a procedure from the FMS source code book but the result is that I type in makehelp in the immediate window and my application API is displayed in a web browser. Being able to print out an overview of all the procedures in the the application would certainly help with application maintenance. Below is a sample from modFolders.

Public Sub CreateFolder(DriveLetter As String, Folder As String)

   Create a folder on a storage volume when provided with a drive letter and full folder path

Public Function FolderExists(DriveLetter As String, Folder As String) As Boolean

   Returns True if folder exists, otherwise returns False

Public Function GetOutputFolder(OutputFolder As eOutputFolder) As Funct

   Returns a full path when provided with a target folder alias. e.g. 'temp' folder

Public Sub OpenFolder(DriveFolderID As Long, Folder As String)

   Opens a folder in Windows file explorer

Being able to add something like XML comments to each procedure and print out an API would also go a long way to making the code available to future generations. ('///This is a comment using the C# syntax for flagging an XML Comment and is what I am using). I imagine that someone has already written similar code for documenting applications that is shareable.

Regarding not being able to make future proof applications, there is another strategy that is available with open source applications. As far as I can tell, I can alway fire up a virtual machine, load a prehistoric version of Linux then load my prehistoric application and run it. That is not a long term solution but is a get out of goal card.
Go to the top of the page
post Mar 17 2017, 08:06 AM

Utterly Banned
Posts: 3
Joined: 17-March 17

SQL Server.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    10th December 2019 - 02:24 PM