SQL Table access question...

Morpheus

Right lads!!

Ive just got a new task from my boss ....

A VBA app is using sql tables, 3 tables

Maintbl,
QuotePricetbl
Batchtbl

I have multiple users with each a copy of the client app on their terminal.

A batch job consists of 3 steps....

Copy records from MainTbl except for the prices column, into BatchTbl.

Now we copy the prices from the quotepricetbl into Batchtbl.

Now do an update on Maintbl from Batchtbl/


at the minute when a user batches, the batchtbl is only accessible by one user at a time. Obviously this is undesireable, so i have suggested each user creating a temp table, the name will be genereated from their username concatenated onto a table name eg... "#BatchTblMaxJam" this way they all have their own temp batchtbl and it wont increase overhead on the DB....

Now my problem is that Im not too sure about what happens to the maintable while im copying from it or to it, will all users be able to use it, or will i have to give one user rights to access it at a time??? Have you any suggestions or ideas about an easier way to batch that im just not getting???

Basically what effect does one user updating MainTbl have on another user Copying from it????

thanks

Mick L

You can't have one user copying from a table while another user is changing the values and still have meaningful data unless you can ensure that the users will only access their 'own' data and you can implement a suitable locking mechanism (or the database has one built in that suits)

If your data is not too time sensitive, would it be possible to make a daily copy of the main table (in the case where the table might contain weekly products or offers etc) Users could copy this table when needed and the main table itself will still remain writable.

bonkey

Originally posted by Morphéus
Basically what effect does one user updating MainTbl have on another user Copying from it????

Updating every row in the main table will require an exclusive table-lock. While it is occurring, no other user will be able to access the table without performing what is called a "dirty read", which would have to be specifically coded, and would be a bad idea in most cases. If only some records are being updated, then it should only be the records being updated which are locked during the update...the rest of the table should be fine.

I can't understand how a user writing to some records in the batch table is locking it from all other users as things stand. I'm not aware of any database server
which behaves like that. Has it been deliberately coded that way???

jc

secret_squirrel

Originally posted by bonkey
I'm not aware of any database server
which behaves like that. jc

You could do pretty much anything screwed up you like if its the Jet engine

secret_squirrel

This is off the top of my head but bear with me....

Surely a better batch is just an update/insert on the maintbl with a join from the maintbl to quotetbl???

Then add a new column to the quotetbl say userid. Then drop a 'commit' button on the user dialog, to commit a particular users changes. If necessary you could even step through a recordset setting a flag if the update fails due to another process locking the row???

Prolly not quite what you want but should give you some idea??

bonkey

Originally posted by secret_squirrel
You could do pretty much anything screwed up you like if its the Jet engine

Glib, but untrue.

I've used the Jet engine successfully in any number of multi-user, simultaneous-access applications.

jc

Morpheus

1stly its a VBA-Access 2000-SQL system

Im looking into the way the batch system is structured this morning.

From that i will try to devise a devlish plan of action...

Pain in the arse is that all the existing SQL for the batch processing is embedded in VBA!!! dreaded access is the bane of my life.

i like the userID commit thingy, its not something ive done before, maybe you could elaborate???

Im trying to figure out why they cant just update the prices in one table directly form the other... theres a reason for the batch table in the middle, but quite what im not sure.

secret_squirrel

Originally posted by bonkey
Glib, but untrue.

Glib - yes. Untrue - no.
Access makes it incredible easy to code bad database transactions. You may have coded some nice multi-user systems - so have I.
Im betting you have also had to troubleshoot some dogs breakfasts - I know I have.
The way access allows you to throw transactions at the Jet Engine without stopping to think doesnt help. Its not the fault of the Jet engine but rather the ease with which access allows you to build DB's on it.

Im trying to figure out why they cant just update the prices in one table directly form the other... theres a reason for the batch table in the middle, but quite what im not sure.

That was my first thought too, unless Im missing something obvious. Thats what prompted my (slightly unfair) comments about the Jet Engine. My first impression was that it sounded like a typical diddly access application - the kind that has evolved rather than being planned.
My first thought as to why it might be there would possibly be to get around some record locking issues???

As for my 'commit' idea. I was thinking the commit button executing something similar to this.

update maintbl m
set m.price = (select qpt.price
from quotepricetbl qpt
where userid = strUser);

strUser would be passed into the code when you pressed the button

That should give you the general idea. Its certainly one way to skin this cat. Depending on locking issues you may want/have to code a loop to update each record individually. You may also want to flag any update that fails due to another user holding the record, say by adding another column to the quotepricetbl.

Then again you might want to split that kind of reporting off into a new 'usercentric' table.

Would post more but my Access is a lil rusty