[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL ,Microsoft Access, Online Shopping Cart

Posted on 2007-10-09
7
Medium Priority
?
808 Views
Last Modified: 2008-01-09
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.  
0
Comment
Question by:livinlif3
  • 3
  • 3
7 Comments
 
LVL 8

Assisted Solution

by:Bradley Haynes
Bradley Haynes earned 400 total points
ID: 20044022
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.

http://mysql-form-generator.qarchive.org/
http://www.fileflash.com/allfiles/mysql_database_form/
0
 
LVL 19

Accepted Solution

by:
v2Media earned 1600 total points
ID: 20044327
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.
0
 
LVL 3

Author Comment

by:livinlif3
ID: 20049438
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!
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 19

Assisted Solution

by:v2Media
v2Media earned 1600 total points
ID: 20052466
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 (http://www.dbqwiksync.com/). 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.
0
 
LVL 3

Author Comment

by:livinlif3
ID: 20058739
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.

0
 
LVL 19

Assisted Solution

by:v2Media
v2Media earned 1600 total points
ID: 20061648
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:

ODBC -
http://office.microsoft.com/en-us/access/HP051890191033.aspx?pid=CH063648361033

Linked ODBC Tables -

http://office.microsoft.com/en-us/access/HP030893711033.aspx?pid=CH063655171033

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.
0
 
LVL 3

Author Comment

by:livinlif3
ID: 20148169
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In the below post we have mentioned the best hosting type for startups. Also, check out some of the superlative web hosting companies that are proposing affordable web hosting solutions to host your startup website.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question