Optimising Excel VBA database calls

[oni29]

My code is as follows:

Const szConnect = "Provider=MSDASQL;DSN=FGBTS-prod;"

Public Function getOrder_status(numOrder As Long) As String

    On Error GoTo getOrder_status_error
        
    Dim rsData As ADODB.Recordset
    Dim szSQL As String
    
    szSQL = "select Status from tblOrders where ORDER_NUMBER = " & numOrder
    
    Set rsData = New ADODB.Recordset
    rsData.Open szSQL, szConnect, adOpenStatic, _
                adLockReadOnly, adCmdText
   
    If rsData.RecordCount = 0 Then
        strReturn = "Error - Order not found in table"
        GoTo getOrder_status_end
    End If
    
    strReturn = rsData.Fields(0).Value
    GoTo getOrder_status_end
    
getOrder_status_error:
    
    strReturn = "Error: " & Err.Number & " " & Err.Description & " " & Err.Source
    
getOrder_status_end:

    'clean up
    rsData.Close
    Set rst = Nothing
    getOrder_status = strReturn

End Function

I've found that the code works fine. It does however take anywhere from 1-5 seconds to run. Is there any way of optimising this?

FGBTS-prod points to a Access 2002 database located on a Network mapped drive. I'm using this approach for two reasons:

• Not all the people using this macro have the Access Database mapped to the same drive letter.
• In the future I would like to move away from using Access to Oracle. I'm hoping using ODBC will make doing this move a bit easier (in terms of code re-writes)

 

[petehilljnr]

Here's an idea (just an idea, haven't tried it):

Create a Connection (MyConnection) to the database as a public variable within your application.
Open the Connection when your workbook opens.

Then, when calling your recordset, refer to your variable (MyConnection) in the Connection part.

I'm just guessing but seeing you've already created and opened the connection on start up, it should be quicker??

Happy to be corrected.

Pete.

 

[RoyVidar]

I don't think you need static, it should be enough with forwardonly.

If you do as suggested by petehilljnr and have a persistant connection, you could do

dim rs as adodb.recordset
set rs = cn.Execute(szSQL, , adcmdtext)
if rs.eof or rs.bof then
    ' no record...
end if

or open a connection, and execute on it

dim cn as adodb.connection
dim rs as adodb.recordset
set cn = new adodb.connection
with cn
    .connectionstring = szConnect 
    .open
end with
set rs = cn.Execute(szSQL, , adcmdtext)
if rs.eof or rs.bof then
    ' no record...
end if

air code

I don't know what differences there would be in programming when changing backends between ODBC and OLE DB, but I thought OLE DB would perform faster?