[VBA] Timing Workbook.Open vs SQL Connection

[skorpionkz]

Hi guys,

I curious have I done it correctly. I have lots of csv file which I am using as a database (I know it is not best way, but it is only way I can keep working on it without IT involved).
At the moment I am using Workbook Open method to open each of them and dump into array which i then converted to the classes and array is erased.
I am considering to use SQL connection rather than Workbook Open, therefore before I jump into deep water I decided to do little test. I open 1 of my files 1000 time with Workbook.Open and SQL connection. Then I check average time for it and how many times each of them was faster than the other method.
I was surprised to see that workbook open is actually faster method, but as I am not to familiar with SQL I probably made some stupid mistake. Could you guys have a look and advice if I can improve code somehow? Bonus point... when I using SQL it looks like database is loaded to the array transposed... is this how it should be?

So.. below is the Timing from the test, and then code for Workbook.Open followed by SQL

Excel 2010

	A	B	C	D	E	F
1	SQL	OPEN	Check
2	0.364819	0.307095	Average of 1000 rep			Count
3	0.234375	0.292969	SQL		SQL	25
4	0.351563	0.269531	OPEN		OPEN	970
5	0.339844	0.265625	OPEN		SAME	6
6	0.390625	0.308594	OPEN
7	0.351563	0.269531	OPEN
8	0.367188	0.332031	OPEN
9	0.472656	0.257813	OPEN
10	0.367188	0.449219	SQL
11	0.507813	0.308594	OPEN

<tbody>
</tbody>

.........

Workbook.Open

Public Sub DataArray(ByVal myFile As String)


With Application
    .EnableCancelKey = xlDisabled
    .ScreenUpdating = False
End With


Dim wb_CSV As Workbook
Dim ws_CSV As Worksheet
Dim LC As Long, LR As Long
Dim arr_Temp As Variant


Set wb_CSV = SetWorkbook(myFile, , True)
Set ws_CSV = wb_CSV.ActiveSheet


With ws_CSV
    On Error Resume Next
        .ShowAllData
    On Error GoTo 0
    LC = .Rows(1).Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    LR = .Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    
    arr_Temp = .Range(.Cells(1, 1), .Cells(LR, LC)).Value2
End With


wb_CSV.Close savechanges:=False




With Application
    .ScreenUpdating = True
    .EnableCancelKey = xlInterrupt
End With


End Sub


Public Function SetWorkbook(ByVal myFile As String, Optional ByVal str_Folder As String = Empty, Optional ByVal b_ReadOnly As Boolean = False) As Workbook


If str_Folder = Empty Then str_Folder = MyFolder


On Error Resume Next
Set SetWorkbook = Nothing
Set SetWorkbook = Workbooks(myFile)
    If SetWorkbook Is Nothing Then
        Workbooks.Open Filename:=str_Folder & myFile, UpdateLinks:=False, ReadOnly:=b_ReadOnly
        Set SetWorkbook = Workbooks(myFile)
    End If
On Error GoTo 0


End Function

SQL

Sub GetDataFromDatabase()


    Dim myConnection            As New ADODB.Connection
    Dim myRecordSet             As ADODB.Recordset
    
    Dim sConnectionString       As String
    Dim sSQL                    As String
    Dim iRow                    As Integer
    Dim iFieldCount             As Integer
    
    Dim vArray As Variant
       
    sSQL = "SELECT * FROM " & pbl_file_DETAILS
                           
    sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyFolder & _
                        ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
    
    myConnection.Open sConnectionString
    
    Set myRecordSet = myConnection.Execute(sSQL)
    
    vArray = myRecordSet.GetRows
    
    myConnection.Close
                
    Set myRecordSet = Nothing
    Set myConnection = Nothing




End Sub

Thank you guys.

 

[CalcSux78]

Try opening the data as read-only

Change this:

myConnection.Open sConnectionString

Set myRecordSet = myConnection.Execute(sSQL)

To this:

With myRecordSet
    .Open sSQL, myConnection, 0, 3
    vArray = .GetRows
    .Close
End With

More info here: https://www.w3schools.com/asp/met_rs_open.asp
and here:http://www.globaliconnect.com/excel...to-execute-sql-statements&catid=79&Itemid=475