ADO connection, Execute method, and Excel VBA...

[Gedeon Kh]

Dear friends,

I'm creating an Excel Add-in that has several functions. Also I have a bid data in Access (more than 10 000 rows, and several tables).
The Add-in connects to DB when it opens. Then every function in the Add-in use RecordSets to read data from Access DB and do some calculations.

This method works. The problem is the speed )).
When I use a workbook that has lots of this Add-in functions, it works very slow And every time it takes several minutes to open the workbook.

Of course I could bring the data to Add-in worksheets, but this will overload the excel file.

So I would like to ask Excel/Access experts if there is another way to read the data and not to execute them every time with every function?

 

[LockeGarmin]

The slow speeds are probably going to be because of your use of Recordsets. How advanced are these calculations? Where are the calculations being written to? Unless your calculations are really complex you'll probably be better off using a Query/SQL then doing the work in Recordsets/VBA.

 

[Gedeon Kh]

Ok, do you think that executing a Query in every function will work faster than if I execute the recordsets/tables first, and than use functions to read data from recordsets?

 

[Gedeon Kh]

One of the tables (tbl_exchange) in DB is like this:

db_date | currency_iso | currency_rate
01/01/08 | USD | 100
01/02/08 | EUR | 150
.....
01/01/16 | USD | 110
01/02/16 | EUR | 160
.....

This can contain up to 10 000 records. So I didn't want to write them into the add-in worksheets.
In excel I use this function: get_rate("date", "iso")

Function get_rate(exDate As Date, Optional r_iso As Variant)

Dim RS As New ADODB.Recordset

Set RS = cn.Execute("SELECT currency_rate FROM tbl_exchange WHERE db_date = #" & exDate & "# AND currency_iso = '" & r_iso & "';")
If RS.EOF Then
    get_rate = "Rate not found"
    Else
    get_rate = RS!currency_rate 
End If

Set  RS = Nothing

End Function

This function is in my add-in, so when I start Excel it opens add-in and opens a new connection "cn":
So I use this function in my calculations in other excel workbooks, and it can be used for different dates at the same time in 50 cells for example.

So I'm looking for a faster way other than execute RecordSet every time.

 

[Kyle123]

That shouldn't really be slow. If the value isn't likely to change then it sounds like you need to build some sort of caching mechanism, there are various ways of doing this; but they will revolve around fetching the data the first time, storing it somewhere then the next time the function is called, it checks whether the value exists and returns it if it does.

 

[james_lankford]

10 000 records is nothing

grab the entire table by opening a recordset
then use the excel vba function to copy the entire recoredset into a range
https://msdn.microsoft.com/en-us/library/office/ff839240.aspx
it is very fast

now you have all your values in a range on worksheet
now if you want to look up a value on that worksheet use vlookup or index+match or write your own vba function

but it will be RIDICULOUSLY slow to connect to the database, open a recordset and pull in a single value in an excel function

if you have only 1 000 rows that's 1 000 trips to the database

that's crazy

 

[Gedeon Kh]

I think you are right.

If I connect to the db every time it will be slow.
If I cash the data in ram, it will be the same as to bring data to worksheets.

So the best way is to bring data to the worksheet than read from it.

 

[Gedeon Kh]

I face some other problem when I try to retrieve data from Access to Excel worksheet.

Here is my code

Private Sub get_data()

If cn.State = 0 Then connect_to_db
Dim wb As Workbook
Dim ws As Worksheet


Set wb = Application.ThisWorkbook
Set ws = Worksheets("data_sheet")


Application.ScreenUpdating = False


Set rs = cn.Execute("SELECT * from cb_exchange ORDER BY db_date")
i = 1
Do Until rs.EOF
    i = i + 1
    ws.Range("A" & i) = rs!db_date
    ws.Range("B" & i) = rs!currency_iso
    ws.Range("C" & i) = rs!currency_code
    ws.Range("D" & i) = rs!cb_rate
    rs.MoveNext
Loop


Set rs = Nothing


disconnect_from_db
Application.ScreenUpdating = True


end sub

It works BUT my number is 8.1811 : it writes to excel range this number: 8.18109989166259,

I can't understand what's wrong with my code ?

 

[james_lankford]

nothing's wrong with those numbers
what you're seeing in Access is just the rounded version of the number
but the number in Access is actually 8.18109989166259

also change to code to this
its teh copyrecordset function I mentioned above

Private Sub get_data()
    
    If cn.State = 0 Then connect_to_db
    
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = Application.ThisWorkbook
    Set ws = Worksheets("data_sheet")
    
    Application.ScreenUpdating = False
    
    Set rs = cn.Execute("SELECT * from cb_exchange ORDER BY db_date")
    
    ws.Range("A1")[COLOR=#000000][FONT=Consolas].CopyFromRecordset rs[/FONT][/COLOR]
    
    Set rs = Nothing
    
    disconnect_from_db
    
    Application.ScreenUpdating = True
    
end sub

 

[Gedeon Kh]

Thanks for the trick, this was faster.

But the value in Access is 8.1811, but excel returns it as 8.18109989166259: