Y'herd thisun? 

“We well know, from repeated experience, how much misery and hardship men will undergo in their own country, before they can determine to desert it; and how often the most tempting proposals of embarking for new settlements have been rejected by people who appeared to be almost starving.”

from Principle of Population, Chapter 10 by Thomas Malthus

Pivot out to dBase, Excel, HTML, whatever

TaggedCoding, Database

This page builds off of the Export To Anything basics. You should look at that little page prior to tackling this one.

We got a neat question recently concerning how to export the results of Pivot queries.  The simple answer is:  Remember that saved Jet Querys (querydefs) can be used as source "tables" just like intrinsic tables or linked tables; So if you need to do Pivots all you have to do is use a saved Pivot querydef as the source and run a very basic Export To Anything SQL where the source is the PIVOT querydef's name.

Here's the whole step by step of it:


I want to export the results of this query in DBF file in a Visual Basic module without use the Interactive method of MS-Access' File|Save/export. I have to create a DBF database everyday with the fields resulting from a Pivot query.

Our Pivot query is similar to:

TRANSFORM Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS
ProductAmount SELECT Products.ProductName, Orders.CustomerID,
Year([OrderDate]) AS OrderYear FROM Products INNER JOIN (Orders
INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID) ON Products.ProductID = [Order
Details].ProductID WHERE (((Orders.OrderDate) Between #1/1/95#
And #12/31/95#)) GROUP BY Products.ProductName,
Orders.CustomerID, Year([OrderDate]) PIVOT "Qtr " &
DatePart("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");

(note: when pasting this SQL from this page into the Access QueryBuilder SQLView you may have to do some work to clear the carriage returns)


You've already done the hardest part ... building the Transform SQL

Step thru this:

1) Create a new folder to be the destination of the resulting dbf file. For this demo we will use a new folder called C:\TestPiv

1) Open up your copy of Nwind.mdb

2) press the button to make a new query, use Design View and as soom as the query builder shows up, close the little "Show Table" window .

3) at this point you're looking at a blank Query Builder

4) Press the "SQL" toolbar button or choose "SQL from the "View" menu

5) Paste your transform SQL into the SQL window (overwriting the generic "SELECT ...;" words)

6) Run the query to see that it is showing what you wanted (if it prompts you for parameters it's because Access adds hidden carriage return characters to the SQL View window, you have to remove these to make a single long SQL statement that wraps without strange breaks) Forgive me if you know all this I just have to say it sometimes ;)

7) Save this query, for this demo we'll save it as "qryMyPivoter"

8) close the query builder

9) press the button to make a new query, use Design View and as soom as the query builder shows up, close the little "Show Table" window .

10) at this point you're looking at a blank Query Builder

11) Press the "SQL" toolbar button or choose "SQL from the "View" menu

12) Paste this into the SQL window (overwriting the generic "SELECT ...;" words)

SELECT qryMyPivoter.* INTO [dBase III;Database=C:\TestPiv].[mytst] FROM qryMyPivoter;

13) Run that query.

That is the step by step using Access to make it most obvious.

You know where this is going, right?

In VB, All you have to do, since you're writing the Transform SQL anyway, is either create a stored QueryDef using your TransformSQL (OR when you need to do the export just create a temporary querydef using your Transform SQL)

Then call the second SQL using the name of your Transform QueryDef as the source instead of using table name(s).

And there you go. Once you have data either in tables or accessible via a named querydef you just run the Export To Anything code.

Give it a few tries in Access an the VB implementation should just naturally unfold. If you aren't used to creating QueryDefs (stored or temporary) search help for CreateQueryDef and/or use the copier at:  which is just a utility for reverse engineering Jet files, you can use this to "write" the vb code you need to create your querydefs on the fly)

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