2

I'm trying to aggregate about 500-1000 MS Excel files that deal with various small business issues (inventory, purchase orders, etc) and I'm currently trying to decide what kind of RDMS I should use. To give you more detail, the database will be mainly used by 1 maybe 2 people hosted on network server. Currently, I've been fooling around with MariaDB and Microsoft Access. Excel data is easily imported into MS Access and I have some road bumps when it comes to importing that same data using MariaDB. The DB must be easily query-able and must have the capability to easily import data from a spreadsheet.

I'm not sure what direction to go in, do I continue fenagling with MariaDb and MySQL or move onto MS Access. Or, should I be using something else?

Improve this question
2
  • 2
    You might consider re-posting this topic as multiple questions. 1) How to import your excel data into MariaDb. 2) Which RDBMS to use for your project. This question needs additional information such as the front-end to be used for the database (.NET, PHP, etc.). As a rule of thumb I recommend avoiding Access DB for a multiple user environment. Database use tends to grow and Access has a low ceiling in my opinion. MS SQL Server, MariaDb, MySQL and other databases are worth exploring as options.
    – Jeremy
    Commented Apr 9, 2014 at 16:17
  • 2
    Don't forget Postgres. It's much more advanced when it comes to modern SQL features than MySQL or MariaDB
    – user1822
    Commented Apr 9, 2014 at 19:51

3 Answers 3

Reset to default
6

MS Access Vs MySQL

  • MS Access works only on Windows whereas MySQL works on almost all platforms.
  • MS Access does not support Partitioning feature whereas MySQL has composite, Range partitioning support.
  • MySQL is opensource so no cost involved.
  • Performance in MySQL is way better than MS Access
  • MS Access database is more suitable for desktop use with a small number of users accessing it simultaneously. One reason you might choose to use Microsoft Access over SQL Server is for compatibility/sharing. You might need to email someone a copy of your database. People are more likely to have Access on their desktop computer than SQL Server.
  • Security - MS Access is limited to security in terms of user-name/password on the database. Where as MySQL is much more secured and you can configured it with SSL support.
  • MySQL is the most famous or rather more loved amongst the professional folks because of it's multiple choice in custom storage engines. You can have custom storage engines developed in MySQL based on your requirements or choose amongst the inbuilt ones
  • Actually there is no comparison between MySQL as a real commercial heavy duty database, and MS Access which is typically a local use, giveaway in Microsoft Office. It is no good for remote access, its' connection system is not solid. It is generally useless for multiple user purposes.And you are far better using a web based interface for mysql, VB is also limited in functions and power. Consider using one of the free distributions of the Apache Web server which carries php as the scripting language, and the mysql database pre-configured.

Go ahead with MySQL and experience it.

Improve this answer
2
  • 3
    Whilst I agree that Access is a poor choice in many instances, I would add that the OP should also think "Why NOT MySQL (or any other particular RDBMS)"? In the case of MySQL it is not terribly friendly to beginners and the OP may not have the time or inclination to learn about multiple storage engines. That's not to say it's a bad choice - it isn't - but it may not be the right choice. It's worth considering SQL Server Express edition which is free and includes a load of useful, easy-to-use tools, all MS compatible out of the box.
    – Steve Pettifer
    Commented Apr 10, 2014 at 10:32
  • 1
    As Steve indicates, if you are targeting a Windows environment only then SQL Express is an excellent choice: it is essentially the full core DB engine of SQL Server with a few limitations applied and some tweaks to optimise it for small loads where the real thing is optimised for large many-user loads, so you skip some of mySQL's key limitations and have an easy upgrade path to full SQL Server if you need to scale later. For cross-platform needs both SQL Express and Access "fail" of course.
    – David Spillett
    Commented Apr 10, 2014 at 11:45
1

As well as PostgreSQL (which I recommend), you could also look at Firebird (another excellent choice). What is your development environment? If you're replacing spreadsheets, another good option might be Oracle's APEX with Oracle XE. If you do decide to go down the MySQL route, try to avoid non-standard data types like SET and ENUM (porting becomes difficult). They all have external tables or similar for importing .csv data.

Improve this answer
0

Take a look at http://www.mysql.com/why-mysql/windows/excel/

MS Access has limitations, data types, storage size, users access control (since you're going to have it accessed by more than a user).

i think using MySQL will be more useful as your database grows up. it is a powerful dbms and will open doors for future enhancements and migrations.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.