The function I have below is taking between 6 and 8 seconds to process [u]per record</u>, has anyone any idea's how I can speed it up?

The main field being processed is a 1500 character memo field.

Thanks Ben



Public Function GoGadgetGo(activityCode, scheduleStart, scheduleCode, attributeValue, exceptID)

Dim db As Database
Dim rec As DAO.Recordset
Dim rec2 As DAO.Recordset
Dim codeTrue() As Integer, codeFalse() As Integer, maxVal As Integer

Set db = CurrentDb
Set rec2 = db.OpenRecordset("SELECT Count(attrval.ATTR_VALUE_NAME) FROM attrval WHERE (((attrval.ATTR_ID) = 1));", dbOpenDynaset)

maxVal = rec2(0)

rec2.Close

ReDim codeTrue(maxVal)
ReDim codeFalse(maxVal)

For i = scheduleStart To (scheduleStart + (Len(scheduleCode) - 1))

activity = Right(Left(activityCode, i), 1)
schedule = Right(Left(scheduleCode, i), 1)

activity = Asc(activity)
schedule = Asc(schedule)

Set rec = db.OpenRecordset("SELECT attrmap.ATTR_VALUE_ID FROM attrmap WHERE (((attrmap.ATTR_ID) = 1) And ((attrmap.EXC_ID) =" & schedule & "));", dbOpenDynaset)

If activity = schedule Then
codeTrue(rec(0)) = codeTrue(rec(0)) + 1
Else: codeFalse(rec(0)) = codeFalse(rec(0)) + 1
End If

rec.Close

Next

Set db = Nothing

For i = 1 To maxVal

codeTrue(i) = IIf(codeTrue(i) = Empty, 0, codeTrue(i))
codeFalse(i) = IIf(codeFalse(i) = Empty, 0, codeFalse(i))

GoGadgetGo = GoGadgetGo & codeTrue(i) & "," & codeFalse(i) & ","

Next

i = Len(GoGadgetGo)
GoGadgetGo = Left(GoGadgetGo, i - 1)

End Function

Are you using Access97?

I'm using Access 2000

I've cut the processing time down to about 20 seconds for the whole query, the latest function I have is:

Public Function AdherenceMeasure(activityCode, scheduleStart, scheduleCode)

Dim db As Database
Dim adhVal As DAO.Recordset
Dim maxVal As DAO.Recordset
Dim attVal As DAO.Recordset
Dim codeTrue() As Integer, codeFalse() As Integer, i As Integer
Dim strActivity As String, strSchedule As String, bytActivity As Byte, bytSchedule As Byte

Set db = CurrentDb
Set maxVal = db.OpenRecordset("SELECT Count(attrval.ATTR_VALUE_NAME) FROM attrval WHERE (((attrval.ATTR_ID) = 1));", dbOpenSnapshot)
Set adhVal = db.OpenRecordset("SELECT attrmap.EXC_ID, attrmap.ATTR_VALUE_ID FROM attrmap WHERE ((attrmap.ATTR_ID) = 1);", dbOpenSnapshot)
Set attVal = db.OpenRecordset("SELECT attrval.ATTR_VALUE_ID, attrval.ATTR_VALUE_NAME FROM attrval WHERE (((attrval.ATTR_ID)=1));", dbOpenSnapshot)

ReDim codeTrue(maxVal(0))
ReDim codeFalse(maxVal(0))

For i = scheduleStart To (scheduleStart + (Len(scheduleCode) - 1))

strActivity = Mid(activityCode, i, 1)
strSchedule = Mid(scheduleCode, (i - scheduleStart + 1), 1)

bytActivity = Asc(strActivity)
bytSchedule = Asc(strSchedule)

adhVal.FindFirst ("EXC_ID = " & bytSchedule)

If bytActivity = bytSchedule Then
codeTrue(adhVal(1)) = codeTrue(adhVal(1)) + 1
Else: codeFalse(adhVal(1)) = codeFalse(adhVal(1)) + 1
End If

Next

adhVal.Close

For i = 1 To maxVal(0)

attVal.FindFirst ("ATTR_VALUE_ID = " & i)

If i = maxVal(0) Then
AdherenceMeasure = AdherenceMeasure & attVal(1) & Chr$(44) & codeTrue(i) & Chr$(44) & codeFalse(i)
Else: AdherenceMeasure = AdherenceMeasure & attVal(1) & Chr$(44) & codeTrue(i) & Chr$(44) & codeFalse(i) & vbCrLf
End If

Next

attVal.Close
maxVal.Close
Set db = Nothing

End Function

Ben,

I would try including an index in one of your queries. I know that using indexes helps queries run faster from past experience because I had to use an index for a database that I am currently working on for my Advanced Access class

Ben
Madison Area Technical College student
-------------------------
I am one of those people that you call "Microsoft Access Freaks". I'm addicted to Access