Refreshing a Database - Please Read 1

[CandieCane]

Hello everyone,
I'll try and explain this to you the best that I can.

I'm am making a webpage using dll's to connect to my access database. In this, I cannot use autonumbers in case that we wanted to convert our database from access to oracle or sql. So, I have a function that does this. It calls a table in our database, to see that last id in which to increment it everytime so there'll be no duplicate values in the primary key. Only problem is, in a normal registration form, you ask the user for atleast 3 types of phone numbers (in this case, I ask for home, work & fax). I have 3 separate functions to insert each different phone number (because of our database table you don't have much choice).

To get to the problem, it won't increment properly. I'm wondering if maybe I should refresh the database(or that specific table, if possible) after every phone function is called so that maybe it'll take the right last id to increment?! The weird thing is that the home phone number works fine, it's only when it gets to the second phone # (work) that it has problems, but than when it comes to the fax # it's fine again!

ANY help appreciated!!!! Candie ;o)

"Mere distances between 2 people bring you all that much closer in the end!"

 

[RFletch]

Hi,

When you say it has problems, can you explain exactly what happens?

Rletch

 

[JimHorton]

cane,
It's possibly a commit-syncronous issue in the registry, (explained below) but first, you should be incrementing the values with a function, that essentially opens a recordset on the 'number' table, increments the number, and returns the incremented number. Don't use Dmax, Dlookup, or sql to do the fetching or incrementing of the number--use a recordset and use dbpessimistic as the lock type, and trap for lock errors in this function.

For example, in the application, all users will be running the same function, the error trap will prevent the second user from fetching a number while the first is doing so--the trap should include a time delay (usually less than a second is more than enough) and then retry the fetch/increment.

Now, the registry issue. In the software\ms\jet\engines\3.5 (or 4.0 or whatever) there are 2 keys to look at: ImplicitCommitSync, and SharedAsyncDelay. The first one is either Yes or No. If you set to Yes, this should prevent the problems you're having, since what you describe could be due to delayed writing of the data, I've run into this before. However--this will slow down the app and create lots of network traffic (or disk activity on a local machine), since it's writing to the disk on every single update, instead of caching writes for a few seconds and then writing the group of updates in bulk, which is the default.

The second key, async delay, says that if Commitsync = no, then this is how many milliseconds it waits until it writes all pending updates. I think default is 2000, or 2 seconds, so you might try changing it to 100 (with implicitcommitsync = no), or a tenth of a second. Performance will be somewhat slower for bulk updates (say a recordset is updating 400 records--if it does 200 per second, then the disk is being written with a group of 20 at a time, where as with the 2000ms setting, it would write the whole 400 in one chunk--but the 20 at a time is still better than 1 at a time.
--Jim

 

[CandieCane]

Thanks alot Jim!! I understand what you mean but might just keep the error trap since I have no idea how to do that time delay and get in the software\ms\jet\engines 3.5 or 4.0.

Also, thank you, RFletch, for trying to help! ;o) Candie ;o)

"Mere distances between 2 people bring you all that much closer in the end!"