Tool's overview

Name MultiLevelExporter, also known as MLE
Requires installation?

No - it can also be used without installation as a portable tool!

Purpose

MLE tool exports and transforms MS Access Tables with Subdatasheets, Queries with Subdatasheets or Forms with Subforms into Microsoft Excel while the resulting spreadsheets keep the Microsoft Access-like drill-down design of original objects.

See more in Purpose & Features.

Home URL www.probiz.biz/mle 

Requirements,

Compatibility,

Operation Systems

In order to use the tool both Microsoft Access and Microsoft Excel need to be installed on the computer.

MLE tool is compatible with all Microsoft Office versions from 2007 to 2019, and 365, both 32bit and 64bit.

It  works in all versions of Microsoft Windows.

See more in Tool's requirements & limitations.

Supported languages English
License type & Price

MLE is available as Demo that can be upgraded to Full version through purchase of the Product key.

The price is $9 for the 1st Product key or $5 for every further Product key.

See more in Purchase of Product key and upgrade to Full version

  

  

Video

This short YouTube video demonstrates how it actually looks when MLE is exporting data from Microsoft Access into Micorosft Excel.

www.youtube.com

 

 

Purpose & Features

With MLE tool you can export following Microsoft Access structures and objects into Microsoft Excel:

  • Tables with Subdatasheets
  • Queries with Subdatasheets
  • Forms in datasheet view with Subdatasheets
  • Forms with Subforms

It actually converts the above mentioned database objects with all its data into Microsoft Excel.
The result is that the data of both the parental level (Datasheet or Form) and also all its children's levels (Subdatasheets or Subforms) get exported into Microsoft Excel spreadsheet while retaining the original drill-down tree-like layout.

See example of such an export in Example exports.

 

MLE tool is usually being used in order to:

 

Share Microsoft Access data with non-Microsoft Access users

You may need to distribute Datasheets with Subdatasheets or Forms with Subforms to individuals who:

  • Aren't familiar with Microsoft Access
    In this case it is usually easier and more comfortable for end users if they are provided with an Microsoft Excel spreadsheet containing exactly the data they require, so they needn't learn working with Microsoft Access forms, queries and the whole user interface of Microsoft Access.
     
  • Don't have Microsoft Access installed
    In this case the export to Microsoft Excel may be the only option.
     

 

Protect sensitive Microsoft Access data

If your database contains sensitive data that shouldn't be shared, with MLE you may export and share only selected Tables, Queries or Forms and their underlying children, and not the remaining sensitive data. 
 

 

Process Microsoft Access-originating data in Microsoft Excel while keeping parent-child layout

After you export parent-child structured data from Microsoft Access into Microsoft Excel, you may want to further analyze and process the data in Microsoft Excel Worksheet in the ways that aren't available to Microsoft Access (you may for example use Microsoft Excel's graphs, formatting, forms, formulas and other Microsoft Excel specific features).
 

 

Print out parent-child tree-like layout

It is not possible to print Datasheet with Subdatasheet or Form with Subform and the underlying data of both the parent and children levels all at once directly from Microsoft Access.
Nevertheless after you export parent-child tree-like organized data into Microsoft Excel you may print it now from there easily. 

 

Export 1:N data of any non-Microsoft Access database into Microsoft Excel

With a little effort and with use of MLE tool it is possible to export 1:N tree-like organized data of any database, including non-Microsoft Access databases like MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Oracle, SAP, dBASE, FoxPro, IBM DB2, LibreOffice Base, Informix, Teradata, etc. into Microsoft Excel.

Let's say that your data is stored within several tables of non-Microsoft Access database and there are relations established between those tables (usually 1:N relation type).
How can you export this data into Microsoft Excel while keeping 1:N layout?
Neither of these relation databases can do such an export itself. Many of them can export data to Microsoft Excel, but non can retain 1:N layout through the export.
 

Example:

