How best to do an array in Access 2013 (1 Viewer)

GoodyGoody

Registered User.
Local time
Today, 09:27
Joined
Aug 31, 2019
Messages
120
Hi Majp, I get that. TBH, the coding logic isn't really that important it's more about theoretically how the begintrans/commit/rollback work and more specifically what rollback does to the current record indicator. So the first question is: Does Rollback reset the current record indicator? I am READING through recordset rs1 to get the individual data with which to create the team totals. I am also UPDATING rs1, the individual's record, with the team they 'might' be in as I step through since I don't know they are definitely in a team until the correct number of athletes has been reached. So, if ,say, the team requires 4 male members but it turns out there are only 3 left then I want to rollback the updates to the 3 team members who don't constitute a team. I thought that rollback would just undo changes made since the last begintrans/commit cycle but it also seems to reset the current record. This wouldn't normally be a problem and I suspect the solution is to create another recordset (say rs5) specifically for updating the individuals' records. I'm hoping then this will leave the current record indicator of rs1 in place as no updates will have happened to it. Does that make sense and are my assumptions correct.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:27
Joined
May 21, 2018
Messages
8,463
I applaud the academic curiosity and I often go down this path, but IMO you are making this overly complicated. But I believe to answer your question
When you use transactions, all databases and Recordset objects in the specified Workspace object are affected; transactions are global to the workspace, not to a specific database or Recordset. If you perform operations on more than one database or within a workspace transaction, the Commit and Rollback methods affect all the objects changed within that workspace during the transaction

To me what you are doing is equivalent to when people use error handling for code execution flow. So instead of adding an if check they let the code fail.
I don't know they are definitely in a team until the correct number of athletes has been reached.
So I find it hard to believe that you cannot determine ahead of time if you have enough athletes to fill a team. Even if I cannot do that in a simple query, I could build a simple function to determine this. This way I avoid using transactions in a non-traditional way.

So your idea of adding another RS to overcome this is IMO a band-aid on a band-aid. Take a step back and keep this simple. If you just feel compelled to use transactions then maybe switch this to ADODB. Transactions and batch updates are more robust in ADO. But IMO you are again building a Rude Goldberg machine.

 

Attachments

  • selfNapkin.jpg
    selfNapkin.jpg
    40.5 KB · Views: 195

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:27
Joined
May 21, 2018
Messages
8,463
If you use the # tag it is a lot easier to read allowing you to maintain indentations.

Code:
Private Sub btnCalculateRaceEventTeamResults_Click()

'On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim rs4 As DAO.Recordset
Dim sqlString As String
Dim intTeam As Integer ' Holds the number representing the team 0=A, 1=B, 2=C etc
Dim intTeamID As Integer ' The table "Team" primary key
Dim intTeamTotPos As Integer
Dim intTeamTotSecs As Integer
Dim intTeamCounter As Integer ' How many in the current team
Dim strTeamGender As String
Dim intMaxTeamCounter As Integer
Dim intClubID As Integer

'Make sure a race has been selected
If IsNull(Forms!frmPrintRaceEventTeamResults!cmbRaceName) Or IsNull(Forms!frmPrintRaceEventTeamResults!cmbRaceDate) Then
  intAnswer = MsgBox("Please select a valid race", vbCritical)
  Exit Sub
End If

intAnswer = MsgBox("You are about to recalculate the team results for this event. Doing so will remove all previous results. Do you wish to Continue", _
vbQuestion + vbYesNo)
'Exit if they don't want to continue
If Not (intAnswer = vbYes) Then
  Exit Sub
End If

DoCmd.SetWarnings (WarningsOff) 'This avoids confusing messages to the user that 'n' records will be being updated (or not!)


Set db = CurrentDb()


'First remove all the records for this race event that might have been previously calculated
DoCmd.OpenQuery "qryDeleteRaceEventTeamResults"

Set rs1 = fDAOGenericRst("qryRaceEventTeamResultsIndividual")

