smithvoice.com
 Y'herd thisun? 

“Nothing is impossible on earth; one must only discover the means with which it can be carried out.”

from Ways of Spaceflight, 1928 (NASA F-622) by Hermann Oberth

Export to Anything!

TaggedCoding, Database

Originally printed as the Visual Basic Programmers Journal Tip of the Month in February 1998 then expanded in the Dec 1999 VB Advisor Magazine.  The middleware trick that goes far beyond just JET to anything..  once you know the Microsoft secret :) 

All of this works the same with ADO.NET, it's Jet power not language/platform!

(Once you get the gist of this... take it to the fullest level, converting from anything to anything.  Check that trick out right here)

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\BIBLIO.MDB")
  
Dim cmd As New OleDbCommand( _
"SELECT * INTO [dBase III;DATABASE=C:\Test Folder].[DbExpt] FROM [Authors]", cn)
  
cn.Open()
  
Try
Dim i As Integer = cmd.ExecuteNonQuery
MsgBox("num recs: " & i.ToString)
  
Catch ex As Exception
MsgBox("ex: " & ex.tostring)
  
End Try

And for an Excel2002 export, use this SQL in your OleDbCommand:

"SELECT * INTO [Excel 10;DATABASE=C:\Test Folder\XLFile].[SheetName] FROM [Authors]"

(that makes a file named XLFile.xls that contains a sheet named "SheetName")


In Smith's VB Advisor Magazine article, the table that was printed
was a draft version.  The final, correct, table of details was accidentally not used
by the magazine's printer.  This happens sometimes.
We apologize for any confusion

On these pages are the correct details for your exports.

(this is Jet information, it works with VB4 and higher, VC5 and higher and Delphi4 and higher)

Everyone tells you how to import, but even MS-VB techs start coughing when you ask how to export with DAO or ADO!  In fact, their own documentation clearly states that it can not be done (see the note on page 314 of Jet Database Engine Programmer's Guide's "Creating an External Table" ... page 375 of the second edition). 

To that statement we can only ask: "If it's impossible, then how does the Access TransferDatabase macro/method work?"  Plus, if MS didn't know it was possible then why did they list "HTML Export;" way back in the early days as a DAO specifier yet never mention how or why it could ever be used?  Is it really just that they would like every VB coder to force a full copy of Access onto every user's machine ... nah, that couldn't be <g>!

Use this code to get data out to different ISAM formats without requiring a complete copy of any DBMS or spreadsheet app on the user machine. 

Create a new project with a command button and a DAO (or ADO2.1sp2 or higher) reference, then copy this code to the button's Click event (snippet assumes the MS sample file biblio.mdb is in the application path):

 

Dim db as database
On Error Resume Next
  
Set db = Workspaces(0).OpenDatabase(app.path & "\biblio.mdb")
 
'commented out syntax followed by working example
'db.execute "SELECT tbl.fields INTO
'[dbms specifier;DATABASE=path].[unqualified
'filename] FROM [table or tables] 
 
db.Execute "SELECT * INTO [dBase III;DATABASE=C:\My Documents].[testb] FROM [Authors]"
 
If Err.Number <> 0 then'Always check this!!!
Msgbox Err.Number & vbcr & Err.Description
End If

(for ADO2.1sp2 and higher:  Cmd.CommandText = [your sql statement]  or Conn.Execute [your sql here]) 

By using the brackets and dot operator, you get a completely proper output in the ISAM database type of your choice.  Also, if you choose Text, Lotus or HTML as the export database type, the statement creates a Schema.ini for you automatically, or adds a new section for your new data file to a Schema.ini already in the path folder.

Now you can do any kind of export the client wants without using a DBMS on the machine.  This makes your life easier when you notice that some of your users running Word have problems mail-merging with text files you originally created with traditional (and slower) Write# and Print# methods. 

