CODEWITHSUNDEEP
mysqlvbaexcelms-access
Jordan Adams
Jordan Adams

Reputation: 21

Copy Access database query into Excel spreadsheet

I have an Access database and an Excel workbook. What I need to do is query the database and paste the query into a worksheet.

The issue is Runtime. I have stepped throught the program and everything works, but it works extremely slow, we're talking up to 30 second run times per query, although most of this run time is coming with the CopyFromRecordset call.

The database has over 800k rows in the table I'm querying. Currently at my company there are people every morning who manually query the tables and copy and paste them into excel. I'm trying to remove this process.

Here is what I have:

Sub new1()

    Dim objAdoCon As Object
    Dim objRcdSet As Object

    ' gets query information '
    Dim DataArr()
    Sheets("Data2").Activate
    DataArr = Range("A1:B40")

    For i = 1 To UBound(DataArr)

        job = DataArr(i, 1)
        dest = DataArr(i, 2)

        If InStr(dest, "HT") > 0 Then
            OpCode = "3863"
        ElseIf InStr(dest, "HIP") > 0 Then
            OpCode = "35DM"
       End If

       strQry = "SELECT * from [BATCHNO] WHERE ([BATCHNO].[Job]='" & job & "') AND ([BATCHNO].[OperationCode] = " & "'" & OpCode & "')"

       Set objAdoCon = CreateObject("ADODB.Connection")
       Set objRcdSet = CreateObject("ADODB.Recordset")

       objAdoCon.Open "Provider = Microsoft.Jet.oledb.4.0;Data Source = C:\Users\v-adamsje\Desktop\HTmaster.mdb"
       'long run time
       objRcdSet.Open strQry, objAdoCon
       'very long run time
       ThisWorkbook.Worksheets(dest).Range("A2").CopyFromRecordset objRcdSet

      Set objAdoCon = Nothing
      Set objRcdSet = Nothing

   Next i

End Sub

Any help is appreciated. I am new to VBA and Access so this could be an easy fix. Thanks

Upvotes: 2

Views: 1866

Answers (3)

HarveyFrench
HarveyFrench

Reputation: 4568

Excel is very good at getting data for itself, without using VBA.

On the DATA ribbon

  1. create a connection to a table or view of data somewhere (eg mdb or SServer)

  2. then use the "existing connections" button to add data from your connected table to a worksheet table (ListObject).

  3. You can even set the workbook (ie connection) to refresh the data every 12 hours.

Repeat for all the tables /view you need to grab data for. You can even specify SQL as part of the connection.

Let excel look after itself.

I just grabbed a 250,000 row table from a "nearby" disk in 2 secs.

It will look after itself and has no code to maintain!

Upvotes: 1

Jordan Adams
Jordan Adams

Reputation: 21

Whelp, never found out why the CopyFromRecordset runtime was obsurd, but solved my problem by pulling the whole table into excel then into an array, looping through that and putting them in respective sheets. From 30min runtime to <1min

Upvotes: 0

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

I don't see how the CopyFromRecordset can be improved. You could copy the recods programmatically (in VB) record-by-record but that will probably be slower than the CopyFromRecordset.

You can move the CreateObject statements out of the loop, With the connection and RecordSet already created, this could be faster:

Set objAdoCon = CreateObject("ADODB.Connection")
Set objRcdSet = CreateObject("ADODB.Recordset")

For i = 1 To UBound(DataArr)
    ...
next i
Set objRcdSet = Nothing
Set objAdoCon = Nothing

You could also try ADO instead of DAO. ADO seems to perform faster on large record sets.

But also the server could be an issue, for example, are there indexes on Job and OperationCode? If not, then the slowness could be the server selecting the records rather than Excel placing them in the worksheet.

Upvotes: 0

Related Questions