Requery in P2P (XPDev)

Topic

New Reply

I just set up my app on a P2P and am a bit concerned about retaining the following code (with thanks and a tip of the hat to HansV) in a shared environment. This code requeries the form to diplay whether a new order has been entered after the form was opened. It is in the ‘On Timer’ event of the continuous form; I have “suspended” it for the time being.

Private Sub Form_Timer()
Dim lngCount As Long
lngCount = DCount(“*”,”tblMaster”,”OrderStatus=’NEW'”)
If lngCount > 0 Then
Searching.Caption = “New order(s) found. Click ‘Refresh’ to display: ” & lngCount
Searching.Backstyle = 1 Normal
Else
Searching.Caption = ” ”
Searching.Backstyle = 0 ‘ Transparent
End If
Me.Requery
End Sub

Also, while I’m at it, what record locking strategy is best for a P2P environment?

Any input greatly appreciated!

Reply | Quote

Replies

To answer your second question first, the best strategy with 2000/2002 is to do record locking rather than page locking, and in general you want to use optimistic locking. In your case you shouldn’t see problems in general anyhow with only two or three workstations entering data. As to the second question, it shouldn’t cause you any grief in a shared environment, and would be more useful, as all users would see NEW records entered by other users. I’m not sure about the necessity of it however, as Access does check periodically for new records on it’s own, and with an interval that you can adjust to suit your needs. Hope this helps.

Reply | Quote

Thanks for the assist!

I re-set the code and it works just fine (thanks for alleviating my paranoia).

I have a bit of a curiosity as to the record locking issue though. I am occasionally ( as in not always) encountering Record Locking (.ldb file) after exiting the FE app. The only way I can re-open the FE is to reboot. My Option settings are:

PC-1 (Host)
Backend
Advanced | Default Open Mode | Shared
Advanced | Default Record Locking | Edited Records
Advanced | Open Databases Using Record-Level Locking checked
General | Compact On Close
Frontend
Advanced | Default Open Mode | Exclusive
Advanced | Default Record Locking | No Locks
Advanced | Open Databases Using Record-Level Locking NOT checked
General | Compact On Close

PC-2 (Client)
Frontend
-Same as PC-1

PC-3 (Client)
Frontend
-Same as PC-1

I have “assumed” (uh-oh) that the FE on all stations do not need to have record locking as a result of being separated from the BE. However, when I think of what the On Timer code does, perhaps that is not true. A bit confused there. I also discovered that I have made a huge error when appending old data into the tblMaster yesterday, but that’s another post. (too many irons in the fire?)

Reply | Quote

Don’t make your default mode exclusive on the FE and I would recommend you set recordlocking on in the front end. The processing occurs in the front end, not the back end, so queries, etc., will use the front end settings by default. You may be encountering a lock file because the database is still compacting. Try deleting the ldb file instead of rebooting.

Why do you list PC-2 (Client) for the frontend twice?

Reply | Quote

Charlotte:

Thanks for the help. Sorry about the PC-2 duplication, the second one should have been PC-3 (now edited). So, are you saying that the FE on all stations should be the same as I have for the BE on the host? i.e.:

PC-1,PC-2,PC-3
Frontend
Advanced | Default Mode Open | Shared
Advanced | Default Record Locking | Edited Records
Advanced | Open Data Bases Using Record-Level Locking checked
General | Compact on close

I couldn’t delete the .ldb file, the only thing I could do to get rid of it was to re-boot. The BE was still open at the time I encountered the .ldb on the FE, so I don’t think it could have been compacting at the time.

Reply | Quote

You didn’t say which machine you were on when you encountered the lock. The font ends on the other machines are copies, right, not just shortcuts to open the front end on PC1? If they’re copies, then they could be opened exclusively but not if they are running the front end from the mapped shared drive. In the latter case, having the application open from another machine would leave the lock there.

Once in a while a lock file will get stuck like that even on a regular network and the only cure for it that I’ve found is to log out and sometimes to reboot. Since you’re using Win9x, reboot is the only option in that case. It can happen if the program ends abnormally and the Access process is still open. As I recall, you can’t actually see the processes in the Win 9x task list, so there’s no way for you to kill it without a reboot.

Reply | Quote

Charlotte:

I was on PC-2 when it happened the last time, although it has also happened on PC-3. I did go ahead and tried the new settings of Shared, Edited Record Locking, and Record-Level locking on all FE. I’ve tried everything I can to make it freeze-up (except turning off the host) and can’t repeat the problem. So, your suggestion seems to have resolved the problem!

Reply | Quote