Plus, it's not just for full table exports, to limit the records, create a result file from a multitable query, or to use any required field renaming, dynamic "field calculation" and/or multitable equijoin link statements, just add the JETSQL to the SELECT, FROM and WHERE clauses { "SELECT tblY.*, (tblX.FieldA + tblX.FieldB) AS myNewField ... FROM tblX, tblY WHERE (tblX.ID = tblY.ID) ORDER BY tblY.FieldC ..." }  

Even PIVOT exports are easy once you know how, to see details on Pivots, click here.

If you want to simply export to JET with the technique, that's easy too, For DAO: just remove the destination specifier and the "DATABASE=" qualifier, the default for unspecified databases is the JET format.  For ADO: same as DAO but you have to put a semicolon before the database path like "SELECT * INTO [;c:\mydocs\fred.mdb] ..."  For both methods to work the jet database file must physically exist already, use (DAO) Workspace.CreateDatabase or (ADOX) Catalog.Create if you need to make a new file. (See the SmithVoice DBCodeCreatorClassic or DBCodeCreator.Net if you're new to Jet object programing)

Added Notes

Your default ISAM registrations will cover 90% of all uses but if you need full control over exported file structures Click here.   You can set different delimiters, Tabs, switch between OEM and ANSI, set headers and more without having to do any registry work.  Page/source code shows with the Text; specifier but the information can be adapted to work with all ISAMs that work with Schema.inis..

Different row/col limits apply to different ISAMs and ISAM versions, always check for the return error and adjust your workarounds to suit the specific export file type (see related Excel-specific workaround below)

Excel notes: If you have DAO 3.51 (or higher) then Excel 5/95 (specifier Excel 5.0;) and Excel 97 (specifier Excel 8.0;) workbooks are made for you just like all other types.  Simply use the fully qualified path to the workbook you wish to create as the DATABASE element.  If the workbook file already exists the code will add a new worksheet file to the workbook.  If you do not have the most up to date MSExclXX.dll then a new workbook will not be created, but you still will be able to add a new worksheet to any existing high version workbook file.   Click the source code link at the bottom of this page, there is a full source code Excel<>Jet demo that covers each specifier.

Excel versions lower than 8.0 (97) have a limit of 16,384 rows per worksheet (256 Columns) , if you're exporting over the worksheet maximum rows you'll hit Err 3047 ("SpreadSheet is Full).  Up the version to 8.0 to allow up to 65,536 rows (256 columns) or set a limit in the SQL with "Top 16384" in the SELECT Clause. 

New Excel Info Nov 1999!: Excel can hit your with an error #3349 ... this is pretty neat and could be a bug in the MS Y2K fix.  If you use Date type fields for Excel exports/conversions, click here!

LOTUS123 notes:  The Jet ISAM dll for Lotus supports readonly access for WKS and WK4 formats, but read/write for WK1 and WK3.  To export to 123 version1 spreadsheet use the specifier "Lotus WK1;"  To Export to a 123 version3 "Workbook" use the specifier "Lotus WK3;" (this will create a new file if one does not exist and create/add to an ini for that lotus file). 

Another thing to consider on Lotus 123 version3 exports is that using this routine will create a new worksheet BUT the worksheets themselves will be named AlphaSequentially (A, B, C, etc as shown in the ini files) while the export table name you specify will be used to create a named range for that worksheet.  Since this range includes the entire worksheet it is fine but if you are later going to connect to the file you should remember that you will have to connect via the named ranges in the tabledefs collection.  Click here to see how to override these default outputs (Page shows with the Text; specifier but the information applies to all ISAMs.)

 

source code projects: 4 good examples

demos how to export to anything, how to convert from any ISAM to any ISAM and how to create complete non-Jet databases on the fly .. and a source code demo showing a full Excel-X<>Jet converter.  Current demos are DAO, using ADO(x) [and .Net] is the same JetSQL code.

 

Robert Smith
Kirkland WA



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