smithvoice.com
 Y'herd thisun? 

“There is nothing inherently expensive about rockets. It's just that those who have built and operated them in the past have done so with horrendously poor efficiency.”
-Elon Musk



Treat Flatfiles like Relationals

TaggedCoding, Database

Text as Relational Database? You Bet!

And not just CSVText, but HTML tables and Excel4 & Lotus worksheets!

Download the fully commented example VB5 project to see how to get around the restrictions of using these ISAM formats as relational multi-table datasources with DAO. 

The demo shows how to import multiple text files into a JET file using SQL via advanced (but easy) DAO techniques and the "Export To Anything" code.;

Using these techniques you'll find all of your SQL is now available to any ISAM, even if the source supposedly can not be used relationally or with any SQL (like Text and HTML tables). 

Plus, with the techniques shown you can not only import and query the various non-JET ISAM files, but you can easily create full file converters (CSV to HTML, HTML to Excel4, Lotus123 to dBase, etc.) for single tables and complete databases.

This opens a lot of doors for your apps.

Using dBase?  This information can help you too!  Jet SQL is far less restrictive than dBase3/4 SQL, with these techniques you can now use full Jet SQL against your dbfs and really give your users a boost!

(ADO simply does not have the performance of DAO against Jet and other ISAMs ... finally even MS is publicly admitting that DAO is the better choice for a number of situations.  However ADOx 2.1sp2 and the Jet4 native provider does now allow linking using the "Jet OLEDB:Create Link" Property of the Catalog object.   If you have DAO experience then this might seem like a very un-intuitive procedure, of course if you have DAO experience a lot of ADO's non-object-model probably seems un-intuitive, however it can be done.  For details see page 597 of the Office2000/Visual Basic Programmer's Guide)

Robert Smith
Kirkland, WA

note: the demo uses CSVText files (but can be easily altered to work against any ISAM format) and there is one special restriction to Text file based databases.   Because the Text ISAM does not support multi-user connections, you must have "exclusive rights" to the "database."  Since all ISAMs except JET files consider the folder in which the tables reside to be the "database" this means that, for Text, you can not have the folder containing the tables (txt files) open at the time of running such code, this is considered a "connection". 

If you try to run the code while the folder holding the text files is also open on your desktop, you will get the error ... just close the folder and it runs fine.   Thus, to use this technique against CSVText files in a production app, test for the error prior to continuing through the rest of the routines.

Click here to get! (just 64k)

 

Advanced example (just 16k!)

shows performing Jet and HTML exports from dBase files at any location
example includes the dbfs, Export To Anything and Dynamic Linking SQL


home     who is smith    contact smith     rss feed π
Since 1997 a place for my stuff, and it if helps you too then all the better smithvoice.com