Rx_
Nothing In Moderation
- Local time
- Yesterday, 22:17
- Joined
- Oct 22, 2009
- Messages
- 2,803
In the case of bringing back data from a Linked Table to a Local Table and then having a dozen functions work against the record set.
Make the network call once, use the data many times. Also prevent one of the records being edited in the middle of the analysis.
On some other post, the question as to why an autonumber (or autocounter) in the first place? There are the rare instant where a Rule Engine is making many passes against the data based on what it finds. Keeping track of the few unique index to make a final comparison for the determination can be regressive. In general, using the counter to repopulate a local table would not be normal. Make sure a local autocounter can be justified.
To use, call the subroutine and pass in the Table name followed by the autocounter field name.
Example of code to run: RE_ResetLocalTableAutoNumber "RE_1Seg_datapull", "ID_RE1seg_Datapull"
Make the network call once, use the data many times. Also prevent one of the records being edited in the middle of the analysis.
On some other post, the question as to why an autonumber (or autocounter) in the first place? There are the rare instant where a Rule Engine is making many passes against the data based on what it finds. Keeping track of the few unique index to make a final comparison for the determination can be regressive. In general, using the counter to repopulate a local table would not be normal. Make sure a local autocounter can be justified.
To use, call the subroutine and pass in the Table name followed by the autocounter field name.
Example of code to run: RE_ResetLocalTableAutoNumber "RE_1Seg_datapull", "ID_RE1seg_Datapull"
Code:
Public Sub RE_ResetLocalTableAutoNumber(LocalTableName As String, AutonumberFieldName As String)
' This will [B]delete all the existing data [/B]then reset the autocounter field back to 1 - resulting in an existing empty table with the counter starting at 1
' warning this will not work on linked tables, only works on local Access tables
' warning, this will error if the table is currently being edited e.g. The database engine could not lock table
' Example of code to run: RE_ResetLocalTableAutoNumber "RE_1Seg_datapull", "ID_RE1seg_Datapull"
On Error GoTo ErrorTrap
10 DoCmd.SetWarnings False
20 DoCmd.RunSQL ("Delete From " & LocalTableName)
30 Call ChangeSeedAutocounter(LocalTableName, AutonumberFieldName, 1, 1) ' sets to start at number 1
40 DoEvents
50 DoCmd.SetWarnings True
Exit Sub
ErrorTrap:
Debug.Print "RE_ResetLocalTableAutoNumber" & Err.Description
End Sub
