Getrow very slow (1 Viewer)

[sal21]

note:
- I have access database over netowork dir
- all fields filtered with "where" clausole are indexed

my conn:

...
ENVIOR = ""
    ENVIOR = VBA.Environ("COMPUTERNAME")
    
    'If CONN Is Nothing Then
        strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
        If Not (ENVIOR = "SS-72D68331754B" Or ENVIOR = "UTENTE-PC") Then
            strSource = "Data Source=\\ro\wo$\FS\Gest\DATABASE\T35.mdb;"
        Else
            strSource = "Data Source=C:\DATABASE\T35.mdb;"
        End If
        strConnection = strProvider & strSource & "Persist Security Info=False"
        Set CONN = New ADODB.Connection
        CONN.Open strConnection
        CONN.Properties("Jet OLEDB:Max Locks Per File") = 25000000
    'End If

my code to open rset:

....
        Set RS1 = New ADODB.Recordset
        RS1.CursorLocation = adUseClient
        RS1.Open SQL, CONN, adOpenStatic, adLockOptimistic, adCmdText
....

...
        Dim strDBRows() As Variant
        Erase strDBRows()
        strDBRows = RS1.GetRows()
        RS1.Close
        Set RS1 = Nothing
....

2 problem:
1) very slow to open recorset
2) very slow to fill the array from GetRows

possible to optimize?

 

[sal21]

Sorry for up!

 

[Old Man Devin]

There is nothing obviously slow looking in what you have posted, but it sounds like the problem is just that your recordset takes a long time to query, probably due to a complex query you are using or a vast number of records. So I think you should look to see if you can achieve the same goals with a less complicated recordset before trying to optimise the code.