'Now read the Team table firsst to get the criteria for the teams
'Then read through the database doing first the teams for the women gender = 'F' and then the men 'M' gender = 'M'
'Write a team record for each set of male or female runners qualify for the team criteria
'Mixed teams will be dealt with later
'If the race is part of a series the series team criteria will take precedence over the race event team criteria

'If there are no records to process just end here
If rs1.EOF Then
  intAnswer = MsgBox("There are no records to process for this Event", vbCritical)
  Exit Sub
End If

'Read the RaceEvent or Series record to get the Team calclation ID
If rs1![RaceSeries] = 0 Then
    sqlString = "SELECT * FROM [RaceEvent] WHERE [ID] = " & rs1![RaceEvent]
    Set rs3 = db.OpenRecordset(sqlString, dbOpenDynaset)
    rs3.MoveLast 'this will "populate the recordset"
    intTeamID = rs3![RaceEventTeamID]
Else
    sqlString = "SELECT * FROM [Series] WHERE [SeriesID] = " & rs1![RaceSeries]
    Set rs3 = db.OpenRecordset(sqlString, dbOpenDynaset)
    rs3.MoveLast 'this will "populate the recordset"
    intTeamID = rs3![SeriesTeamID]
End If

'Now read the team table to get the correct criteria

sqlString = "SELECT * FROM [Team] WHERE [TeamID] = " & intTeamID
Set rs4 = db.OpenRecordset(sqlString, dbOpenDynaset)
rs4.MoveLast 'this will "populate the recordset"

rs1.MoveLast 'this will "populate the recordset"
rs1.MoveFirst

'Loop through the runners for the raceevent for each gender to calculate totals for each runner in the team
'Females are first in alphabetical sequence
'Increment the team ID for each new team

intTeamTotPos = 0
intTeamTotSecs = 0
intTeamCounter = 0
intTeam = 0
strTeamGender = rs1![RaceGender]
intClubID = rs1![RaceClub]
If rs1![RaceGender] Like "F" Then
   intMaxTeamCounter = rs4![NumOfFemaleAthletes]
Else
   intMaxTeamCounter = rs4![NumOfMaleAthletes]
End If

Set rs2 = db.OpenRecordset("TeamResults")
Do While Not rs1.EOF
  If Not strTeamGender Like rs1![RaceGender] Or Not intClubID = rs1![RaceClub] Then
   ' DBEngine.Rollback ' Undo any changes so far as there are not enough runners to form a team
    intTeamTotPos = 0
    intTeamTotSecs = 0
    intTeamCounter = 0
    intTeam = 0
    intClubID = rs1![RaceClub]
    If Not strTeamGender Like rs1![RaceGender] Then
        intMaxTeamCounter = rs4![NumOfMaleAthletes]
        strTeamGender = rs1![RaceGender]
    End If
  End If
'DBEngine.BeginTrans 'All database reads and writes will be held until 'Committed'
intTeamCounter = intTeamCounter + 1
intTeamTotPos = intTeamTotPos + rs1![RaceGenderPosition]
intTeamTotSecs = intTeamTotSecs + rs1![RaceTimeSecs]
With rs1
.Edit
  ![RaceTeam] = intTeam
.Update
End With

If intTeamCounter = intMaxTeamCounter Then
'Insert the team record
    With rs2
        .AddNew
            ![TeamSeriesID] = rs1![RaceSeries]
            ![TeamRaceEventID] = rs1![RaceEvent]
            ![TeamClubID] = rs1![RaceClub]
            ![TeamGender] = strTeamGender
            ![Team] = intTeam
            ![TeamTotPos] = intTeamTotPos
            ![TeamTotSecs] = intTeamTotSecs
        .Update
    End With
    'DBEngine.CommitTrans 'Commit the database changes so they are permanent and consistent
    intTeamCounter = 0
    intTeamTotPos = 0
    intTeamTotSecs = 0
    intTeam = intTeam + 1
End If
rs1.MoveNext
Loop

DoCmd.SetWarnings (WarningsOn)


