possibly need a way of error trapping

[miroku800]

I don't know if this should be here or in an Access forum but here goes

I have a VB6 program that uses an access database to record competitors and scores from a competion this works fine and i will call this database (A)

after each update is made database (A) copies itself to a preset location which I will call database (B)

so far everything is fine

at a remote location there is another computer with a cut down version of the main program which only allows competitors to view the results this uses a database I will call (C)

database (C) is a copy of database (B) and it also updates every time you return to the main selection screen

everything works fine for 99% of the time until database (C) attempts to copy (B) just at the point (A) is copying to (B) and the program on the remote computer crashes

and now to the question

does anyone know of a way of trapping the error caused by (C) trying to copy the database (B) when it is locked by (A) updating (B)
All I would require is for the remote computer to skip the update and just try again next time

I hope this makes sense and someone may be able to help me get past this annoyance as simply as possible

Many thank in advance

Regards Rob

[Killer42]

On a slight tangent, you might be able to reduce the chances of it happening by doing the A->B copy in two stages.

1. Copy A to B's location with a different name
2. Delete B and rename the copy.

End result is the same, but you spend much less time with B unavailable.

Um...

I think I may have missed the point of the question. I was assuming you were talking about copying the database files to another location. Were you actually referring to copying records from one database to another?

[pureenhanoi]

... after each update is made database (A) copies itself to a preset location which I will call database (B) ...

This is simple; create a lock file called (something.lock). No magic about the lock file. It's simply an empty file.
Before (A) updating (B), it creates a lock file. (C) will skip update from (B) till lock file is deleted.
When (A) completes updating (B), it delete lock file. So (C) can continue update data from (B).

[miroku800]

This is simply create a lock file called (something.lock). No magic about the lock file. It simply an empty file.
Before (A) updating (B), it create an lock file. (C) will skip update from (B) till lock file is deleted.
When (A) completelly update (B), it delete lock file. So (C) can continue update data from (B).

Thats exactly the problem when it finds the lock file it crashes the program.
I need to find a way for it to skip the update when it finds the lockfile

Many thanks for the replies so far, keep them coming

[miroku800]

On a slight tangent, you might be able to reduce the chances of it happening by doing the A->B copy in two stages.

1. Copy A to B's location with a different name
2. Delete B and rename the copy.

End result is the same, but you spend much less time with B unavailable.

Um...

I think I may have missed the point of the question. I was assuming you were talking about copying the database files to another location. Were you actually referring to copying records from one database to another?

No you were right the first time it is copying one database file but it doesn't matter which way i do it if it finds a lock file then it 'bombs' (and I must admit its only now and again) but I cant even make it restart automatically as it asks for a password to close it down so that the competitors cannot shut it down

[Killer42]

No you were right the first time it is copying one database file but it doesn't matter which way i do it if it finds a lock file then it 'bombs' (and I must admit its only now and again) but I cant even make it restart automatically as it asks for a password to close it down so that the competitors cannot shut it down

So, do you have any control over the program which is bombing? Can you rewrite it?

In any case, my suggestion should at least reduce the scale of the problem, since the file would be unavailable for a very brief period.

[miroku800]

So, do you have any control over the program which is bombing? Can you rewrite it?

In any case, my suggestion should at least reduce the scale of the problem, since the file would be unavailable for a very brief period.

I wouldn't like to have to rewrite it but could certainly modify part of it, as i said at the start 99% of the time its fine but just occasionally it bombs.
The copy only takes a couple of seconds anyway (it is a finite database as it is only used once a year for one 4 day competition and is cleared back to zero at the end ready for next year)

[Killer42]

I wouldn't like to have to rewrite it but could certainly modify part of it, as i said at the start 99% of the time its fine but just occasionally it bombs. ...

Sounds, then, as though it should be relatively simple to have it retry if the copy fails. But I don't think we have anything like enough information about that program to advise.

[pureenhanoi]

I wouldn't like to have to rewrite it but could certainly modify part of it, as i said at the start 99% of the time its fine but just occasionally it bombs.
The copy only takes a couple of seconds anyway (it is a finite database as it is only used once a year for one 4 day competition and is cleared back to zero at the end ready for next year)

The copy takes only a couple of seconds. The comptition apprear once a year. havent you got enought time to make the copy?

[Killer42]

The copy takes only a couple of seconds. The comptition apprear once a year. havent you got enought time to make the copy?

Must be a biiig file, huh. :)

Of course, I did once have a Windows file-copy dialogue tell me there were about 17 million seconds remaining...


Seriously, the file is only copied when updates are happening, so that would be during the competition.

[pureenhanoi]

Must be a biiig file, huh. :)

Seriously, the file is only copied when updates are happening, so that would be during the competition.