Let's say that tables in the external database are named tblParent and tblChild and there is a 1:N relation between them.
The export process with use of  MLE would be as follows:

  1. It is necessary to link all required tables of the external database from within Microsoft Access → so you will create linked tables in Microsoft Access.
    You need to link at least 2 tables: the parent (on the '1' side) and the child (on the 'N' side of 1:N relation).
    Let's say that in our case 2 linked tables will be created and their names in Microsoft Access will be tblParentLinked and tblChildLinked.

  2. Microsoft Access linked tables can't contain subdatasheet (it is because the design of linked tables can't be changed).
    This can be easily worked around by creating Select Query that will return all the records of the linked table.
    And within the Select Query it is already possible to configure subdatasheet.
    Let's say that for our case we create qryParent (which will return all records of tblParentLinked) and qryChild (which will return all records of tblChildLinked).

     
  3. In the design view of parent query it is now possible to configure the subdatasheet, i.e. setup following 3 properties:
    - Subdatashet Name
    - Link Child Fields
    - Link Master Fields
    They need be setup manually - the Wizard that works for tables won't work for queries.
    It is not difficult to setup these properties and I am not going into details here as it would go behind the purpose of this section - should you need help with this, just drop me an Email and I will gladly assist you.

  4. When you have your 2 queries finished and the parent query displays the child query as the subdatasheet it is now possible to export the whole meshup into the Microsoft Excel with the use of MLE.


To summarize, the whole process consists of 4 steps as follows: 
1. Link tables
2. Wrap linked tables into select queries
3. Setup subdatasheet in the parent query
4. Export the parent query with MLE


I tested this Link → Wrap → Setup → Export process and it worked well for me. I tested it thoroughly and realized that with this approach it is actually possible to export the final structure consisting of 1 parent + up to 7 nested levels / linked tables wrapped within queries (meaning that 1 parent has a 1:N child representing the 1st nested level, this child has 1:N child of its own representing the 2nd nested level, etc ... down to the level 7).

Other approach would be to not to link but import tables from external database into Microsoft Access, re-establish relationships between them in Microsoft Access and then export them with use of MLE. Since this is about importing which is one-off process, I regard this approach less flexible but it may be useful in some specific situations. 
 

 

Export drill-down structured data into other file formats

Once you exported parent-child structured data from Microsoft Access into the Microsoft Excel, it is now possible to re-export it further from Microsoft Excel (using 'Save As' option) into number of other file formats.

See few examples on links below:

The fact that you may export from any relation database (see previous point) to any file format makes MLE exporting tool extremely flexible.

 

  

Send database data via Email, Reduce file size

Some Email servers automatically delete all attached Microsoft Access databases from Emails, while Microsoft Excel Worksheets aren't blacklisted and get through.
Furthermore the most of Email servers won't put through any attachments exceeding 10 or 15 MB. 
With MLE it is possible to extract only required Microsoft Access Tables, Queries or Forms and their children into Microsoft Excel and thus control the size of the resulting file.
 

 

Combine 2 originally separated Excel lists into 1 tree-like output

One user provided with following case story:
I had 2 lists in Excel and I needed to show a 1-to-many relationship between them, and I needed to show it in Excel. I tried pivot tables first but of course pivot tables cannot relate 2 lists. I then imported the lists into Access, and related them. When I tried exporting the query result, it did not show the relationship. I was at a loss, because I had no other way of showing the drill-down in Excel. This is where your app came in; it worked beautifully and the results were exactly what I needed.
The reason both list were not in Access was because one of them is a packing list I received from a vendor in Excel format. The 2nd list was originally in Access, because it came from a scanning program that I wrote in Access; I just exported it to Excel. Anyhow, without your tool, Access would have been useless to me because I wanted to show the drill-down report in Excel.
 

 

Tool's advantages

Uniqueness

There are many other tools facilitating export of the data from Microsoft Access to Microsoft Excel, however only MLE can export data of multiple levels while keeping the original multi-level data layout.

The functionality provided by the tool is unique not only for Microsoft Access but across all existing relationship database systems - you won't find any other database system capable of exporting relationship-like (1:N) organized data from the database to Microsoft Excel.
  
  

Broad compatibility

The tool is compatible with Microsoft Office versions from 2007 to 2019 and 365.

It doesn't work with Microsoft Access pre-2007 versions.

It works with both 32bit and 64bit versions of Microsoft Office.

