A quick question to any database developers: I have been using MS Access 2007 for a while now as a front-end to a MySQL database backend and distribution applications using the MS Access 2007 Package and Runtime facilities. Has anybody had any experience of using any other back-end database technology with MS Access as MySQL can be a bit of a pain unless you put in certain fields (such as timestamps and integers) to reduce 99% of errors?
Why don't you use MS SQL for both entities and save yourself any worries? You can use the Express version (free) for the online stuff and the redistributable components for embedding in your applications and you won't have any compatibility issues.
MS Access is made to work more easily with MS SQL Server. You might find that option works a bit more seamlessly. But honestly, once you get over the initial hurdle of setting up the field types, MySQL + Access works very well. It's a sustainable option, it takes a lot of setup at the beginning but it is relatively trouble-free in the long-run. And MS SQL Server, like any Microsoft product, comes with its own set of headaches, not to mention a price tag. Any advantage you get from using MS SQL Server instead of MySQL are going to be modest at best. In terms of other options, they're not good. I tried using Access with PostGreSQL once just out of curiosity; don't waste your time! That's a nuisance to set up, and PostGreSQL alone is harder to install and set up than MySQL. I've also used Access with an Oracle backend; that's not at all fun; in my case the Oracle database was not under my control and there were just some things I couldn't do because of the data types not lining up, but it makes me think that even if you were to set it up yourself, it would take at least as much time/effort as MySQL. The only reason to use Oracle IMHO would be scalability for very large and heavily-used databases; it is unparalleled in this regard. Compared to these other two, MySQL is definitely the easiest option. My advice would be to tough it out with MySQL. Just set up each table right, with the timestamp, watch your data types, and you will probably be fine in the long-run.
according to me the ms sql is best for your application you can also connect it through the oracle but ut require some queries to connect it