Tip This bit me in the ...... Autonumber - don't try this at home (1 Viewer)

Okay, first up, I know how to resolve the following but what a pain in the .......

In one of my applications I keep track of the user logins (session_id, version, dates, times, username, machine name) along with which backend data files (full paths) they are opening/closing.

The session information is recorded in another central backend data file. Users never get to see this other than a Session_ID number on the main form. The session data file is only opened as needed and closed immediately, no linked tables. It's purely for my purposes only; stats. Session data dates back to 2002, so there's a heap information there.

I have two tables;

tbl_Session
tbl_Session_History
The process is that my application directly updates the tbl_Session table with relevant session data. Session_ID is automatically created.

I've written a separate application (solely for me) where I can view the session data. In the first instance I can check which users are still logged in and to which backend data files. It will also show users that have subsequently logged out as those particular date/time fields will not be empty.

I have routines in place whereby I transfer the completed (logged out sessions) to the table, tbl_Sessions_Histroy, and then delete those entries from the table, tbl_Session. So tbl_Session will always have minimal entries.

Over the years I've done some Compact and Repair and now realise that there must have been at least one entry in the tbl_Session table. Two days ago, I did a Compact and Repair (hadn't done one for, well ages) but this time there mustn't have been an entry in the tbl_Session table. This effectively reset the Session_ID number back to 1. I didn't realise this immediately, not until I was transferring records to the history table, all of a sudden I got duplicate error issues.

What a pain.

I am a proponent of the AutoNumber but under this scenario it could bite you in the ....... if you're not careful.

Steve.

I transfer the completed (logged out sessions) to the table, tbl_Sessions_Histroy, and then delete those entries from the table, tbl_Session. So tbl_Session will always have minimal entries.

Click to expand...

Generally, if you move data around inside an application, you have a design problem. In a good design, data has a home, and you can put it there when it is created, and it can stay there.

Since you are effectively using the sessionID as a value with meaning, rather than simply identifying a record, you should consider using a different method for populating your SessionID - for example an incremental number (which would need to be stored in a different table) or some unique combination of values such as user/date

The parallel to the way you are using it is those developers who use the autonumber field to define a customer number - works fine until you delete a customer by mistake and want to restore it but can only do so by using a different number.

I've subsequently resolved this issue; got the autonumber back to where it should be. It wasn't that difficult to correct but I shouldn't have put myself in this position to start with. Always learning.

In the interim no more, willy nilly, compact and repair, until I figure out a more robust approach.

Many, many years ago, my design thoughts were that users logging in and out would only be accessing a table that had minimal records and therefore not a huge impact over a very slow network. I'm not sure if this is a valid concern or not but it looks like I will be testing it anyway. My first thought is just to use the one table and be done with it.

essaytee said:

I've subsequently resolved this issue; got the autonumber back to where it should be. It wasn't that difficult to correct

Click to expand...

Hi essaytee, would you mind explaining how you fixed it.?

Gasman said:

Hi essaytee, would you mind explaining how you fixed it.?

Click to expand...

1. Copied the subject table and data (tbl_Session) to a new table (tbl_Session_Copy). Only had about 20 records.

2. Modified the design of the copied table, changed the autonumber field to a number field only.

3. From my other table (tbl_Session_History) determined what the last number was ie. 100,500.

4. As my copied table had about 20 records, manually changed the number field starting at 100,501. (If I had a heap more records I would have created an Update Query, basically [Session_ID] = [Session_ID] + 100500.

5. Created an Append Query. Appended records from the copied table (tbl_Session_copy) back to the main table (tbl_Session).

6. The append query bypassed the autonumber field restriction.

7. From the main table (tbl_Session) this now had twice the number of records therefore deleted the records where the autonumbers were incorrect (effectively, deleting what I copied to the temporary table)

8. Autonumber now re-seeded.

Thank you for that.

I had visions of changing a parameter in tabledefs somewhere.