Excel pull from Access (speed up the process)

[JamesDSM50328]

I am trying to speed up the process of ADO connection from EXCEL to ACCESS db. I made a excel function that passes a SQL statement from each cell to access and returns the answer value for that cell. Currently running aprox. 30,000 queries that was taking only 3 mins, but now is taking 20 mins. Any help would be greatly appreciated.

this is the formula/function that passes the SQL statement
=IF(ISERROR(date),0,xlbasSQLFunctions.TotalVolume("SELECT Sum(Volume) AS Vol2 FROM tbl_RL_Total_Product_Volume WHERE (((Entity)='CMD') AND ((Product)='FIX20' Or (Product)='FIX30') AND ((Amort)=1) AND ((Date)=##) AND ((Family)='C'));",A19,"Vol2"))

The reason is that we need to have the funtionality of pulling different tables and criteria based off the Excel sheets data.

'Excel cell function
Function TotalVolume(sqlstring As String, dt As String, fld As String) As Double
Dim rs As New ADODB.Recordset
Debug.Print sqlstring
If adoConn Is Nothing Then
Conn
End If
sqlstring = SQLaddDt(sqlstring, "#", dt)
On Error Resume Next
rs.Open sqlstring, adoConn, adOpenForwardOnly, adLockReadOnly
'Set rs = adoConn.Execute(sqlstring)
Debug.Print Err.Description & " " & Err.Number
If rs.BOF = True And rs.EOF = True Then
    TotalVolume = 0
Else
    TotalVolume = rs.Fields(fld).Value
End If

If rs.State <> 0 Then
    rs.Close
    Set rs = Nothing
End If

End Function

'this adds the date to the SQL Statement
Function SQLaddDt(sqlstring As String, delimiter As String, strdt As String) As String
Dim i As Integer
Dim sql1 As String
Dim sql2 As String
For i = 1 To Len(sqlstring)
    If Mid(sqlstring, i, 1) = "#" Then
        sql1 = Mid(sqlstring, 1, i)
        sql2 = Mid(sqlstring, i + 1, Len(sqlstring))
        SQLaddDt = sql1 & strdt & sql2
        Exit For
    End If
Next i
Debug.Print SQLaddDt

End Function

Public Function Conn() As Boolean
'Connection
Set adoConn = CreateObject("ADODB.Connection")
    With adoConn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath & ";Jet OLEDB:Database Password='PW';"
        .Open
        
        
    End With
    If adoConn.State > 0 Then
        Conn = True
    ElseIf adoConn.State = 0 Then
        Conn = False
    End If
End Function
Public Function CloseConn() As Boolean
    On Error Resume Next
    With adoConn
        .Close
    End With
    If adoConn.State > 0 Then
        CloseConn = False
    ElseIf adoConn.State = 0 Then
        CloseConn = True
    End If
    Set adoConn = Nothing
    On Error GoTo 0

End Function

 

[SkipVought]

Hi,

Each time you call your function you...

Open the DB
Execute the query
Close the DB.

Instead,

Make a sub that...

Turn calculation to MANUAL
Opens the DB
calculate the sheet, (modify your function, removing the DB open & close)
Close the DB

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JamesDSM50328]

Thanks for the suggestions, that helped shave off a couple mins. I'm pretty sure most of it is network traffic and it getting caughtup in access. I also turned off the screenupdating which help a little bit as well.