Article:		ColdFusion MX on Mac OS X, Part 3
Subject:		Converting Legacy Data (Access)
Date:		2003-01-11 13:18:07
From:		jodeo
Now that I have CF up and running on OSX, I have realized that it does not have any MS drivers except for MS SQL Server. I have a few 'old' db's in Access (97) that I must have. I have no db app on OSX at present (not even MySQL - yet). My questions are: 1. Is there a way to convert the data tables in Access over to something compatible? 2. Is there an Access driver out there for CFMX J2EE? 3. Are there any other ideas that would make this a straightforward conversion? Feel free to email me at oreilly@cranford.ws. Thanks.

Showing messages 1 through 4 of 4.

• Converting Legacy Data (Access)
  2003-01-11 16:18:36  dicklacara [Reply | View]
  
  
  My questions are:
  
  1. Is there a way to convert the data tables in Access over to something compatible?
  
  2. Is there an Access driver out there for CFMX J2EE?
  
  3. Are there any other ideas that would make this a straightforward conversion?
  
  

  
  
  1. There are several ways to convert MS-Access tables to some other database:
     
     
     
     
     • Run Access and SQL-Server under VirtualPC -- there is a Wizard that will convert the Access db to an equivalent SQL-Server db
       
       
     • Export the Access tables to CSV or TSV text files. Write CFMX programs to read these files and insert the data into whatever db you desire.
       
       
     • Use a setup available from OpenLink Software http://www.openlinksw.com which allows you to run their program under VirtualPC to interface mdb files with their JDBC driver. The OpenLink driver can be used by CFMX. In effect, OpenLink appears to be a MS-Access Server available (with an IP address) on your network. OpenLink has a free-trial period (30 days). The cost is $500 to purchase the software.
       
     
     
     
  2. There is no MS-Access driver for anything but windows. In fact, the Jet Engine (the MS-Access database program), itself, is implemented within the driver. So, the driver is the database system and the database system is the driver.
     
     
     
  3. Of all the approaches, I prefer the OpenLink solution. It allows you to create a set of CF programs to do the conversion. You can reformat, validate, normalize, etc. in the process. The process is fast and repeatable -- I used a large MS-Access database that I had converted to SQL-Server several years ago. I had done major reformatting, validation and normalization in the process. I took that same set of programs and converted it to Sybase_ASE -- the whole thing ran on the Mac.
     

  
  
  The OpenLink setup is too involved for this space. I plan to cover this in a later article.
  
  Dick