[AccessD] New Database Table - How to Proceed

Hi Brad

With three users making 40 updates a day, an Access backend is the perfect choice.

However, there would be no reason to not use an SQL Express version as backend if you can dedicate a machine to host it. It could be an old XP machine with 500 MB ram and a decent disk. Given that you already joggle with three different database engines plus Access, it may not be too clever to introduce MySQL as a fifth.

/gustav


-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Brad Marks
Sendt: 24. marts 2014 22:16
Til: Access Developers discussion and problem solving
Emne: [AccessD] New Database Table - How to Proceed

All,

I have a question, but first I need to explain a little background.

I work for a small manufacturing firm.  The IT budget is VERY limited.

We have a purchased Accounting system that uses a Pervasive Database.

We have a purchased Manufacturing system that uses a Firebird Database.

We have a Website that is maintained by an outside party.  The website is hosted by GoDaddy.  It uses a Microsoft SQL Server database.

Because all three of these databases are accessible via ODBC, I have been able to use Access 2007 to build several inquiry and reporting systems.  Access works great for this purpose.

Currently, updates are only permitted "through the front door" of these three systems using the screens/pages that are provided.  No updates via ODBC are permitted.

We do not have any "in house" database that can be used for updates other than Access 2007.

Recently I have starting working on a new project (using Access 2007) to streamline our internal order processing.   In order to do this, I will need a new database with one table.  Because of the size of the firm, the number of orders is quite small, therefore the number of updates to the new table will be quite small.

When this new application goes live, there will be three users who will need to make a total of about 40 updates per day to this new table.  In addition, there will be an Access 2007 batch application which will be run two times per week.  This application will make approximately 100 database updates/additions each time it is run.

Originally I was planning to use an Access 2007 (Split Back-end) database for this application.

Currently, no "Critical Business Data" is stored in any Access Database.

I was also thinking about storing this new table in the Web Database (SQL-Server on GoDaddy), but our Web Consultant has vetoed this idea.

He also strongly recommends that we do not store the data in Access.  He recommends that we use MySQL.




So, here are the options that currently come to mind.

Microsoft Access 2007 (Split Back-end) - Despite what our Web Consultant says.

MySQL  - or one of its Forks (I have no experience with MySQL) 

Local Install of Microsoft SQL Server (I have concern about the price tag and the amount of work - seems like a large overkill for just one table) 

Microsoft SQL Server (via a paid Hosted service - we have no experience with this approach) 

Given all of this as background, which approach would be best?

Other options ?

Everyone here on AccessD has more experience than I do in this realm and I value your ideas and insights.

Thanks,
Brad