Chriss,
You are correct in that I have mixed two entirely different issues here, however both boils down to reading and writing remote views, which is kind of why I put it here instead of starting a new thread... Sorry...
Above, the "connection is busy" error was traced to the requery() command. Disabling it made the error go away. Problem is, 20-30 line later in code I need access to the newly created record to add more data once the data is known. When originally created, we only had enough data to create and start a session. I also get and save the new records PK for later use. Once authenticated, I add user identity info to it and the logon form and its attached views are closed.
Now for the @@Identity question. In Mike's article I referenced above, he clearly indicated what was wrong with using SELECT SCOPE_IDENTITY() and the reasons why @@Identity is better, I've totally missed the point. Maybe Mike will chime in... Instead of at the end of the session (minutes or hours) looking up the session to write an ending time to it by using the systemid and such, I now store the session's pk in a public variable and use that when when closing the session using a quick lookup to the pk.
Also note that doing a query for only the systemid and sessionid to find the max is slightly faster than doing it the original way of pulling all down, create an index, set order desc, go top, get larges number way. Four seconds down to 3 seconds, 25% increase. I haven't tried it yet, but I'm going to try a remote view made especially for this max thing, and hopefully that will deliver sub second result.
You also asked for some schema. The table has a pk that the database manages hence the need to capture it with @@identy upon creation of the session record. The session id is calculated based on the highest number of a subset and unique by concatenating systemid and sessionid together. The systemid is per location and stored and retrieved from a setup table.
What I'm really struggling and fighting with is all the errors, locks, busy, revert, updates and all things with remote views. With remote views, you can do this, but not that... Same with pass-thru. I'll get there, someday, and quicker with help from the experts like you and others...
Thanks so much,
Stanley
Thanks,
Stanley