Moving On From An Access Database

[jamesbrown]

Sounds like you need a MapReduce based architecture providing a highly available, scalable, distributed environment. Have a look at Hadoop and associated databases (Cassandra, HBase etc), which you should be able to wire together quite nicely using Java. Deploy options include a local virtual cluster or something like AWS/EWS for a cloud based offering (ps. It does mean you'll have to unfortunately ditch the Access back end) HTH

Or Spark

[unixman]

OK, I am by no means a techie, I can handle a tiny bit of command line scripting but that's about it.

I have an Access database, storing various records etc and then pull of reports based on the underlying data.

I want to take it to the next step, ideally have some sort of dashboard that gets updated when ever the data is changed, it will not be very complex, its basically a list of hostnames and various release data associated with it. I would like it to be hostable so it can be viewed from a browser, I assume something so basic can be done with Python and MySQL.

Obviously being self hosted I would need to factor in security, but given a limited amount of experience in this how possible is it to pick up?

This can be done fairly easily as others have said with MySQL and php. You don't say if the web front end should be for actually making changes or just viewing data. If for viewing only, it will be easier and probably more secure.

It sounds like this will be single user only, so Sqlite might suffice for the database, or even text files if this is Linux/Unix. Or you might get away with a self hosted wiki such as mediawiki or even tiddlywiki, unless you are doing lots of relational stuff.

[SpontaneousOrder]

Access isn't a database.

is that much different than saying mysql isn't a database?

[unixman]

I think what SueEllen meant is that Access is not a multi user RDBMS.

[SpontaneousOrder]

I think what SueEllen meant is that Access is not a multi user RDBMS.

I thought it was referring to the underlying DB engines used in MS products.


Do you need a DB at all? what about a spreadsheet?

And does it need to be relational? I do my accounts on a little mongodb instance so I don't have to worry about schema too much.

[Fandango]

If you want a jazzy Dashboard and you've got Azure, have a look at PowerBI, easy to knock together something impressive looking

https://powerbi.microsoft.com/en-us/...-and-power-bi/

[VectraMan]

I think what SueEllen meant is that Access is not a multi user RDBMS.

It is multi user and what does the R mean anyway:

The most popular definition of an RDBMS is a product that presents a view of data as a collection of rows and columns, even if it is not based strictly upon relational theory.

I assume what Sue Ellen meant is that Access is a development environment for creating database applications, sort of. But the OP didn't say "An Access" he said "An Access Database" - i.e. a database created in Access, which clearly is a database.

Who knows? Anyway don't use Access it's old and crap.

[DimPrawn]

Anyway don't use Access it's old and crap.

I don't think there's many new Access systems being written, but there's lots of old one written 10+ years ago out there.

There's a few tasks here:

1. Identify the new storage. I would choose a free RDBMS. MySQL, PostgreSQL, Firebord maybe, choose one you like, that has some future proofing. You could go to files and spreadsheets, but that seems a retrograde step to me. Find something that has a free tool to migrate the MDB file for you if you can. I would choose SQL Server Express because I'm a MS developer.

2. Choose a web front end framework and language. PHP is popular. ASP.NET MVC if you are a MS fan using C#.

3. Find some snazzy JavaScript frameworks for visualising the data. https://razorflow.com/ http://dashingdemo.herokuapp.com/sample etc

[MrMarkyMark]

I don't think there's many new Access systems being written, but there's lots of old one written 10+ years ago out there.

And very glad we are to have them in investment banking.

The external regulators insist they all have to be dismantled and go, for good.
Meanwhile, users are building more....

Thars gold in thar hills

[Cirrus]

It is multi user and what does the R mean anyway:

It means relational and show me any other database that is more relational.

Anyway don't use Access it's old and crap.

It's wonderful - a favourite of Bill Gates himself. Show me another database environment where you can take two updatable views and create an input/display grid with the tables joined. In under 60 seconds!!!

For that matter, show me another system that allows updatable views with joins, full stop.

I don't know what the OP means by 'hostable'. Access typically runs on a network drive so is accessible to others. If they mean 'can be viewed without having Access installed' then they might consider leaving it in Access. I would have thought there is a downloadable free viewer from MS if the local IT people allow that. Also if they are using an older version of Access they could always have a quick look at Data Access Pages that translate a report into a web page for viewing in a browser. It does a brilliant job but uses something like ActiveX which again is IT Security dependant.

I've looked now and again for an Access replacement but nothing I've seen has the same wonderful QBE interface.

If the OP wants to go the plain programming route then MS SQL Server is probably more sensible than MySQL in a corporate environment. There are various versions. I don't know what they are called because MS are pathological fiddlers and name changers but last I looked there was some small, free version that moves around in a single file (like Access). The benefit of that is if you need to boost you user footprint, your company will very likely have licences and infrastructure to let you grow the application effortlessly.

Or another approach I saw at a big bank was to write all the dashboards in Excel and then put calls in the Excel sheets to collect the data from Access.