Access To Excel

Pull and analyse data from Closed Access Databases

Get insights into your data stored inside Microsoft Access with this Excel Addin.

If you use both Microsoft Excel and Access, you would love this tool. I used ADO, SQL and VBA to build this tool.

 

7 Powerful Features of this Addin

Click any feature below to see how to use it. I have given animated images in most cases to show how it works. The Addin itself is very intuitive and easy to use. Just download and play with the features.

  1. View Related Tables and Pull Raw Data from Related Fields
  2. Summarize, Aggregate and Group Data like a pivot table in many interesting ways
  3. Perform Cross Tab Queries
  4. Filter Data (“WHERE” CLAUSE of SQL)
  5. View, modify and run SQL code
  6. View and run stored queries in the Database
  7. Group Dates by Months, Quarters and Years

This Addin is particularly very useful when you need to share your Access Database with Multiple Users on a read only basis. Especially  when Multiple Users need to simultaneously access  in real time.

Using this Addin, users can retrieve, summarize, query the database and even view , modify and run their SQL codes without the need to open the ACCESS database.

If you have any difficulty retrieving information or using this Addin, please write to me at zubair@excelnaccess.com

Download

You can download the copy of this Addin from the link below. Please provide email address so that if there is any improvement in this Addin, I can share it with you 🙂

There is no need to download again if you have already downloaded “Z-Addin” from another webpage in this website

    Your Name (required)

    Your Email (required)

    STEP BY STEP INSTRUCTIONS TO USE 'PULL FROM ACCESS' ADDIN

    How to Install this Addin

    Save the ZAddin.xlam file in your PC.

    Open any excel workbook. Press ALT + T + I to open the Addin Manager. Browse for the file and enable the Addin

    Once installed you will see a new tab 'Z-Tools' in your excel workbook. Activate the 'Z-Tools' Tab and press 'Pull Data from Access” Button

    ztools-tab

    A dialog form would appear as shown below

    dialogform

    View Related Tables and Pull Raw Data from Related Tables

    Go to Page Top           

    Press  'Select Access Database' button to locate and select your access database.

    The database won't open, however you would a new screen with one table from the Database and the fields inside that table. Choose any table by clicking the dropdown. You will see the fields in that table.

    myfirstscreen

    Right clicking a table allows you to add tables related to that table. This is shown below in the animated image. You can move the tables around to see the relationship clearly. You can move the tables around by holding the left mouse button.

    get-related-tables

     

    To select fields press “Toggle Tables’ movement”. Now you can select the fields you want  by dragging the mouse over. Click anywhere in the excel workbook where you want data to be placed and PRESS 'Retrieve Records' button in the DIALOG FORM.

    retrieverecords

     

    The Dialog Form works in MODELESS fashion i.e. you can work in EXCEL while the Dialog Box is open. The Dialog Form is Resizable. You can resize it from any corner.

    Summarize, Aggregate and Group items

    Go to Page Top 

    This powerful feature lets you summarize your data in ACCESS tables which might span thousands or even millions of rows.

    Since ACCESS tables are often collection of individual transactions, users/ analysts would be interested in summarizing data. For example in a daily sales table having ten thousand rows of individual sale transactions, you might like to see the total sales for a particular period for each customer and for each sales person.

    Select the fields from related tables that you you wish to Aggregate and Group and then press “Aggregate and Group” at the bottom of the form. You would see a new tab 'Aggregate & Group'.

    The animated image illustrates how to use this

    aggregatengroup

    Perform Cross Tab Queries

    Cross Tab Query allows you to view an aggregated field across two sets of values— one down the side of the datasheet and the other across the top. This results in data in a compact Matrix Format which can be very valuable for analysis. The demerit is that you can only view one aggregated field.

    To perform a cross tab query, Select the fields from related tables and then press “Cross Tab Query” at the bottom of the form. You would see a new tab 'Cross Tab Query'.

    The figure below shows how to perform “Cross Tab Query”

    crosstab

    Filter Data (“WHERE” CLAUSE of SQL)

    Go to Page Top 

    This feature allows you to filter your data and get the particular records you need. Here you develop the WHERE clause of SQL.

    To make queries check the 'Make Queries/Filter Data' checkbox at the bottom of the FORM. You will see the “Run Queries” tab.

    filter

    View, modify and run SQL code

    Go to Page Top 

    In each of the following three cases you will see an option of View SQL code. The beauty of this feature is that you not only view the SQL for the query you designed but also you can directly modify and run the SQL code

    1. Retrieve raw data
    2. Aggregate and Group
    3. Cross Tab Query

    sqlview

    View and run stored queries in the Database

    This feature allows you to run queries already created in the database.

    This is depicted below

     storedqueries

    Group Dates by Months, Quarters and Years

    When you use a field whose datatype is  “Date/Time” for Aggregation&Grouping or cross tab query, you will see an option to Group Dates by Months, Quarters or Years.

    The animated image below depicts this

    groupdates

    HISTORY OF MICROSOFT ACCESS DATABASES OPENED

    Go to Page Top 

    The Addin maintains history of 10 most recent Access Databases which you have accessed using this Addin.

    This helps you quickly access your most used Databases without having to browse and locate them again.

    Go to the Quick Links in “Menu”. Just Double click on the Database you want to retrieve data from

     

    Go to Page Top 

    Share This:

    Share This: