X-Cart forums

[soyuz02]

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:

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:

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:

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

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

[balinor]

Moving to Custom Mods

[nevets1219]

Sounds good. But for people on shared hosting, they might have to allow certain IP address (ie your computer's IP) to connect to the MySQL table.

[MrHeeltoe]

how can I stabilize the connection? It is on and off and on, etc. I can see lot of potential here but right now it is too frustrating to even try.

[MBA]

I can connect using the ODBC but as soon as i try and open the connection in Access i get an error

Basically - Access Denied for user '****MY USERNAME***@'**MY IP ADDRESS*** to database 'MySQL'

The username works on the localhost but doesnt seem to work with my IP

[geckoday]

You should make sure you have SSL setup for MySQL or tunnel through an SSH connection when doing this. Otherwise, you'll have all your data traveling unencrypted across the internet.

[MBA]

Cheers m8 - will leave well alone for now!

[Tom Briggs]

I'll offer some VBA code demonstrating how I accomplished this. I'm using MS Access 2000 on a Windows 2000 machine.

First download the ODBC Driver from MySQL.com. Install but no need to go any further with Windows ODBC setup.

Put the code below in an MS Access module. Anywhere you see brackets <>, replace with your information (no <>).

From the Immediate window run:

LinkMsAccessToXCartTables

You will get a few popups asking for the primary key field(s) on some of the tables. Some X-Cart tables do not have a primary key. I just made guesses, sometimes choosing one field other times multiple fields. I'll learn how this plays out in time.

In the end you will have linked tables available to Access that can be treated just like a local table.

There may be more issues beyond this point that I have not discovered yet. But so far it looks like I'm on the right track.

(I went over my message size limit, so where you see MissingCodeBlock.txt click on the link to retrieve the missing lines of code.)

Sorry, code indenting was lost somewhere.

======================================
'
' Demo for X-Cart Forum
'
Sub LinkMsAccessToXCartTables()
'
MakeTableLinkedToMySQL "xcart_address_book"
MakeTableLinkedToMySQL "xcart_amazon_data"
MakeTableLinkedToMySQL "xcart_amazon_orders"

MissingCodeBlock.txt

MakeTableLinkedToMySQL "xcart_xmlmap_extra"
MakeTableLinkedToMySQL "xcart_xmlmap_lastmod"
MakeTableLinkedToMySQL "xcart_zone_element"
MakeTableLinkedToMySQL "xcart_zones"
'
End Sub

Sub MakeTableLinkedToMySQL(sMySqlTblName As String)
'
Dim sXCartServer As String
'
On Error GoTo ErrHandler
'
sXCartServer = "ODBC;DRIVER=MySQL ODBC 5.1 Driver;SERVER=<www.yourstore.com>;UID=<MySQL User Id>;PWD=<MySQL Password>;"
'
DoCmd.DeleteObject acTable, sMySqlTblName
DoCmd.TransferDatabase acLink, "ODBC Database", sXCartServer & ";Database=<Database Name>", acTable, sMySqlTblName, sMySqlTblName, False, True
Exit Sub

ErrHandler:
'
Select Case Err
Case 7874 'Microsoft Access can't find the object
Resume Next
Case Else
Stop
End Select
'
End Sub