1

This is to ask whether accdb databases are suitable for this application, and how to connect the user with the database (and the phone network with the database if anyone has a comment on this).

I use an old pc program that downloads data from a local sensor, writes it to a small Access database (mdb), and gives the user queries to view graphs of the data and export it to Excel and other programs. I want to make a new system that does roughly the same but runs online. It doesn't need to be compatible with the old system.

So far I've written a new pc program (in C sharp) to download data from a local sensor and write it to an Access (accdb) file on the same pc. So the user gets a small Access file for each download, similar arrangement to before.

The new system is intended to download data remotely (mobile phone network) to a server in the cloud, and for the user to view graphs of the data, download data to their pc etc. There will only be a small number of users (probably one) connected at a time.

I tried mySQL when writing the first part of the new program but found Access easier. Would there be a database engine in my program in the user's pc, and if so is it best if it's the same as the database in the cloud? Is Access ok for this application?

The users are generally not software savvy, and their IT departments are sometimes not enthusiastic about new software, so the users' software needs to be as easy to install as possible, as well as easy to use. I don't know much about databases and program occasionally, at present in C sharp; so simple explanations would be welcome please.

Since there are no legacy restrictions it's an opportunity to start in the right direction, which is why I'm asking the question at an early stage. Thanks for any responses.

egginstone
  • 19
  • 1
  • 1
    You wrote: *"so the user gets a small Access file for each download"* - so it is important the users can handle such data, I assume? Who are your users? Do they all have MS Access installed? What is their preferred data format? – Doc Brown Apr 01 '19 at 15:50
  • Excel and spreadsheets are very powerful tools thesedays. Couldt not you provide an Excel file Instead of an mdb? Do the user need DB capabilities? You also said "*The users are generally not software savvy, and their IT departments are sometimes not enthusiastic about new software, so the users' software needs to be as easy to install as possible*". Simpler than Excel can not be. – Laiv Apr 01 '19 at 18:31
  • In the present system the users have my software on their pcs (the old pc program mentioned above). It creates the mdb files from the sensor data and has a few queries and graphs to display it. I'm thinking in the new system to keep the database files on the server, and use a query to send an output (eg Excel or pdf or the database file itself) to the user. Different users have their own preferred format, including some who use the mdb file at present. Is this the best arrangement? – egginstone Apr 01 '19 at 18:48
  • Regarding Excel, I use it to process data for my own use, but some of the operations are cumbersome so a query would be better. – egginstone Apr 01 '19 at 18:49
  • Can you execute queries on these mdb vía command line or API? Does MS allow you to do that whitout running MS Access? – Laiv Apr 01 '19 at 19:15
  • Just a bit of advice. Don't try to force technologies to work where they aren't meant. Could you use Access as a web service DB? Sure. Should you? Probably not. My recommendation is retry MySQL or another DBMS designed specifically for concurrent access. – GrandmasterB Apr 02 '19 at 20:36

2 Answers2

4

The standard solution to such a problem would be to

  • implement a Web application using the technology of your choice

  • create graphs which can be displayed directly in the users's browser, using a standard HTML graphics feature (google for "show graph html" to find some components which can do that for you)

  • provide some extra features for generating downloadable files in any format you like (like PDF, or as an Excel or Access file, if that's required)

This will make your IT department happy, since they will have to install zero extra software on the users' machines - assumed the users already have a modern browser installed, which is quite likely today. And you can use any database technology you like, as long as it is available on the server. If you prefer to use an Access DB as an intermediate store on the server, why not? And this is a decision independent from the DBMS where you manage the user accounts themselves, in case you need one.

Of course, you will have to make decisions about the technology stack and frameworks for the Web application, which is something we cannot really help you with - there are way too many such technologies available for this purpose (and recommending one is 100% off-topic for this site, for good reasons).

However, let me add one thing which I would recommend against: better do not use MS Excel Automation to generate graphs or PDF files on the server. Even Microsoft suggests to use alternatives, if possible. MS Access Web Apps is also a dead technology, according to Microsoft. So better use something which is recommended for server-side use by its vendor.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565
0

Access Databases have a generally poor reputation for reliability. But this is probably mostly due to their history of being the first port of call when a database was initially needed and still being around when the application had long outgrown its roots.

Also, traditionally they required Office to be installed on the PC which was often a problem on servers.

It sounds like the data you are collecting will always be accessed through the application rather than directly from the database, and thus the database choice shouldn't matter to the end user.

Given this I would go with a 'proper' database, probably a cloud based one for the server, and yes, it sounds to me like you will also want a local database to buffer the data before sending it to the server.

For this I would not try and use the same database. I would use a sqlite (or similar) file based database which requires no deployment other than the dlls deployed with your application.

Robert Harvey
  • 198,589
  • 55
  • 464
  • 673
Ewan
  • 70,664
  • 5
  • 76
  • 161
  • This conversation has been [moved to chat](https://chat.stackexchange.com/rooms/91865/discussion-on-answer-by-ewan-small-databases-online). – Robert Harvey Apr 02 '19 at 15:17
  • my point is not that your edit is bad, but that the tone and presentation of answers in general has importance in addition to the facts presented. You might not like my prose, but if all the answers end up with the same style they will be rather dry and boring to read. – Ewan Apr 02 '19 at 15:23
  • Check your answer's score. It has gone from -6 to +16 since the edit. – Robert Harvey Apr 02 '19 at 15:27
  • 1
    Popular and inoffensive != good – Ewan Apr 02 '19 at 15:32