1

I currently have an Excel spreadsheet acting as a small data base, about 2000 entries. It is fine for our needs but we now need to allow a couple of staff to securely and quickly access subsets of the data from their cars.

I was thinking the best way to do this was to migrate from Excel perhaps to MS SQL server. (we currently have our website hosted on a Windows server with the option of an MS SQL Server so thought this was a logical plan.) I thought I could then design a simple user form to allow staff to search and query the DB via a browser on a handheld device (phone or tablet).

My questions are

  1. Does the above like a reasonable solution?

  2. Can the excel spreadsheet be easily migrated to an SQL server DB?

  3. What tools are available to quickly develop simple search and display GUIs for this DB if I go this way? I have a good understanding of Relational DB theory and SQL but very limited experience of development. Also time and money are limited so the simpler the better.

Also if my approach is not the most sensible one I would love to hear alternatives. We currently have the excel WB on MS SBS server accessbile via VPN (don't have MS server at the moment). Maybe could get MS Server and make DB available that way?

1
  • Since you already have MS Office, why not use Access?
    – RichardM
    Feb 7, 2012 at 14:29

3 Answers 3

1

I think your proposed solution would certainly work. But if you haven't purchased the license for the Microsoft products yet. I recommend simply using the free MySQL database and use a free apache+PHP web server to host the front end, would also work.

1
    • Yes - assuming that you are using Excel to store data and not actually doing any calculations... This is not a stopping/major factor, but, it would change this from a 5-10 minute job to possibly an hour for simple calculations down to many months if it is highly complex.
    • Last time I did this, I found the easiest solution involved saving as CSV from Excel then using a CSV to Mysql tool - I am sure things have changed, but, I found this to be very easy.
    • Sorry, can't help you here :(

As for recommendations, if your SBS server contains SQL Server (or even if it doesn't), I would go down the SQL Server route as with Entity Framework and more, this could probably be set up in no time at all.

0

If the webserver is a public webserver, you'll need to set up security both to control access to the data and to secure communication between the working clients and the database.

I really like the idea of migrating to a web database. Interfaces are easy to set up, and freeing your data like this allows for great flexibility. However, you'll need to be aware of the security issues, access issues, backup configuration, to name a few considerations.

If your web host goes belly-up, how will you get your data? How will people work if the internet is down? Who is responsible for backups? Is there a transaction limit on your webhost? Is there a data transfer limit for your webhosting account?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .