VBA: request access using Excel. Why so slow?

Question

I found this code on the Internet for requesting access and entering data in Excel (2003), but it is much slower than it should be:

Sub DataPull(SQLQuery, CellPaste)
Dim Con As New ADODB.Connection
Dim RST As New ADODB.Recordset
Dim DBlocation As String, DBName As String
Dim ContractingQuery As String

If SQLQuery = "" Then

Else
    DBName = Range("DBName")
    If Right(DBName, 4) <> ".mdb" Then DBName = DBName + ".mdb"

    DBlocation = ActiveWorkbook.Path
    If Right(DBlocation, 1) <> "\" Then DBlocation = DBlocation + "\"

    Con.ConnectionString = DBlocation + DBName
    Con.Provider = "Microsoft.Jet.OLEDB.4.0"
    Con.Open

    Set RST = Con.Execute(SQLQuery)
    Range(CellPaste).CopyFromRecordset RST

    Con.Close
End If

End Sub

The problem is that this code takes a very long time. If I open Access and just run the request, it takes about 1/10 of the time. Is there any way to speed this up? Or for some reason it can take so long? All my queries are simple select queries with simple where statements and no joins. Even a request select * from [test]takes much longer than necessary.

UPDATE: I must indicate that the line

Range(CellPaste).CopyFromRecordset RST

was the one that took a long time.

Answer 1

. , .

, - .

Answer 2

, . , Command, Connection.

Set RST = Con.Execute(SQLQuery)

I

Dim cmd As ADODB.Command
Set cmd.ActiveConnection = con
cmd.CommandText = SQLQuery
Set RST = cmd.Execute

, , , , ?: -)

Answer 3

, .

Access, , :

• ( );
• ( );
• , Access ( DLL Access Database, ).

VBA:

• ( );
• ( );
• Excel - .

, , dataview Access , , , . ADO .

, ( ) , Excel .

, , .

, , . Access, , , . .

Answer 4

Recordset , , Execute. CursorType LockType, .

, , Excel, . , .. Recordset CursorType = adOpenForwardOnly & LockType = adLockReadOnly:

...
RST.Open SQLQuery, Con, adOpenForwardOnly, adLockReadOnly
Range(CellPaste).CopyFromRecordset RST
...

Recordset Object (ADO)

Answer 5

Access 2003, DAO, Jet.

. http://www.erlandsendata.no/english/index.php?d=envbadacexportdao .

, " ", .

Answer 6

38 63780 7 - , , - .

, ? , , ADO Excel MDB.

, , , .

Answer 7

, , Range(CellPaste) . ( Access, , CopyFromRecordset, .)

CopyFromRecordset MaxRows:

Public Function CopyFromRecordset ( _
    Data As Object, _
    <OptionalAttribute> MaxRows As Object, _
    <OptionalAttribute> MaxColumns As Object _
) As Integer

, (, 10 ) .

Answer 8

:

• XML (rst.saveToFile xxx), .
• (rst.getRows xxx)
• /: , , , , ..

Answer 9

, , VBA ADO Excel.

(< 5 ), (15 ). , .

, Excel ( ).

, .

Answer 10

9 10 / , .

, .

, CursorLocation = adUseClient . , , .

- , ADO, , .

I recently changed this because I had a simple loop filling the list item and each loop took about 0.3. Do not slow down, but even by 1000 entries, which is 30 seconds! Changing only the cursor location allows you to complete the entire process in less than 1 second.