Solved

Access query to Excel spreadsheet

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

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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

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 29

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 29

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

920 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

12 Experts available now in Live!

Get 1:1 Help Now