UPDATING ACCESS DB

[hbez]

I'm using an Access DB in a voting program. One of the fields (VotedFor) relevant to a candidate is to record how many votes has been cast for that particular candidate. Problem is, if two or more voters vote for the same candidate at more or less the same time, only the first vote will be added to the total already recorded in the column while record locking will cause an error trying to record the later vote/s.
I dont have experience with it but, is a ClientDataSet maybe the way to go? And, is dbExpress maybe a better option than Access since it does not appear to have as restictive record locking?
Ideally it seems to me the record must be locked, updated, then released. Meanwhile, other votes must be placed in a q and written to the db without the user being involved in the process. Any suggestions?

Hannes

 

[lespaul]

I would expect that you would COUNT the votes not increment a field to record a new vote.

If you have a table that has all the votes in it:

[tt]
tCandidates
CandidateID
CandidateName

tVotes
VoterID
CandidateID

[/tt]

SELECT CandidateName, COUNT(V.CandidateID) FROM tVotes V INNER JOIN tCandidates C ON V.CandidateID = C.CandidateID GROUP BY CandidateName

will give you a list of the candidate names and the number of votes they received.

Leslie

Come join me at New Mexico Linux Fest!

 

[DjangMan]

I would agree with Leslie's approach. Have a table that stores the votes and then simply total them as required.

Now - you might still have locking problems when Access is being updated. If you can - add some code to re-try the posting (up to, say, five times with a short delay in-between attempts). That should get you past that issue.

Or - switch databases to one that uses record locking rather than page locking.

 

[hbez]

Thanks for that. I ended up having a new table to which every vote is INSERTed, recording the candidate and the election number (the program handles up to 20 elections at a time, as you may find in a school environment). I found that locking does not seem to affect posting an INSERT command. Then, when voting is done, I update the various election tables reading from this table. Hope I am right as far as the locking is concerned.

Hannes