Follow us on Twitter X-Cart on Facebook Wiki
Shopping cart software Solutions for online shops and malls

Linking Microsoft Access with X-Cart using ODBC
 
Reply
   X-Cart forums > X-Cart 4 > Changing and adding new features
 
Thread Tools Search this Thread
  #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

The following 2 users thank soyuz02 for this useful post:
chamberinternet (03-04-2013), flamers (08-29-2011)
  #2  
Old 09-18-2006, 07:02 AM
 
balinor balinor is offline
 

Veteran
  
Join Date: Oct 2003
Location: Connecticut, USA
Posts: 30,253
 

Default Re: Linking Microsoft Access with X-Cart using ODBC

Moving to Custom Mods
__________________
Padraic Ryan
Ryan Design Studio
Professional E-Commerce Development
Reply With Quote
  #3  
Old 09-18-2006, 08:16 AM
 
nevets1219 nevets1219 is offline
 

eXpert
  
Join Date: Jun 2006
Posts: 351
 

Default Re: Linking Microsoft Access with X-Cart using ODBC

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.
__________________
4.1.8
Reply With Quote
  #4  
Old 10-17-2006, 03:00 PM
 
MrHeeltoe MrHeeltoe is offline
 

eXpert
  
Join Date: Jan 2006
Posts: 206
 

Default Re: Linking Microsoft Access with X-Cart using ODBC

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.
Reply With Quote
  #5  
Old 08-01-2007, 12:35 PM
 
MBA MBA is offline
 

eXpert
  
Join Date: Apr 2006
Posts: 245
 

Default Re: Linking Microsoft Access with X-Cart using ODBC

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
__________________
xCart Pro Version 4.0.17, 4.0.19, 4.1.8, 4.1.10, 4.1.11, 4.1.12 - retired
xCart Pro Version 4.3.1 - production
xCart Pro Version 4.5.1 - testing
RHEL Platform
Reply With Quote
  #6  
Old 08-02-2007, 07:32 AM
 
geckoday geckoday is offline
 

X-Wizard
  
Join Date: Aug 2005
Posts: 1,073
 

Default Re: Linking Microsoft Access with X-Cart using ODBC

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.
__________________
Manuka Bay Company
X-Cart Version 4.0.19 [Linux]

UGG Boots and other fine sheepskin products
http://www.snowriver.com
Reply With Quote
  #7  
Old 08-02-2007, 09:05 AM
 
MBA MBA is offline
 

eXpert
  
Join Date: Apr 2006
Posts: 245
 

Default Re: Linking Microsoft Access with X-Cart using ODBC

Cheers m8 - will leave well alone for now!
__________________
xCart Pro Version 4.0.17, 4.0.19, 4.1.8, 4.1.10, 4.1.11, 4.1.12 - retired
xCart Pro Version 4.3.1 - production
xCart Pro Version 4.5.1 - testing
RHEL Platform
Reply With Quote
  #8  
Old 08-25-2010, 04:33 PM
 
Tom Briggs Tom Briggs is offline
 

Newbie
  
Join Date: Aug 2010
Posts: 1
 

Smile New approach for linking Access to X-Cart MySQL

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
__________________
Version 4.4.0
X-Cart Gold
Reply With Quote

The following user thanks Tom Briggs for this useful post:
flamers (08-29-2011)
Reply
   X-Cart forums > X-Cart 4 > Changing and adding new features


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -8. The time now is 08:44 AM.

   

 
X-Cart forums © 2001-2018