Microsoft Access alternatives?

banana_split

So, to set the scene, my company uses a large number of Access databases and over the years they have become unwieldy and prone to crashing.

We primarily use Access for reporting processes, so there are a number of tables from various systems which we then run various macros on to append to reporting tables, merge with other data, update fields on, etc.
We then have reports build on top of the final data.

Because of the volume of data we are importing & querying, some of our databases are close to 2GB (even with compacting regularly) and have become prone to crashing / corrupting.

I’m investigating if there are any alternatives to access?
I had considered SQL, but I believe it doesn’t have macro or reporting functionality.
Another option was Power BI, but I believe this doesn’t have data manipulation (append/delete/update queries), macro functionality.

Are there any other tools out there that may suit our needs?

Fake Scores

The functionality that you had in macros could instead be in a vb.net front end that connects to your sql server db. There's third party reporting tools also.

SilverSideUp

You need to separate the business logic from the database. For the database you could use mysql, it's an open source, tried and trusted solution. For the business logic, you could use Python or similar scripting language to make the calls to the database. After that, it's a question of how to present your query results to the user. It doesn't have to be anything fancy unless you have particular requirements. But this is the road I would go down.

Edser

Or a variation on the above, you could move the data to SQL, but keep the front end functionality (macros, forms, reports, etc) in Access.

Inquitus

I would advise ditching access, it is not fit for purpose for the size of DB you are working with nor is it a good front end even if you switch to mysql.

Jim2007

banana_split wrote: »

So, to set the scene, my company uses a large number of Access databases and over the years they have become unwieldy and prone to crashing.

We primarily use Access for reporting processes, so there are a number of tables from various systems which we then run various macros on to append to reporting tables, merge with other data, update fields on, etc.
We then have reports build on top of the final data.

Because of the volume of data we are importing & querying, some of our databases are close to 2GB (even with compacting regularly) and have become prone to crashing / corrupting.

I’m investigating if there are any alternatives to access?
I had considered SQL, but I believe it doesn’t have macro or reporting functionality.
Another option was Power BI, but I believe this doesn’t have data manipulation (append/delete/update queries), macro functionality.

Are there any other tools out there that may suit our needs?

If you are going to put time, effort and money into something like this, replicating the same mess in another technology very rarely proves to be a success.

Forget about technology for a while and time into defining the business requirements and look at possible ways to implement the solution.

free_man

Edser wrote: »

Or a variation on the above, you could move the data to SQL, but keep the front end functionality (macros, forms, reports, etc) in Access.

+1 for this option.
Its very easy to convert the tables within access database to linked tables (to SQL DB). The front end will stay the same and keep users happy. Normally change is always frwned upon. The backend will be SQL DB.

bennyineire

Have a look at FileMaker Pro, it is like access on steroids'.

Easy enough to start off with and can be as complex as you wish. Also great for reporting and automating tasks. Can be used for some many other things too

I've been using it for over 4 years and think its great,
https://www.claris.com/filemaker/pro/

Check out this YouTube channel as a starting point to give you an idea

https://www.youtube.com/user/Dasaint1982

In saying all that I agree with Jim2007, you first step is to nail down the business process flows and requirements. If you have them agreed with MGMT it is some much easier to begin the process of moving to another platform

Also you can convert a MS Access DB to FileMaker Pro

MarkR

I've been using Power Bi with some success, but it would rely on a connection being available to pull the data directly from some form of online resource. Either that, or you're relying on manual exports. Depends on the level of reporting required really, and you mentioned updates, but I wasn't sure was it just to manipulate the data for use in the reports, or to feed it back to the source. Power Bi is really only for reporting I think.