Afternoon All!
I really need your help with this one, been going round in circles for ages now!
I've got a DB which simulates future waiting lists. This works absolutely fine, it's just extremely slow, 15 minutes to complete process. What I need to do is change from the SQL statements I use to an array. This is so that all of the calculations are done in memory instead of being written back to the DB all the time.
Only problem is I don't know anything about arrays. I've had a go and I can get the first record of the recordset into my array, but that's it, I can't get the next record in there. It's really frustrating now!
Here's the code I have so far:
I've got a lot of things DIM's up there because this is part of a large DB, I just need a little kick in the right direction to get the ole' brain working again. Haven't used Arrays for 8 years so I've completely forgotten everything to do with them. I've read everything I can find on this forum and many others but I just don't understand it now. Must be my old age.
Any help you could offer would be greatly appreciated!
Kind regards,
Ben
I really need your help with this one, been going round in circles for ages now!
I've got a DB which simulates future waiting lists. This works absolutely fine, it's just extremely slow, 15 minutes to complete process. What I need to do is change from the SQL statements I use to an array. This is so that all of the calculations are done in memory instead of being written back to the DB all the time.
Only problem is I don't know anything about arrays. I've had a go and I can get the first record of the recordset into my array, but that's it, I can't get the next record in there. It's really frustrating now!
Here's the code I have so far:
Code:
Dim DB As Database
Dim rst As Recordset
Dim X, Y, Z As Integer
Dim SID As Integer
Dim WeekY As Integer
Dim Pats As Integer
Dim NoToRemove As Integer
Dim NoToAdd As Integer
Dim myRemove As Recordset
Dim myAdd As Recordset
Dim myValue As Recordset
Dim myValue2 As Recordset
Dim myValue3 As Recordset
Dim NewValue
Dim NewValue2
Dim NewValue3
Dim MyNewValue
Dim sqlMakeTable As String
Dim sqlRemove As String
Dim sqlAdd As String
Dim sqlNewValue As String
Dim sqlNewValue2 As String
Dim sqlNewValue3 As String
Dim ProgAmount As Integer
Dim RetVal As Variant
Dim varArray() As Integer
Dim S As String
Dim intArrayCount As Integer
Dim intArrayCount2 As Integer
Dim intCounter As Long
sqlMakeTable = "SELECT tbl1stAttempt.[Weeks Waited], tbl1stAttempt.[Patients Waiting], tbl1stAttempt.Spec_ID, " _
& "tbl1stAttempt.WeeksSinceApr06, tbl1stAttempt.Spec_Code, tbl1stAttempt.Spec_Desc INTO tblTemp" _
& " FROM tbl1stAttempt, qryMaxWeek " _
& "WHERE (((tbl1stAttempt.[Weeks Waited]) = 0) And ((tbl1stAttempt.Spec_ID) = 3) And ((tbl1stAttempt.WeeksSinceApr06) = 52))" _
& "ORDER BY tbl1stAttempt.[Weeks Waited] DESC;"
DoCmd.SetWarnings False
DoCmd.RunSQL sqlMakeTable
DoCmd.RunSQL ("DELETE * FROM tblTemp")
DoCmd.SetWarnings True
ProgAmount = 1
RetVal = SysCmd(acSysCmdInitMeter, "Simulating Waiting List.....", 11077)
Set DB = CurrentDb
Set rst = DB.OpenRecordset("tbl1stAttempt")
intArrayCount = 0
intCounter = 0
ReDim Preserve varArray(0 To 3, 0 To intArrayCount)
SID = rst![Spec_ID]
X = rst![Weeks Waited]
WeekY = rst![WeeksSinceApr06]
Pats = rst![Patients Waiting]
intArrayCount = 0
intCounter = 0
With rst
.MoveLast
.GetRows
.MoveFirst
Do Until rst.EOF
varArray(0, intArrayCount) = X
varArray(1, intArrayCount) = Pats
varArray(2, intArrayCount) = WeekY
varArray(3, intArrayCount) = SID
ReDim Preserve varArray(0 To 3, intArrayCount + 1)
For intCounter = 0 To 3
Debug.Print varArray(intCounter, intArrayCount)
Next intCounter
intArrayCount = intArrayCount + 1
.Move 1
Loop
.Close
End With
I've got a lot of things DIM's up there because this is part of a large DB, I just need a little kick in the right direction to get the ole' brain working again. Haven't used Arrays for 8 years so I've completely forgotten everything to do with them. I've read everything I can find on this forum and many others but I just don't understand it now. Must be my old age.
Any help you could offer would be greatly appreciated!
Kind regards,
Ben