Error_Handler_Exit:
On Error Resume Next
If Not rs1 Is Nothing Then
rs1.Close
Set rs1 = Nothing
End If
If Not rs2 Is Nothing Then
rs2.Close
Set rs2 = Nothing
End If
If Not rs3 Is Nothing Then
rs3.Close
Set rs3 = Nothing
End If
If Not rs4 Is Nothing Then
rs4.Close
Set rs4 = Nothing
End If
If Not db Is Nothing Then Set db = Nothing
Exit Sub

ErrorHandler:
'DBEngine.Rollback
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: cmd_AddRec_Click" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Sub
 

GoodyGoody

Registered User.
Local time
Today, 09:27
Joined
Aug 31, 2019
Messages
120
Hi MajP thanks as ever for your input but to me looping through the records individually makes sense. If not I would have to do some sort of variable query (the max number of team members differs potentially by race and gender) and then a modulus calculation to it to determine if the number had any remainder etc etc. It seems far easier and more explicit to loop through, update as I go along and just rollback the updates.

The answer to my question is that Rollback does indeed reset the current record so if I just bookmark the current record before the rollback and then reset the current record after the rollback job done and it works nicely and extremely quickly. E.g.

varbookmark = rs1.bookmark
dbengine.rollback
rs1.bookmark = varbookmark

Once again thanks for the feedback. And yes I didn't quite have the begin, commit, rollback logic right.
 

GoodyGoody

Registered User.
Local time
Today, 09:27
Joined
Aug 31, 2019
Messages
120

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:27
Joined
May 21, 2018
Messages
8,463
Is ADODB generally better than DAO or do both have their pros and cons
Both have their pros and cons. DAO is optimized for ACE/JET so when working with a native Access application you are better off with DAO. Faster more efficient. ADODB was designed to handle all kinds of datasources so if working with SQL Server or something else you want to use ADO. ADODB was going to be the go to and for a time DAO was going to be deprecated. At that same time ADO was coming around, .net was coming in force, vb was dying, and ADO.NET (which is completelty different) became the standard for vb applications. So DAO was not deprecated and ADODB kind of stagnated. There are somethings you can only do in ADODB such as in memory recordsets (which can be real useful). When working with Access I use DAO.

Out of curiosity and looking at the size of your results, is this process fast enough? My guess is there is no delay noticed. You are doing a lot of looping, but I imagine it is still pretty instantaneous. If you leveraged a little more SQL queries it would be even faster, but likely talking fractions of seconds.
 
Last edited:

adomanim

New member
Local time
Today, 02:27
Joined
Dec 25, 2019
Messages
7
I'm going through the labs in the App Development in Swift eBook, and i'm at the bit about accessing elements in an array contained in a dictionary.

I've got two arrays of strings, one containing names of shapes, and the other containing colours. The dictionary (called shapesAndColours) uses "Shapes" as the key for the shapes array and "Colours" as the key for the colours array.

The last task of the page is;

Print the last element of colorsArray, accessing it through the dictionary you've created. You'll have to use if-let syntax or the bang operator to unwrap what is returned from the dictionary before you can access element of the array.




snaptube telegram web
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:27
Joined
May 21, 2018
Messages
8,463
I would start a new thread with a appropriate title.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:27
Joined
Feb 19, 2002
Messages
42,970
I want to avoid multiple reads on the database
MajP is 100% correct. Normalize the table and use simple queries to do the calculations. This is not the place to resort to code.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:27
Joined
Sep 12, 2006
Messages
15,613
instead of how, why not why? why do you want an array?

how often do you read the array. If it's 52 integers, you could store it as a text string with 52 elements separated by commas or other character. Then use split to put the text back into a 26x2 array. You will probably also need a function to write your internal array back to the table.

Alternatively, if you only have 52 elements, just store them in a table with ID's 1 thru 52. You can easily translate a 2D array index to a linear value, and vice versa.

The usefulness of all this will depend on how often the array contents change. If it's volatile, then your data may be out of date at any time.

[edit. most of this is what TheDocMan said in his first reply, now I read it again]
 

Users who are viewing this thread

Top Bottom