Wow.  If this is what a "Professional" is now expected to know, I'm sad for .Net.

[more]

"Hi Smith! Remember the project that needed to allow entry of date values but the users couldn't be forced to any specific precision?  I have the same requirement and can't remember how you did the precision metadata, we're on SQL Server, not Oracle, was it a PLSQL ability?"

:)  Uh... you're thinkin' too hard, budd.  All I did was add a sibling column to the DATE field and packed it with the id/enumval of the desired datepart precision.  

I gotta laugh about my saying that you're thinking too hard, it's ironic...

[more]

"Hey Smith!  Got that country code table SQL?"

Yeah, right here.

featured in Mike Gunderloy's Sybex Press book ADO AND ADO.NET Programming"

If you're a big iron RDBMS coder, consider that dynamically created Jet files as middleware have come in very handy for lots of folks using Oracle and SQLServer in the enterprise, so this is not just a power for those using Jet as a core source.

Years ago we noticed that we were spending more time writing DAO object code than working with data. So we took an afternoon and wrote the first SmithVoice.com DBCodeCreator. It saved us lot of time and other devs seemed to like it so we started giving it away on the SmithVoice.com sitelett.

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.

I find it interesting how many folks have never heard of Datalink files or only know of them as part of the VisualStudio wizards.

A data link is nothing more than a file-based connection like a file-based DSN; what's more making a data link is as easy as right-clicking on your desktop, choosing to create a new text file then changing the file's extension from .txt to .udl. Voila, power!

I was not at first a fan of using UDLs. But...

Some things in development are not sexy, in fact they are downright boring, but you have to just pound out the code and get the job done before you can move on to the real cool, shiny and new stuff.

Consider the lowly mainframe text file dump. A big old block of characters that one corporation sticks on an FTP site and another one has to pickup and deal with.

It is true that one beautiful day in the future such files will be all XML and BIZTALK will have put that robber baron EDI out of business but till then, and unfortunately it *is* a ways off, corporate developers still have do deal with those cobol-born chunks of ASCII.

The first time you figured out a badly documented file format and finished the hard-coded parsing routine you have to admit that you felt darned proud of yourself. But the day that the format changed the thrill waned, and a few new files later the whole process was nothing but a boring chore.

Text as Relational Database? You Bet!

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 ...

This page builds off of the SmithVoice.com Export To Anything basics page.  Look there for the starting stuff, this page is devoted to workarounds for specific odd Excel errors.

A refresher for Excel conversions:

As promised, here's the downloadable project source code for the demos shown and the demo I didn't have time to show. Also, please look for more details on specific source types on the Export To Anything and Relational Text Files pages here on smithvoice.com.

Demo1: Shows how to export results of ANY Jet query to ANY ISAM format

Demo2: Shows how to convert a single table or a whole database from ANY ISAM type to ANY ISAM type without any automation, all with VB