View Single Post
  #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