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.
- View Related Tables and Pull Raw Data from Related Fields
- Summarize, Aggregate and Group Data like a pivot table in many interesting ways
- Perform Cross Tab Queries
- Filter Data (“WHERE” CLAUSE of SQL)
- View, modify and run SQL code
- View and run stored queries in the Database
- 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 [email protected]
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
[email-download download_id=”527″ contact_form_id= “566”]
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
A dialog form would appear as shown below
View Related Tables and Pull Raw Data from Related Tables
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.
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.
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.
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
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
Perform Cross Tab Queries
A 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”
Filter Data (“WHERE” CLAUSE of SQL)
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.
View, modify and run SQL code
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
- Retrieve raw data
- Aggregate and Group
- Cross Tab Query
View and run stored queries in the Database
This feature allows you to run queries already created in the database.
This is depicted below
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
HISTORY OF MICROSOFT ACCESS DATABASES OPENED
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
[huge_it_share]