Awh, thats bad. I havent understand his mind yet.
No problem. Thats technique is as the same as the lock file. (now, we're using another file - instead of lock-file- it can be called: mark-file)
+ (A) will send data to (B) every time it has new data. When sending complete, it create a mark-file (called: anything.mrk) to tell (C) that: it's time to update.
+ (C) will skip update data from (B) till it see the mark-file. When updating is completed, (C) removes mark-file (thats tell to (A) that: ready to receive newer data, and tell to it-selft that: the data from(B) is now old, no need to update)
+ (A) has new data, it must take a look that: havent (C) completed updating data yet?(by looks into mark-file). If mark-file still existing, (A) will skip sending data to (B) till mark-file is removed.

[miroku800]

Awh, thats bad. I havent understand his mind yet.
No problem. Thats technique is as the same as the lock file. (now, we're using another file - instead of lock-file- it can be called: mark-file)
+ (A) will send data to (B) every time it has new data. When sending complete, it create a mark-file (called: anything.mrk) to tell (C) that: it's time to update.
+ (C) will skip update data from (B) till it see the mark-file. When updating is completed, (C) removes mark-file (thats tell to (A) that: ready to receive newer data, and tell to it-selft that: the data from(B) is now old, no need to update)
+ (A) has new data, it must take a look that: havent (C) completed updating data yet?(by looks into mark-file). If mark-file still existing, (A) will skip sending data to (B) till mark-file is removed.

I am now totally confused!!!

Surely if (C) can check for a 'mark-file' it could check for the presence of the 'lock-file' and just skip the copy if its there?

[Killer42]

I am now totally confused!!!

Surely if (C) can check for a 'mark-file' it could check for the presence of the 'lock-file' and just skip the copy if its there?

Yes, I'm inclined to agree.

Is it possible you could create a program which would simply copy the updated data from one database to the other, without having to copy the entire file? Or perhaps have A/B use the file in a shared mode, so C doesn't bomb when it tries to copy? (This last would have to be very thoroughly tested in case it allowed corruption of the data at C.)

[pureenhanoi]

I am now totally confused!!!

Surely if (C) can check for a 'mark-file' it could check for the presence of the 'lock-file' and just skip the copy if its there?

Mark-file is abit diffrent from lock-file. If lock-file exist, (C) will skip update (because (A) havent finished sending data yet). If mark-file exist, (C) will update data from (B) because the mark-file used to caution: have new data.
If you use mark-file, forgot all about the lock-file.
The client machine (C) will check the mark-file each time it need infomation from database. If mark-file exist, call updating process before reading database infomation.
Say something about updating process:
+ First: Copy data file from (B) to (C) with different name.
+ Close current connection.
+ Re-Open connection with new Data Source (can change datasource by Connection String), that refers to new database.
+ Remove mark-file
+ No need restarting program.
+ You should store atleast 2 filenames. On client app start, use the first file name. On updating database, use the second. On updating again, use the first...and so on.

[Killer42]

Ah! So the "mark file" is basically a sign saying "fresh data here, get it while it's hot".

[miroku800]

Mark-file is abit diffrent from lock-file. If lock-file exist, (C) will skip update (because (A) havent finished sending data yet)..

That would be fine if it would just skip the update cos it will try and update as soon as someone looks at another score which is very frequently
The problem is it won't skip if it can't update it just 'bombs'
all I need to know is how to make it skip on finding the lock file

[Killer42]

That would be fine if it would just skip the update cos it will try and update as soon as someone looks at another score which is very frequently
The problem is it won't skip if it can't update it just 'bombs'
all I need to know is how to make it skip on finding the lock file

For that, I think we will need more detail on what it is doing, and how, and what version of VB it is written in.

Can we see the code which is bombing? It might be as simple as doing something like On Error Resume Next and checking Err.Number to see whether the copy worked.

[pureenhanoi]

That would be fine if it would just skip the update cos it will try and update as soon as someone looks at another score which is very frequently
The problem is it won't skip if it can't update it just 'bombs'
all I need to know is how to make it skip on finding the lock file

Now, say something about skip updating:
the old process maybe like:

Private Sub cmdShowScore_Click()
score = getScoreFromDataBase()
Display score
End Sub
 

the new process must be:

Private Sub cmdShowScore_Click()
If isExistingMarkFile Then
 call updatingDataBase()
End If
score = getScoreFromDataBase()
Display score
Ens Sub
Private Function isExistingMarkFile() As Boolean
Dim fileName As String
fileName = "path to markfile"
If Dir(fileName)<>"" Then
 isExistingMarkFile = True
Else
 isExistingMarkFile = False
End If
End Function
Private Sub updatingDataBase()
'use each diffent file name for each time need updating
If currDbname = "(C)\db2.mdb" Then
 currDbName  = "(C)\db1.mdb"      'here is global varriable, use to store current database file name
Else
 currDbName = "(C)\db2.mdb"
End If
FileCopy "(B)\db1.mdb" currDbName    'overwrite old file if you need
'close current connection
con1.Close
'refer connection to new database
con1.ConnectionString ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & currDbname & "'"
're-open the connection
con1.Open
'remove the mark-file
Call Kill "path to mark-file"
End Sub
 

[Killer42]

...the new process must be:

Private Sub cmdShowScore_Click()
If isExistingMarkFile Then
 call updatingDataBase()
End If
score = getScoreFromDataBase()
...

I disagree, not in principle, but with the way it's implemented. This sort of thing should be handled in the getScoreFromDataBase routine, not the user interface. In other words, you shouldn't have to check for the "mark file" in each button-click routine.

[pureenhanoi]

I disagree, not in principle, but with the way it's implemented. This sort of thing should be handled in the getScoreFromDataBase routine, not the user interface. In other words, you shouldn't have to check for the "mark file" in each button-click routine.

Let see, why did you need checking for mark-fle in getScoreFromDatabase()? Each time user hit the button, we call getScoreFromDataBase() one time. That mean, if i placed the check function outside or inside the getScoreFromDatabase(), the check function will be called each time for the hit button event.
If you have another way to reduce the frequently of check-function, maybe its better to place check-function inside getScoreFromDatabase().
But thats not the problem.You can place the check-function anywhere you like, but you should check for newer data each time user need display data, or the data will be old.

[Killer42]

... But thats not the problem.You can place the check-function anywhere you like, but you should check for newer data each time user need display data, or the data will be old.

That's precisely my point. It should be "encapsulated" within the database lookup function, as a standard part of the process. Not something that the caller has to remember to check before calling the lookup.