?
Solved

Access query to Excel spreadsheet

Posted on 2011-02-23
9
Medium Priority
?
259 Views
Last Modified: 2012-06-27
I have a vb function that runs a query and copys the data to a spreadsheet.

The problem is, running the query in Access takes literally 1 second to complete but when running from the excel macro it takes minutes!

Please see below the macro, how can I speed this up to relfect the Access speeds?
Sub RunAccessQueries()
    Dim cnn As ADODB.Connection
    Dim strQuery As String
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Dim prm As ADODB.Parameter, prms As ADODB.Parameters
    Dim wks As Worksheet
    Dim i As Long
    Dim varSheetName As String

    varSheetName = "QUERY1"

    Set cnn = New ADODB.Connection
    With cnn
       .Provider = "Microsoft.Jet.OLEDB.4.0"
       .ConnectionString = "Data Source=C:\db.mdb;"
       .Open
    End With
    
    Set wks = Sheets.Add
    wks.Name = varSheetName
    strQuery = "[" & varSheetName & "]"
    Set cmd = New ADODB.Command
    With cmd
        Set .ActiveConnection = cnn
        .CommandText = strQuery
        .CommandType = adCmdTable
        .Parameters.Refresh
        .Parameters("[Report date]").Value = dteReportDate1
        .Parameters("[Report date 2]").Value = dteReportDate2
    End With
    Set rst = New ADODB.Recordset
    rst.Open cmd
    With rst
        If Not (.EOF And .BOF) Then
           For i = 1 To .Fields.Count
              wks.Cells(1, i) = .Fields(i - 1).Name
           Next i
           wks.Range("A1").CopyFromRecordset rst
        End If
    End With
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cmd = Nothing
    Set cnn = Nothing
End Sub

Open in new window

0
Comment
Question by:cbsbutler
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 34961338
At what line do you ge the delay if you use a break ? at the Open or Refresh or .. ?
gowflow
0
 

Author Comment

by:cbsbutler
ID: 34961404
ive placed a msgbox either side of the rst.Open cmd

MsgBox "opening"
rst.Open cmd
MsgBox "opened"

There is a large delay between the two MsgBox's being displayed.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 34961434
what is the refrence you use for ADODB in Excel ?
Can you post your db or too big ? coz I tried your code and its fine.
Rgds/gowflow
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cbsbutler
ID: 34961481
Sorry can't post the db as it's too large.
The query in the mdb file literally takes 1 second to complete.

ADODB is using the "Microsoft ActiveX Data Objects 2.1 Library" reference
0
 
LVL 31

Expert Comment

by:gowflow
ID: 34963431
Sorry cannot reproduce your problem hence it will be difficult to troubleshoot.

Meantime you can try compact and repair database and see if this makes a diffrence.
rgds/gowflow

0
 
LVL 31

Expert Comment

by:gowflow
ID: 34963707
Looking at your sub again may I suggest following
try replacing
.CommandType = adCmdTable
by
.CommandType = adCmdTableDirect
or by
.CommandType = adCmdStoredProc

and see if it makes a diffrence.
Rgds/gowflow
0
 

Accepted Solution

by:
cbsbutler earned 0 total points
ID: 34968491
Hi I found this KB article...
http://support.microsoft.com/kb/146607

The DAO method is much much faster.

Thanks for your help, I'm going to close this question as I have found an answer.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 34968522
good for you. Actually I failed to tell you that I never used ADO I usually use DAO but its good to know that it is much faster. I nver use Command but Database.
gowflow
0
 

Author Closing Comment

by:cbsbutler
ID: 35005079
Found solution in KB article
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question