Solved

Access query to Excel spreadsheet

Posted on 2011-02-23
9
257 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 30

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 30

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 30

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 30

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 30

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

732 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