• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Excel to download data from Access databse

M

Mika

New Member
Hello,


I wanted to know the best way download data thats is in an access .mdb file by creating an Excel form or add in.


At work our Reporting & Solutions team downloads monthly data from several countries in to a huge .mdb file. Someone (before I started with this company) created an Excel form or add in where I have options to choose the country I manage and the different cost centers and press a start button. What this does is retrieve the data from the .mdb file and opens Excel workbooks with the different cost centers I chose. These workbooks show the P&L account number, monthly value, budget amount and variance amount. With this information we can analyse the monthly variances of each cost center and complete our month end reports.


I find this retrieve very handy and would like to know what is the best way to learn this cool trick. I believe that this is VBA related. If anyone can lead me to the right path I would be very happy and on the way to having that extra advantage at work!!


Thanks


¡Mika!
 
I've done this a few times for my work and I think the best way is detailed in this article: http://support.microsoft.com/kb/257819. Basically, you'll reference an ADO object and use it to connect with the Access file. From there, you'll supply the ADO connection with some SQL which will return your requested tables.


The code in that link appears to be a little old, so I would double-check my connection string from the info on this site: http://www.connectionstrings.com/access.
 
Thanks for your post Jordan but it seems very complex to take this path. I will give it a try though!!


If anyone else has another method that would be greatly appreciated! Thank you all!
 
A nice manual method: Open datasheet view in access, select records (or ctrl-A for all), copy, go to excel, paste.


If you have the spreadsheet that you want to emulate, I would just try to adapt the existing code.


The ways I know of in VBA executing in Excel are:

1. Use ADO to open a recordset, and copy the data to a sheet. You will need to specify an SQL query to filter the data, or specify parameters to a parameter query in the MDB,

2. Create a QueryTable. Similar to #1 except the table of data is persistently linked to the MDB and can either automatically refresh or be set to refresh manually when desired.

3. If Access is also installed, you can use automation to create an Access object and control it. It has some export functions that would export to a static unlinked Excel file.


#1 makes the most sense unless you need the data linked. #2 usually requires the least code, and #3 is kind of inefficient if your code is already running in Excel.


Here's a procedure to retrieve data using ADO/SQL: http://itknowledgeexchange.techtarget.com/beyond-excel/say-goodbye-to-querytables/


This blog appears to contain sample code and instructions on creating userforms to select data and retrieve it: http://itknowledgeexchange.techtarget.com/beyond-excel (page 6 has a lot of code that seems like it relates to your request)
 
Thanks asa for your in depth reply!! I will try the methods that you have suggested and get back to you with my results. Great work!


¡Mika!
 
You must log in or register to reply here.
Back
Top