I have an Access 2003 db that I've designed to run inventory for a sports memorabilia business. I'm realizing that I should get this data onto the web and run a storefront via MySQL & PHP.
My concerns are that the current db has multiple Append Queries to add stock and transacted items. If I were to utilize an ODBC connection from Access to MySQL, would I be able to run these queries and update source tables in the Access db and have that reflected in the MySQL database on line?
Secondly, the inventory quantities are a query in the Access db, based on this format: Buy_Qty: Sum(IIf([Trans_Type]="Buy",[Qty],0)). The inventory on-hand is then the sum and subtraction of the relevant actions toward quantity on-hand.
How easy is it to keep a query in MySQL that would duplicate this data and be used when a web-user wants to search for inventory via a to-be designed PHP form?
I'm sorry if these questions aren't very strong, I'm studying the feasibility of moving online in this manner and I think the best way forward would be to use the front-end I already have and allow the backend to exist as MySQL. Any thoughts on if that's wise?
I just helped a colleague set something up which may be similar to what you want to do.
In her case she wants to use Access as a front end for an application, but connect to MySQL as the datasource. I don't think this a good idea as I would suggest to make the application front end in C# or make it web based using PHP, but she insists on using Access. But the use of the MySQL database on a remote centralized server rather than the Access databases is a good idea since this allows multiple user access, etc.
Anyway, we just downloaded the ODBC connector from MySQL and installed it on her machine. This allows her (and any user of her application) to link her Access front end consisting of various forms to the MySQL database. In her Access forms she defines various queries that are carried out when certain buttons are pressed. And these queries are written in the MySQL query syntax. In some cases she tells me she had to create views in the MySQL database to get some things working.
The point is, I think that you will need to change the queries that you have already in Access to be in MySQL syntax.
I am not sure what you want to do with this duplicating data with PHP. If you put your data in a MySQL database and use this as datasource for your Access part, then there is nothing stopping you from also creating a PHP application that, independently from the Access application, also has access to the database. In my PHP scripts, if I have tasks that require mutiple queries to carry out, I lock all affected tables during these transactions to lock out any other user (coming from Access or PHP) during the carrying out of the script.
Here of course I am talking about linking your Access to the MySQL database, as opposed to doing an Access copy of the MySQL database into a local Access database.