MySQL ,Microsoft Access, Online Shopping Cart

Hello Experts,

Currently the organization I work for uses an access database to store customer information.  We are interested in using a online shopping cart. (for some stock products)  I've tested oscommerce, to implement on the current website.  The shopping cart uses MySQL, should I link up the mysql database with our current access database and have it synchronize? (at a certain time during the day)    (I've tested using the odbc mysql driver)
 With the mysql information linked up to the access database I can then be able to create forms to show the customer information?   Does mysql have a program that can create forms?  I'm new to this so please bear with me.  
Who is Participating?
Good solution, however bear in mind that MySQL's ODBC driver has issues between Access and MySQL server v5+. MySQL datatype double is interpreted as strings. This bug has been slowly tracked and fixed since odbc v 3.51.17. The current version, 3.51.21 resolves the issue between MySQL v5+ and MSSQL server.

If your MySQL server is v4, no probs.
Bradley HaynesCommented:
OScommerce is quite decent as far as shopping carts go. Using MySQL is preferred to Access. Why not import the Access data into MySQL.
If you need to keep Access - - - create DAO's and link via ODBC to MySQL.
You can create forms in whatever server side language your host supports ( the smart money is on PHP) but not directly in MySQL.

Have a look in the PHP forum for more.
There are also several software titles you can use.
livinlif3Author Commented:
Very good information...I read on a website, you can transfer locally stored Access tables to MySQL, then set up links in the Access database that point to the tables.(how do I go about this, is the DAO's you're talking about).    (does this mean, we still could use the Ms Access database to store customer information)?  Should, a whole migration to a web based database be smarter?   Migrating to a web based solution, the users would essentially be logging onto the admin portion of the oscommerce shopping cart?  And would it be difficult adding tables and form fields on the oscommerce default php pages?  Thank you for your responses!
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

It would be easier to use OsC as your customer database. To sync records between Access and OsC, I would recommend a 3rd party app such as dbQwikSync Pro ( OsC stores customer data in separate tables from admins, so no, customers don't get access to the back-end.

However, you'd have to change the structure of your Access database to reflect the structure of OsC. dbQwikSync can sync records both ways, from OsC to Access and vice-versa. Changing the structure of the OsC tables to suit your Access db isn't advisable.

Alternatively, you could also install a web server app and PHP onto one of your office computers and have an intranet site for staff to use instead of Access. This would entail hiring a PHP coder to write up the scripts to connect to the OsC database and your required forms/reports. The beauty of this solution is that you would then be interacting with OsC in the same language/db that it's developed in.
livinlif3Author Commented:
I was referring to my sales users.  Sorry, I wasn't clear on that.  I would want to limit access to the sales users to only the orders portion while logging on to the admin portion of oscommerce.  The organization has been using the access database for about 6 years now.  I'm thinking it would be very difficult to cater the website to incorporate all the forms and reports from the access database.  I think plan of action is  linking up the sql database to link to certain tables on the access database.  Then synch up daily any customer information that may have been inputed on the website during there order process.  So basically when the customer calls, the sales rep can make changes to the order through our msaccess database.  There would be a huge amount of configuration to the web site?

So basically, how would I go about linking the tables in access to mysql to sync up the customer information when they order from website.   I'm trying to eliminate the need for sales reps having to input the information twice.  Our current website has only a form that sends email, then gets distributed.  By having the customer login to website and by them typing in the information then the sales rep will not have to re-enter customer information.

What's your opinion on this.

Well in that light, you can use access as a portal to the OsC database. Forget syncing the tables. By 'portal', I mean Access can use linked, or live, odbc tables from the website.

This is done by installing MySQL's odbc driver (up to v3.51.21 now) and configuring a system dsn datasource to the OsC MySQL database. You'll have to get the office's IP address access through the webhosts firewall on port 3306 and add the IP to the hosts list of the mysql database. Links to live tables are created thus:


Linked ODBC Tables -

Do this with a beta version of the access mdb first, and !!! back-up your OsC database !!! before attempting any update, insert or delete queries.
livinlif3Author Commented:
Thank You Everyone for all the information.  I've been using some different syncing utilities, import and export utilities, and some programs that are GUI based to create quick tables and php forms.  I'm not an expert with programming but (I'm a network manager) these GUI based programs make it real easy to create a nice php driven website.  I hear the general practice of importing ms databases to SQL, are by cleaning up the tables making sure there isn't any spaces in the tables and fields, and making sure each table has the correct values.   Thanks everyone for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.