I am invoking a function from within Access 2007 to perform a lookup on a table. If the row is found then the row data is returned, if the row is not found then the first unused row is updated in the table. Without the column Function call the query runs in about 4 mins. With the column function call it take over 3 hours. The VBA module does not do very much and is straight forward:
Option Compare Database
Public Function fGetProfileName(FormNum, JobFunction, Otran, CsiNum As String) As String
Dim dbsMfes_Reconfig_Automation As DAO.Database
Dim rstGetProfileName As DAO.Recordset
Dim rstAddProfileName As DAO.Recordset
Dim strGetProfileNameSql As String
Dim strAddProfileNameSql As String
Set dbsMfes_Reconfig_Automation = CurrentDb
strGetProfileNameSql = _
"SELECT [Profile Name], Form, [Job Function], [CSI Number] " & _
"FROM [Profile Names] " & _
"WHERE [Profile Names].[Form] = '" & FormNum & "' " & _
"AND [Profile Names].[Job function] = '" & JobFunction & "' " & _
"AND [Profile Names].[CSI Number] = '" & CsiNum & "'"
Set rstGetProfileName = _
If rstGetProfileName.EOF = False Then
fGetProfileName = rstGetProfileName![Profile Name]
strAddProfileNameSql = _
"SELECT Top 1 [Profile Name], Form, [Job Function], [CSI Number] " & _
"FROM [Profile Names] " & _
"WHERE [Profile Names].[Form] Is Null"
Set rstAddProfileName = _
If rstAddProfileName.EOF = True Then
fGetProfileName = ""
[TempVars]![ErrorCode] = 1
rstAddProfileName![Form] = FormNum
rstAddProfileName![Job Function] = JobFunction
rstAddProfileName![CSI Number] = CsiNum
fGetProfileName = rstAddProfileName![Profile Name]
Set rstGetProfileName = Nothing
Set rstAddProfileName = Nothing
The column function call:
Profile Name: fGetProfileName([Otrans for eLabel Temp 2].[eLabel FormNum],[Otrans for eLabel Temp 2].[eLabel Job Function],[Otrans for eLabel Temp 2].[Otran],(Val(IIf(IsNull([Otran CSI by Otran].[csi_id]),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[csi Number]),IIf([Otran CSI by Otran].[csi_id] In ('000000','999999'),IIf(IsNull([otran to csi mapping].[CSI NUMBER]),'43458',[otran to csi mapping].[CSI NUMBER]),[Otran CSI by Otran].[csi_id])))))
The table has and index on the approriate column.
Tuesday, July 09, 2013
Table: Profile Names Page: 1
DateCreated: 5/7/2013 1:51:33 PM DefaultView: 2
DisplayViewsOnShare 1 FilterOnLoad: False
GUID: HideNewField: False
LastUpdated: 7/9/2013 1:45:24 PM NameMap: Long binary data
OrderByOn: False OrderByOnLoad: True
Orientation: Left-to-Right RecordCount: 456976
TotalsRow: False Updatable: True
Name Type Size
ID Long Integer 4
Form Text 4
Job function Text 3
Profile Name Prefix Text 4
Profile Name Seq Text 4
Profile Name Text 8
CSI Number Text 10
Occurance Integer 2
Name Number of
CSI Number 1
CSI Number Ascending
Profile Name 1
Profile Name Ascending
Does anyone have any ideas what could be casuing the query, calling the VBA function that uses DAO, to run so slowly?
Am I right in understanding that your table [Profile Names] has a set of previously created records with Null in the [Form] field, so if you don't find a match you grab an unused record and fill it in?
I see two things that would likely be slowing this down a lot more than it needs to be. The first (and simplest) one is that you don't appear to have an index on [Job Function]. Since you're applying a criterion on that field, I would expect an index on the field to speed things up (though in fact it may not matter, depending on the query plan the database engine comes up with).
Second, and more important, is that you are calling CurrentDb for each call to the function. CurrentDb is very slow. Better is to get a reference to CurrentDB once and save it in a static object variable, and then use that static reference wherever you would use CurrentDB. Michael Kaplan wrote a fine method for doing this, which involves a public property procedure:
Private m_db As DAO.Database Public Property Get CurrentDbC() As DAO.Database If (m_db Is Nothing) Then Set m_db = CurrentDb End If Set CurrentDbC = m_db End Property
Then you use CurrentDbC where you would normally use CurrentDb. Note that you must not close the database returned by CurrentDbC, or subsequent uses will fail. (You shouldn't be closing CurrentDb, either, but Access reopens it if you do.)
With the above code in a public module, your code would be amended to use CurrentDbC instead of CurrentDb, also removing the line "dbsMfes_Reconfig_Automation.Close". That will speed things up a lot.
However, you are still opening and closing recordsets on queries inside the function. So if you are using this function as a calculated field in some query, it's not going to be fast. If there is no criterion on that calculated field, then probably Access will defer evaluating the function until the field's value is queried for display purposes, and so it won't be too bad even if you have a lot of records -- unless you do something in code to evaluate the field for every record. But if you have a criterion on the calculated column, then the function will have to be evaluated for every record, and that could be very slow.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
- Marked as answer by Dummy yoyoModerator Wednesday, July 17, 2013 1:01 PM