Faster Module? 4

[OAKEJ]

I Have a Module in excel that extracts a table from access and it works fine however it takes a long time for this to complete. It extracts about 18000 lines from my table. Is there anything in this code or anything that could speed this up, I know that's a lot of data, but I'm just curious

Option Explicit
Sub Access_Data()

Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
Dim MyConn, sSQL As String

Dim Rw As Long, Col As Long, c As Long
Dim MyField, Location As Range

'Set destination
Set Location = [A5]
'Set source
MyConn = "M:/Common/Oakes J/Freightliner Parts Analysis.mdb"
'Create query
sSQL = "SELECT * FROM YTD; "



'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
Set Rs = .Execute(sSQL)
End With

'Write RecordSet to results area
Rw = Location.Row
Col = Location.Column
c = Col
Do Until Rs.EOF
For Each MyField In Rs.Fields
Cells(Rw, c) = MyField
c = c + 1
Next MyField
Rs.MoveNext
Rw = Rw + 1
c = Col
Loop
Set Location = Nothing
Set Cn = Nothing
End Sub

 

[Chance1234]

   For Each MyField In Rs.Fields
            Cells(Rw, c) = MyField
            c = c + 1
        Next MyField

Thats your big performance killer right there using the loop approach

however,

you can pass the data straight over if there is no manipluation involved

look at CopyFromRecordset Method in the VBA help



Chance,

Filmmaker, gentleman and ROMAN!

 

[SkipVought]

Hi,

Check out the CopyFromRecordset method...

    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
        Set Rs = .Execute(sSQL)
    End With
[b]
    YourSheet.YourRange.CopyFromRecordSet Rs
  [/b]  
    Rs.Close
    Cn.Close
    Set Rs = Nothing
    Set Cn = Nothing

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Chance1234]

sorry my bad grammar. The For next loop is a killer in performance within your do loop. You could alternativly use somethign along the lines of

With rs
cells(rw,1) = rs.fields(0)
cells(rw,2) = rs.fields(1)
etc etc

however if you can use the copyfromrecordset use it

Chance,

Filmmaker, gentleman and ROMAN!

 

[ClulessChris]

Thanks to you both. I'd not seen the copyfromrecordset method before. Very nice, have a star.

Everybody body is somebodys Nutter.