We help IT Professionals succeed at work.

Performing Access query with Parameters from inside Excel

622 Views
Last Modified: 2012-05-06
I need some help performing an access query in excel.
The access data is all in one table (and its about 3 million records) and I have the query with the right parameters set up and it works fine but I want to be able to run the query and get the data from excel because I have to perform several calculations on the data (so I don't want to build a form). I need the query to be dynamic - i.e. it chooses parameters from user entries in the worksheet.

I have tried using the get data "from other sources" through MS query, and it sort of works including looking up the parameters from the cells i tell it to, but it is painfully, painfully slow and sometimes the connections break.

Is there any other way, perhaps with some vba code or is the MS Query the only way?
Comment
Watch Question

Commented:
This is how to do Access query in Excel by VBA:
http://support.microsoft.com/kb/q147739/
CERTIFIED EXPERT

Commented:
hi mstringfield, one option would be to use a recordset, however with over 3m records the results won't be blisteringly fast, however the excel range's copyfromrecordset method makes it fairly straightforward.  Here's some air code to give you an idea as to how it could be done.



Sub GetData()
Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String
Dim wksD as Excel.WorkSheet   'Destination worksheet
Dim wksS as Excel.Worksheet   'Worksheet with parameters
Dim rng as Excel.Range
 
Set wksD = ActiveWorkbook.Worksheets("Data")
Set wksS = ActiveWorkbook.Worksheets("Source")
 
'Grab the database you want to get the data from
Set db = DAO.DBEngine.OpenDatabase("<full path of the db you want use>")
 
'Create the SQL to capture the data, you could modify this bit to dynamically alter the WHERE clause depending which parameters were specified.
strSQL = "SELECT <field1,field2,fieldn etc> FROM <the table you want to use> "
strSQL = strSQL & " WHERE field1=" & wksS.Cells(1,1)    'Pretending there is a parameter in A1 in the 'Source' sheet
 
'Open the recordset to capture the data
Set rst = db.Openrecordset(strSQL,dbOpenSnapshot)
 
'Plonk the data into Excel
Set rng = wksD.Cells(1,1)
rng.CopyFromRecordset rst
 
End Sub

Open in new window

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.