Background: I have been asked to develop a database to replace an existing 2002 MS Access database (for obvious reasons). This company has been using this database since 2005 so it is extremely outdated and does not perform the necessary report generation or queries required for their current needs. It also doesn't lend itself to very user friendly data entry or have all the necessary fields they desire.
The database is one gigantic table with about 130 fields. Every time even repeat customers use the business a new record is made. The database has grown to well over 65,000 records in their current business dataset and over 100,000 records which have been "purged". They use about 20 different reports and I see about 40 different queries (some no longer used). I have limited database experience, however I have in the past developed several very functional MS Access databases with some pretty cool functionality and am familiar with VBA. I truly don't have any experience with SQL, however I am confident enough to know with some tutorials, example code I can cut and paste, , and maybe a trip to Barnes and Nobles I can teach myself enough to accomplish the task. I just know that MS Access doesn't quite foot the bill for their current needs (speed, size, concurrent updates, multiple connections, etc.).
I would like to automate this process as much as I can in order to provide some sort of product for use for them relatively quickly. My question and idea is......if I can convert the old 2002 Access database to MS Sql Server Express, can I then convert the MS Sql Server Express database to the free open source MySql. The cost of MS SQL Server with licensing could end up being rather hefty so I would like to possible leverage the free MySql.
The database will be primarily used on the local network. Some users remote desktop in currently to access the data (I'd like to get them on VPNs in the future). End state, I would like to create an application tailored to their specific needs and more modern and functional, potentially accessible from the web (they have a site and domain), and also potentially in the future a mobile application for easy access to the data. I've looked at App Development solutions and have found IronSpeed which seems very cool (any opinions?) My overall question is: Is the scenario I described above possible? Will it support the future implementations I am looking to source/create? Would it be too difficult to teach myself the basics needed to program MySql (My background it IT, however not databases). I think this could be a good first large project to learn with.
Thank you for your time and reading. If anyone has any suggestions/alternate solutions it would be greatly appreciated.
