bobdole22
08-27-2013, 01:15 PM
I need some help with this query. Is their a way to shorten it (Maybe Limit) or an error in the code? It runs, but runs extremely slowly. I takes like 5 whole minutes and nearly freezes my computer. It ends with 8370 total rows aftwerword. Can anyone help me improve the start up time?
Firstly, I am using .accdb files, so please don't help with code that only works with .mdb. That has already happend many times before :(. Also, if there is a limit, there needs to be a where clause and a limit (which hasn't worked in this program).
Option Explicit
Sub Access_Data()
'Requires reference to Microsoft ActiveX Data Objects xx Library
'"SELECT * FROM Data_All", "C:\Users\zachk\Desktop\Strats 2011.01.accdb
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 = [B2]
'Set source
MyConn = "C:\Users\zachk\Desktop\Strats 2011.01.accdb"
'Create query
sSQL = "SELECT Trust FROM Data_All WHERE BondIssue='C03B1T';"
'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.ACE.OLEDB.12.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
Please someone help me out! Thanks!
Firstly, I am using .accdb files, so please don't help with code that only works with .mdb. That has already happend many times before :(. Also, if there is a limit, there needs to be a where clause and a limit (which hasn't worked in this program).
Option Explicit
Sub Access_Data()
'Requires reference to Microsoft ActiveX Data Objects xx Library
'"SELECT * FROM Data_All", "C:\Users\zachk\Desktop\Strats 2011.01.accdb
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 = [B2]
'Set source
MyConn = "C:\Users\zachk\Desktop\Strats 2011.01.accdb"
'Create query
sSQL = "SELECT Trust FROM Data_All WHERE BondIssue='C03B1T';"
'Create RecordSet
Set Cn = New ADODB.Connection
With Cn
.Provider = "Microsoft.ACE.OLEDB.12.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
Please someone help me out! Thanks!