View Single Post
  #1  
Old 09-17-2006, 01:08 PM
 
soyuz02 soyuz02 is offline
 

Advanced Member
  
Join Date: Jun 2006
Posts: 71
 

Lightbulb Linking Microsoft Access with X-Cart using ODBC

Hi everyone,

I use both X-Cart as my web store and Access in the backend with its database facilities. I was doing a lot of keying in twice, very time consuming. I was looking for a way to somehow link the two. At the same time you can administer products of through Access. I dont know about you but its a lot faster than keying everything into the web interface. (Yeah I'm one of those people who just dont understand CSV files and how they work).

So I finally managed to integrate the two and wanted to put a small tutorial up in case more people would like to know how its done.

************************************************** *****
Step 1: Installing the MySQL ODBC Connector
************************************************** *****

This ODBC connector is like the interface between the MySQL and your computer.

Goto http://dev.mysql.com/downloads/connector/odbc/

Download the appropriate ODBC version and install it "Complete"

********************************************
Step 2: Starting the ODBC Connector
********************************************

Under Windows, the ODBC connector is located under:

Start > Control Panel > Administrative Tools > Data Sources (ODBC)

Now you are going to setup a Data Source Name (DSN). There are two types we are concerned with, a USER DSN and a system DSN. A USER DSN sets up the connection only for your current user whereas a SYSTEM DSN sets up a connection for your entire computer. I always went with SYSTEM DSN

Goto "Add" and in the list which comes up, you can choose your ODBC driver. You are going to choose the one which you just downloaded "MySQL ODBC x.XX Driver" and click "Finish". Now you will have a MySQL screen come up.

************************************************
Step 3: Setting Up the ODBC Connector
************************************************

In the MySQL screen which comes up you have different options to configure now:

Data Source Name: choose a name
Description: a description of the connection
Server: the server where your MySQL server is running (either IP or domain)
User: your MySQL superuser (typically root)
Password: password of your user
Database: leave blank, the program will populate this with values if the connection is made successfully

Click Test.

Now you should get a window saying "SUCCESS". Now you can select the databse from the drop down list, the last field, in this case your xcart database.

If this works you can skip to the next step.

If you don't get the SUCCESS message, and instead get something like SQL_ERROR, please read this. There are many errors but I am going to concentrate on this one because it seems the most common one.

The problem here usually is that your SQL superuser is only allowed to be used from the same machine on which your SQL server runs and not from an external host.

What you are going to have to do here is change the privileges for your mySQL user. To do this, login to your server shell.

When youre at the shell, type this:

Code:
Shell> mysql -u YOURSQLUSER(USUALLY ROOT) -p

Now it should ask yor your password, type your password.

Now the shell indicator should have changed to "mysql>"

Now type this:

Code:
use mysql;

Now it will say "Database changed". Dont panic, it hasnt changed anything, it just means youre using the mysql database where all the users are stored.

Now issue this command to allow you to connect with your user from anywhere:

Code:
GRANT ALL PRIVILEGES ON *.* TO yoursqluser@"%" IDENTIFIED BY 'your password' WITH GRANT OPTION;

Now it should have said "Query ok, no rows changed, X.XX secs".

Ok, all done, now you just need to flush the priviliges, basically it restarts them to make sure you use the new changed ones.

Issue this command

Code:
flush privileges;

That should do it for this error.

************************************************** ***
Step 4: Linking Access to the SQL database
************************************************** ***

Open MS Access, and select "File > Open" and under File type, select "ODBC Databases".

Now a window comes up and you select the tab "Computer sources" and then select your connection which you named earlier and click "Ok".

Your done. Now simply select all table you wish to link to access and thats roughly it. You should be able to take it from here.

You can also create forms that work with the linked SQL tables.

I hope Ive been able to assist with my post because I can imagine that there are a few people seeking to do something similar!

Hope it works
__________________
X-Cart Version :: 4.1.6 (Finally)
X-Cart Mods :: Kids Skin by 7dana
Running on :: SUSE Linux 9.3 with Plesk 7.5.4
Webserv :: Apache 2.0.53
mySQL :: 4.1.10
PHP :: 4.3.10
Reply With Quote