Hey Guys & Gals,
Here is my problem: I have a Win2000 Pro Network that runs an application written in VB that is a front end for an Access 97 Database. When adding records via a DAO based SQL command there is a variable time delay before the new record can be accessed by bound controls. For instance, when adding a new Inventory item several tables are effected, and so it must be done programatically using a series of SQL "Insert...." statements. Once the record has been added, a DAO data control recordset is refreshed, and the new item should appear in a data-bound list control. The problem is that sometimes it does, some times it does not. By creating a search for a particular field value that the new item contains, and looping through a series of "Refresh" commands I can create a delay that "pauses" the program until the new item finally makes it into the data control's recordset. Sometimes the new record shows up on the first refresh, sometimes it must refresh thousands of times. A typical iteration count is around 125 Refreshes before the record finally shows up. This is horribly sloppy and slow. I have tried using Begin Trans and Commit Trans, but this does not help. The nature of the add new process in the front-end requires the new item to be present in the recordset before further processing can continue. I have altered my registry according to the article below, but this has only stopped the database from becoming corrupted (something that was happening with alarming frequency previously.) Suggestions about changing to ADO have been numerous, but this would require a tremendous amount of rewriting, and I am not convinced that it would solve the problem. I sure would like some suggestions.
Thanks,
Hunter
The Following is taken from Microsoft Knowledge Base (an oxymoron?)
"Disable Opportunistic Locking (oplocks) on the Network File Server
Microsoft has discovered an issue where opportunistic locking can increase the possibility of Jet database corruption when the file is shared by two or more clients on a network file server. This issue applies to Microsoft Windows NT 4.0, Microsoft Windows 2000, and Novell file servers that support opportunistic locking. This issue is still under investigation. Microsoft will post further information to this article when it becomes available. To disable opportunistic locking on a Windows NT 4.0 or a Windows 2000 file server, locate the following registry key on the network file server.
WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.
For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT or Windows 2000, you should also update your Emergency Repair Disk (ERD).
HKEY_LOCAL_MACHINE/System/CurrentControlSet/Services/LanmanServer/Parameters
Under this key, create a new DWORD value named EnableOplocks and set its value to 0 to disable opportunistic locking. Reboot the network file server to ensure that the setting is accepted."
Here is my problem: I have a Win2000 Pro Network that runs an application written in VB that is a front end for an Access 97 Database. When adding records via a DAO based SQL command there is a variable time delay before the new record can be accessed by bound controls. For instance, when adding a new Inventory item several tables are effected, and so it must be done programatically using a series of SQL "Insert...." statements. Once the record has been added, a DAO data control recordset is refreshed, and the new item should appear in a data-bound list control. The problem is that sometimes it does, some times it does not. By creating a search for a particular field value that the new item contains, and looping through a series of "Refresh" commands I can create a delay that "pauses" the program until the new item finally makes it into the data control's recordset. Sometimes the new record shows up on the first refresh, sometimes it must refresh thousands of times. A typical iteration count is around 125 Refreshes before the record finally shows up. This is horribly sloppy and slow. I have tried using Begin Trans and Commit Trans, but this does not help. The nature of the add new process in the front-end requires the new item to be present in the recordset before further processing can continue. I have altered my registry according to the article below, but this has only stopped the database from becoming corrupted (something that was happening with alarming frequency previously.) Suggestions about changing to ADO have been numerous, but this would require a tremendous amount of rewriting, and I am not convinced that it would solve the problem. I sure would like some suggestions.
Thanks,
Hunter
The Following is taken from Microsoft Knowledge Base (an oxymoron?)
"Disable Opportunistic Locking (oplocks) on the Network File Server
Microsoft has discovered an issue where opportunistic locking can increase the possibility of Jet database corruption when the file is shared by two or more clients on a network file server. This issue applies to Microsoft Windows NT 4.0, Microsoft Windows 2000, and Novell file servers that support opportunistic locking. This issue is still under investigation. Microsoft will post further information to this article when it becomes available. To disable opportunistic locking on a Windows NT 4.0 or a Windows 2000 file server, locate the following registry key on the network file server.
WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.
For information about how to edit the registry, view the "Changing Keys and Values" Help topic in Registry Editor (Regedit.exe) or the "Add and Delete Information in the Registry" and "Edit Registry Data" Help topics in Regedt32.exe. Note that you should back up the registry before you edit it. If you are running Windows NT or Windows 2000, you should also update your Emergency Repair Disk (ERD).
HKEY_LOCAL_MACHINE/System/CurrentControlSet/Services/LanmanServer/Parameters
Under this key, create a new DWORD value named EnableOplocks and set its value to 0 to disable opportunistic locking. Reboot the network file server to ensure that the setting is accepted."