It can extract & export data from all types of Microsoft Access files, including:

  • MDB (standard database Microsoft Access 2002 - 2003 file format)
  • MDE (compiled database Microsoft Access 2002 - 2003 file format)
  • ACCDB (standard database Microsoft Access 2007 - 2019 file format)
  • ACCDE (compiled database Microsoft Access 2007 - 2019 file format)
  • etc
      
      

Support of multiple nested levels

If there are two or more children (Subdatasheets or Subforms) nested one into another (parent has a child and the child has another child of its own, etc.), it is possible to export them all at once.

Microsoft Access supports 1+7 nested levels of Subdatasheets or Subforms, where 1 stands for the parental level (the main Form or Datasheet) and 7 stands for the maximum number of nested levels.

With MLE it is also possible:

  • To setup the limit for the deepest level to be exported (in case you need to export only limited number of levels out of the total number of existing levels).
     
  • To skip specified levels.
    Let's say you have got following structure of 1 parental Datasheet and 2 nested Subdatasheets:
    The parental Datasheet contains records with freight transports.
    There is the 1st level Subdatasheet with the drill to shipments on these transports where 1 transport may contain N shipments.
    And the shipments Subdatasheet contains another (2nd level) nested Subdatasheet of pieces, where 1 shipment may consist of N pieces.
    With  MLE it is possible to configure that the Subdatasheet of shipments (the 1st Subdatasheet) will be filtered out, while the Subdatasheet of pieces (the 2nd Subdatasheet) should be exported.
    The result in Microsoft Excel will show the parental Datasheet (now converted into spreadsheet) with all its transports, and under it there will be pieces aligned to and grouped by these transports.
    The information about shipments (the middle level) will be filtered out.
      
       

Range of customizations

There are many configuration options available in the MLE user interface and with them it is possible to customize:

  • Where will the data be exported to.
    The 3 options are: 
    - New  Microsoft Excel Workbook
    - New Sheet in current Workbook
    - Current Sheet in current Workbook
     
  • Which levels of data will be exported (1 parental level + 7 children levels can all be configured individually).
     
  • What will the Microsoft Excel export look like (Headers yes / no, Colors, Row heights, Alignment, Font size, etc. - all is configurable separately for headers and rows). 
    All these options can be configured individually for each of 1+7 possible levels. 
     
  • Other options.
      

Actually, the Microsoft Excel output will always look prettier and more colorful, will be more flexible, safer, user-friendly and presentable than the original Microsoft Access data.

See more about configuration options in User Guide section.

See the example output in Example exports section.
  
 

MLE is 3 in 1

There are 3 different approaches for using the tool: 

  • Use MLE as a standalone (not installed) MS Access executable file
    This approach makes MLE de-facto portable tool. 
    You only have double click MultiLevelExporter.accda file to launch it. 
    It is ideal approach for trying the tool and also for situations where installation would require administrator's permissions that are not available at the moment. 

  • Use MLE as the standard MS Access add-in
    This requires installation of MLE the same way all standard Access Add-ins are being installed. 
    Although the installation may be time consuming, it provides with the best user comfort and stability. 
    Also, once installed it is available for all Access databases. 


  • MLE can be used as a Library Database / Library File
    This is intended for developers and advanced users. 
    It is possible to "hook" onto MLE procedures from within your VBA code of your own Access database and thus use MLE functions without displaying MLE user interface. 
    Pls. drop me an Email in case  of interest.
      
      

Tool's requirements and limitations

Both Microsoft Access and Excel need be installed on the computer

In order to run the tool they both need to be installed on the target machine but they needn't be of the same version, as far as each of them is version 2007 or higher. 
So for example the tool will work with Microsoft Excel 2007 and Microsoft Access 2019 installed together on the same computer, or vice versa.
  
 

Exporting tens of thousands of rows may take long

The number of rows (records) that can be exported from Microsoft Access to Microsoft Excel is limited only by the number of rows  available in your current Microsoft Excel Worksheet.

Please keep in mind that Microsoft Excel versions 2007 and higher support Worksheets with 1 048 576 rows.

Since MLE exports rows into Microsoft Excel one by one, exporting tens of thousands of them may, especially on slower machines, take several minutes to complete.
The total export time is proportional to the number of rows to be exported, but is also affected by other factors, especially computer's performance, number of fields to be exported and their data types.
 

 

