Export DAO databases to any ISAM format

Export DAO databases to any ISAM format

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):

Dim db as database               Set db = Workspaces(0).OpenDatabase(app.path & "iblio.mdb")               'commented out syntax followed by working example                ' db.execute "SELECT tbl.fields INTO ' [dbms type;DATABASE=path].[unqualified filename] ' FROM [table “” not found /]

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)

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as