Everyone tells you how to import, but even MS-VB techs start coughing when you ask how to export with DAO! 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").
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;" 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 !
Use this code to get data out to different ISAM formats (see * for exceptions) 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 DAO3.5 reference, then copy this code to the button's Click event (snippet assumes the MS sample file biblio.mdb is in the application path): |
 Click here to copy the following block | Dim db as database
Set db = Workspaces(0).OpenDatabase(app.path & "\biblio.mdb")
db.Execute "SELECT * INTO [dBase III;DATABASE=C:\My Documents].[testb]" & _
" FROM [Authors]" |
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 Word97 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 ..." }
If you want to simply export to JET with the technique, that's easy too, just remove the destination specifier and the "DATABASE=" qualifier, the default for unspecified databases is the JET format.
Exceptions: Funny thing is that the above works for most every ISAM format EXCEPT 2 from Microsoft! Excel5/95 and Excel 97 workbooks can not be created with the above method. And this is not simply due to VB's Connect property (DAO) help page incorrectly listing the Excel 97 ISAM type as "Excel 97;" when it is actually "Excel 8.0;"). You'd think that MS would do better to have their own objects work with their SQL methods ... but since they don't document that the above code works, you can't really blame them.
In a nutshell the restriction is due to the fact that with Excel5 MS started encapsulating "worksheets" within "workbooks" much the way that Access encapsulates "tables" within "database files" rather than having each table being an individual file as with xBase and others. So, trying to create an Excel5 or higher *.xls file results in an error stating that the "database could not be found." This is the same error that is returned if you use the above code to create a Jet table in a Jet database file that does not exist. The workaround for Jet is to use the CreateDataBase method first and then run the code to put the exported table inside that new database. This does not work with Excel5 or higher as CreateDatabase will not create an Excel workbook file.
If an Excel 5, 7 or 8 format must be exported, you have to automate the creation of an empty workbook by automating Excel5 (or higher) and using the Workbooks.Add method. Once this method is used, you can use the above code to create new worksheets of your export results inside the new Workbook, but remember that that methods requires that Excel 5 (or higher) is on the end user's machine.
Personally, we just stick with Excel 4 as it is the common denominator that all higher Excel releases will read and write to, but if you want to you can test whether Excel 5 or higher is on the user's machine (excellent code for this can be found at planetsourcecode.com) and take it from there.
LOTUS123 notes:Similar to Excel, the Jet ISAM dll for Lotus supports readonly access for WKS, WK3 and WK4 formats, but read/write for WK1. To export to 123 use the specifier "Lotus WK1;" Users of later versions will be able to use these files (and you can use all the other versions as sources for later "Export To Anything" routines, see the "Relational Text Files" tip to see this in action)
|
|