The clipboard functions are not available during the export process

None of the clipboard functions (Ctrl+C, Ctrl+X, Ctrl+V and also 'Print Screen' key) within all Windows applications (Microsoft Excel, Microsoft Word, Notepad, etc.) is available while the export is running.
It is because the tool reserves them for its functioning. 
   
  

User Guide 

This guide describes how to export required Microsoft Access database object(s) into Microsoft Excel.
 

1. Open the object(s) to be exported

Open the Microsoft Access database whose objects should be exported.

Open any of the following Microsoft Access structures / objects due to be exported into Microsoft Excel:

  • Table with Subdatasheet
  • Query with Subdatasheet
  • Form with Subform
  • Form with Subdatasheet

You may open multiple database objects at once.
 

 

2. Launch MLE

The way of launching MLE tool differs based on whether it is being launched as a stand-alone application or an installed add-in:


Launching MLE as a portable tool (in case it hasn't been installed as add-in)

Just double click the 'MultiLevelExporter.accda' file wherever it has been saved on your computer.
During its launch, the tool will automatically detect already open Access database, hook onto it and provide with options to export its currently open database objects.
This approach expects that target Access database is already running and the required objects intended for export are open prior to launching MLE tool.


Launching MLE as add-in (in case it has been installed as Microsoft Access add-in)

In Microsoft Access 2007 - 2019 launch it as follows:

Access Toolbar → Database Tools → Add-Ins → MultiLevelExporter

 

When the tool is launched, its main screen will pop up automatically and display the list of all currently open Access objects, see example:

MLE_Main_screen.jpg

  

    

3. Filter and sort data

MLE will export into Microsoft Excel only those rows / records that are currently shown / filtered in Microsoft Access.
Furthermore, records will be exported in the sort order in which they are shown in Microsoft Access.
This provides with the opportunity to apply required filtering and sorting before running the export. 
 

 

4. Configure export process and other options

Before exporting of the required object into Microsoft Excel, you may configure following options of the export process:

  • Data will be exported to:
    This control indicates where the data will be exported to. The 3 options are:
    • New Microsoft Excel Workbook
      The new Microsoft Excel Workbook with one Worksheet will be automatically created for exported data.

    • New Microsoft Excel Sheet in currently open Workbook
      The new Worksheet will be automatically added into currently open Microsoft Excel Workbook and data will be exported to that Worksheet.

    • Current Microsoft Excel Sheet in currently open Workbook
      Data will be exported to current Worksheet of currently open Microsoft Excel Workbook, just below its last used row.

     
  • Following records of the parent (and all its possible children records) will be exported:
    This control provides with 3 options on what records will be exported:

    - All parent's records 
    - Only selected parent's record(s) 
    - Only the current parent's record 

 

  • When export to Microsoft Excel finishes, following application will obtain focus:
    This control configures which application (Microsoft Access or Microsoft Excel) will be given the focus when the export completes.
    It has no effect on how the export will look like or which records will be exported.
      
      

5. Configure Export schemas

You may click the 'Configure Export schemas' button which will open following screen:

MLE_Configuration_of_Export_schemas.jpg

  
As explained in the screen itself, here it is possible to configure the design for each of possible level in Microsoft Excel.
Every level's design can be configured separately.

It is also possible to save newly created schema or load other (saved) schema.
 

 

6. Perform the export itself

After all the settings have been performed click the 'Export to Microsoft Excel' button.
This will actually trigger the process of exporting of selected Microsoft Access database object into Microsoft Excel.
   

 

Example exports

Below there are few example exports, where on the left side there is always an Access object and on the right side of the same picture there is the same data after conversion into Microsoft Excel.

Examples are based on Northwind sample Access database.

The similarity of what is shown on the left and right sides is not an error or mistake: they are really Microsoft Access objects / data on the left and Microsoft Excel export on the right.
Microsoft Access source on one hand and the Microsoft Excel export result on the other not only contain the same data but they also look similar!

  • The plus (+) and minus (-) symbols on the left side of Microsoft Access represent options to unhide / hide nested (Subdatasheet's or Subform's) rows.

  • While the plus (+) and (-) symbols on the left side of Microsoft Excel Worksheet represent so called 'groups'.
    (In Microsoft Excel 2007-2019 the group can be created manually by going to 'Data' ribbon and then clicking the 'Group' button.)

The result of the exporting is that all the data from nested structure(s) of Microsoft Access (Table or Query with Subdatasheets, Form with Subform) gets exported into grouped layout of Microsoft Excel.

No information is lost and it even looks similar as in Microsoft Access.

Microsoft Excel export becomes the virtual replica of the source Microsoft Access nested structure.

 

In Microsoft Excel it is now possible to:

  • collapse rows of the group with minus (-) symbols in order to see only parent records
  • unfold collapsed rows of the group with plus (+) symbols



Example #1

On the left there is the 'Customers' table of Northwind database made of 3 nested levels (a table with 2 nested subdatasheets).
On the right there are the the same data converted into Microsoft Excel.  

ExampleExport01.jpg


  

Example #2

On the left there is the 'Products' table of Northwind database made of 4 nested levels (a table with 3 nested subdatasheets).
On the right there are the the same data after conversion into Microsoft Excel.

ExampleExport02.jpg
  
 
You may download the Northwind.zip zipped sample database used for examples above.

You may download the Example_Microsoft_Excel_outputs.xls file containing many example outputs. 
  
  

Purchase of Product key and upgrade to Full version
  

MLE tool comes as Demo that can be upgraded to Full version

The only limitation of Demo compared to the Full version is that circa every 5th cell exported into Microsoft Excel is populated with the word "Demo" instead of original / expected Microsoft Access value.

In order to unlock to the Full version and use the tool without limitations the Product key needs to be purchased and provided into MLE.
 

 

Try the tool before the purchase

It is strongly recommended that you try the MLE thoroughly in the Demo mode before purchasing the Product key!
 

 

Product key price

The price is:

  • $9 for the 1st Product key
  • $5 for any further Product key 

Discounts and different price scenarios are possible for bulk implementations (like for multiple computers in the company, life-time licences, etc.)

  

  

Acquiring Product key 

  • MLE will provide with the Hardware ID

    Just proceed as follows: 
    → On the bottom of the main screen there is a 'Product activation' button.
    → Click that button and a new small window will pop up.
    → Within that window you will find the Hardware ID.

    What is HardwareID?
    Hardware ID is being generated by MLE and is unique for each computer. 

    It is necessary for obtaining of the Product key.
    1 Product key is always related to 1 Hardware ID (and vice versa), meaning that these two go together like lock and key - if they match, MLE tool runs in Full version, otherwise (if Product Key is missing) it runs only as the Demo.

    This also means that:
    - 1 Product key is always related to 1 computer – to that one for which the corresponding Hardware ID was produced.
    - For multiple computers multiple Product keys are required. 

  

  • Purchase Product key through PayPal

    The PayPal payment gateway is used for check-out.
    It support 2 types of payments:
    - payment through PayPal account
    - payment via credit / debit cards (where PayPal account is not even required)

    Make sure to provide with following information into PayPal check-out screen:

    Hardware ID (required)
    MLE tool itself provides with Hardware ID after it is launched as Demo (see previous step).

    Additional Email (optional)
    Additional Email address is just optional because the Product key will always be sent to the Email address linked to payer's PayPal account.

    By finishing check-out the Hardware ID and other provided information is sent to MLE author along with the payment.
       
Is it the 1st or additional Product Key?
Hardware ID (mandatory)
Additional Email (optional)

       

  

  • Obtain Product key
    Upon receiving the Hardware ID the MLE author generates the Product key matching to this particular Hardware ID and sends it back to payer's Email address. 
    You will usually obtain the Product key within minutes or few hours since the payment was placed, never more than 24 hours!

    

  • Provide Product key into MLE tool
    The last step is to Copy and Paste the received Product key into MLE tool.

    Just proceed as follows:
    → On the bottom of the main screen there is a 'Product activation' button.
    → Click that button and a new small window will pop up.
    → Paste the Product key into the corresponding box and confirm with the button. 
        It is actually this last step that unlocks the tool for Full use!
      
      

Download

Version 8.7: MultiLevelExporter.zip 

The zip file contains installation and uninstallation instructions.