[ic] How to use MSAccess with Interchange

[Ed LaFrance]

At 08:58 PM 7/3/2002 -1000, you wrote:
>It's pretty stone-aged text but It'll get people who understand
>databases up and running.
>
>You can use MS Access to Integrate with Interchange.  Must be using a
>non-textfile database (eg.MySQL)
>
>- Install an ODBC Driver...
>         I use MyODBC 2.50.39 (Do A Search - note it works on XP as well)
>- Set up the ODBC to connect to your server
>         - Use a UserDSN
>         - Enter all Information asked for
>         - If you need to change this info later go to Control
>Panel/Administrative Tools/ODBC Sources
>- Start Access
>- To Link a Database click Open, type: ODBC Databases(), Select the
>ODBC(), it will import link tables.
>- To Import Click import and do the same as the last (note to export you
>will have to download a add-on called MyAccess, using myaccess rename
>all the server databases to [name of table]2 for backup, then move your
>imported tables over to the server with MyAccess (note the reason we are
>doing this is that it changes data type lengths and not NULL filters but
>will allow you to export without trouble meaning errors).
>         (Its easier starting with a Linked database. And using MyAccess
>to allow NULL for most entries - this will get rid of errors)
>         - Now you can export tables from Access without using MyAccess
>(I however just link my tables)
>- You can set up forms to interact with the database
>- The first form you should set up is your products form.  Then set up
>an options form, then a inventory form.  Inventory and Options can now
>be placed as subforms of the products form.  This way data will be
>filtered exclusively.
>- Use a test database while you set up (foundation, keep note of table
>data to help you set up forms)
>- This is much quicker than using the UI, and can be a nice way for
>customers to set up products, manage orders
>
>Chris Chaney

I can add a couple of considerations to this:

1. The mySQL user for the database on the server needs to be specified with 
a wildcard in the domain - i.e.: username@% instead of username@localhost, 
if you want remote access.

2. Port 3306 or some other port needs to be open on the server, and mySQL 
configured to used it (3306 is the default). This is general the case by 
default unless the server admin has explicitly closed it off.

3. It seems to vary by Access version, but a bug will cause problems when 
trying to write data to certain foundation tables from Access in linked 
mode; the error will be something to the effect that "the record has 
already been modified by another user.". The solution is to add (and 
populate) and timestamp field to that table - that seems to clear up the 
confusion for MSAccess.

- Ed L.


===============================================================
Increase profits from your Interchange store...
http://www.newmediaems.com/cgi-bin/nm/software_fus.html
===============================================================
New Media E.M.S.               Software Solutions for Business
463 Main St., Suite D          eCommerce | Consulting | Hosting
Placerville, CA  95667         edl@newmediaems.com
(530) 622-9421                 http://www.newmediaems.com
(866) 519-4680 Toll-Free       (530) 622-9426 Fax
===============================================================