One of our most popular topics is the Smithvoice.com "Export To Anything" technique which allows you to convert and export any ISAM without automation or even having the source or destination native programs on the user's machines.  Need to turn Jet data into Lotus 123?  It is a simple issue of using an undocumented JetSQL query syntax and running the SQL off of the Execute method of a DAO database or ADO connection object.

But Oracle, Sybase, DB2 and SQLServer developers had thought themselves out in the cold on this .. figuring that they have to bite the bullet and pay the silly prices for 3rd party data pumps or (egad!) add full blown MS-Access installation to their systems and use it's not-so-fast ODBC linking in order to use this powerful technique.  

If you are in the CS world, here is the code you have been waiting for.  A simple class with a single method that takes any disconnected ADO recordset and converts it to the ISAM of your choice.  Oracle to Excel (4, 95/97 or 2000), SQLServer to HTML Table, Sybase to Jet 3 or 4, DB2 to dBase 3,4 or 5.  Whatever.

Added bonuses:  with one setting you decide whether exports will overwrite a previous export or raise an error to your users and if you choose Jet as the export you can specify whether to add new exports to an established file or create a new Jet file for each export.

How it works:

The core is the same JetSQL made popular in the Smithvoice.com "Export to Anything" but there are a few extra steps that the class takes care of for you.

1) You create a disconnected ADO recordset based on any query you desire; use joins, table and column aliasing, SQL calculations, whatever ... use the SQL dialect of your source RDBMS and just create the recordset.  

(with a couple of asides:  

    a) if you do CONCATs or other special field-level SQL make sure that you use a column alias that will give the export a valid column header/field name.  For instance if you do an Oracle ... DECODE(T.Source_Col, 1, 'True', 0, 'False')... you will get an error in the export because the engine will try to use the whole function as the export file's column header. In this case you should make sure to alias the column with either the original or a new label as in ...DECODE(T.Source_Col, 1, 'True', 0, 'False') "New Col Name"...

    b) The destination ISAM format may have restrictions you will need to account for.  For instance versions of xBase (dBase3/4/5 & FoxPro2x/3) have 8 and 10 character limits on field names and do not allow embedded spaces.  The good news is that if you try to break the NoSpaces rule for these ISAMs the driver will insert an underscore for you; the not so great news is hidden a bit deeper:  If you have an alias longer than  the format's limit the driver will truncate.  This seems handy until you have 2 source columns named or aliased "MyAddressOne" and "MyAddress2".  In this case the driver will throw a "Duplicate Columns Not Allowed" error as both aliases are truncated to "MyAddres" 

)

2)  You instantiate an RSConverter and call the ConvertRSToFile method, setting the parameters you need with the help of VB's Intellisense. 

3)  The class then takes it all from there as follows:

• A temporary Jet file is created in the computer's Temp folder
  
• The recordset is analyzed column by column and a table is created in the temporary Jet file with column types matching those in the recordset (as closely as possible, since there may be no direct Jet support for all the data types your source is comprised of.  Remember, this is not an issue since the point of the class is to give you an ISAM export)
  
• For each record in the source recordset a standard SQL INSERT dml statement is created and line by line the Jet table is filled with the records from the source.
  
• When all the records are in the Jet table, the Export to Anything JetSQL statement is run to create the export file
  
• The temporary Jet file is deleted.

When you look at that, it all looks pretty obvious, doesn't it?  But year after year so many developers shell out money for Data Pumps or do manual cut & Pastes from SQLWorksheet to Notepad then call on Access' Fixed Length Import Wizard.  We'll have no more of that <g>.

Now, since most of the power is coming from using Jet as middleware, the class does have Jet requirements.  You will need to set refs to both ADO2x and ADOX2x and use an MDAC that installs the Jet and Jet-ISAM drivers.  This means that you will need the MDAC for ADO2.1 or 2.5, since the short-term MDAC2.6 does not include any ISAM support, not even Exchange support!  If you only have ADO2.6 then you will need to get the separate Jet/ISAM support installer from Microsoft's Universal Data Access downloads.  

Want to try it out?  Click here to download the class and a simple demo project (just 6k!).

We hope it helps!

Robert Smith
Kirkland, WA