Solved

Access query to Excel spreadsheet

Posted on 2011-02-23
9
252 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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:cbsbutler
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
Found solution in KB article
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now