[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access query to Excel spreadsheet

Posted on 2011-02-23
9
Medium Priority
?
261 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

831 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