ado multiuser database app

hi Alex,
actually i do use transactions (with my SQL statements).
I annot use SQL Server, sorry :)
with other db aware comonents i cannot use transactions. such as dbgrids.
"you let ADO handle"...
don't think ADO will handle it, unless u tell him how to handle it (ie: cursor types/location)
what happens if a nother user deletes a record i'm currently working on, and so on?
if i delete a record in my application, access will imidiatly "see" that the record was deleted...
I will inc points for a good and persise explanation.
code will be appreteated also.

hi, i see a question that similar with your question last week

the link is
https://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20583310.html

may be help you

regards
hamid reza

reza,
thanks,
but i allready know that.

hi
put your application on you network ,the ADO will handles must of multiuser operation
I do it in ADO and its  work

best regards

usual you have nothing else to do,
the database has to care concurrent sessions,
not the app itself

meikl ;-)

We can not say that the application will stay intact while the database will handle all the multuser tasking.!
The application have to consider some points

For example; if the application generates some sort of an ID -that should be unique- in a table; then what if two instances of the application generates the same ID; and then attept to inssert a record based on that ID at the same time?

Look at the scenario:
1- SELECT Max(OrderID) FROM Orders
2- do some processing
3- INSERT INTO Orders(OrderID, .........) Values(OrderID+1, ........)

Here you will have a problem at a point of time:
If one user starts at step #1 and before going into step #3 another user started step #1. the latest to perform step #3 will face an error!
A transaction will not simply solve this issue because if you start a transaction berofre that block of code and end it afterwards; then no one else will be able to insert any values until your transaction is over; which can take so long as "processing" may last for the duration of showing a form and accepting entry from a slow user.

This might seem to be a hypothetical problem but you will see it one place or the other.

Take a look at:
http://members.tripod.com/appdevissues/downloads/Developing%20Multi-User%20Microsoft%20Access%20Database%20Applications.pdf
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrmultiuserdatabasesolutionarchitectures.asp

Hi all,
I think mocarts hit the "problem" i have. i also thoght ADO will handle mutiuser db "transparently", but if i change a record from within access i sometimes get an error saying "the record was changed" so i really need to trap this errors (?)

however if i delete a record in my app, MSAccess grid will imidiatly show "#DELETED" in his grid.
how to achive this behaviour in my app???

i thoght that using ctDynamic cursorType will solve my problems. but then i need to have cursorLocation = clUseServer. as soon as the table is active cursorType is automaticaly sets back to *ctStatic* (even if it was initialy ctKeyset).
this makes me a bit confused...

I use trasactions in every db operation i make, and all my PK are replication-id (GUID).

>Look at the scenario:
>1- SELECT Max(OrderID) FROM Orders
>2- do some processing
>3- INSERT INTO Orders(OrderID, .........) Values(OrderID+1, ........)

(i would say, bad coding)

its not a good idea to do it in this way,
for this databases have sequences, autonumbers or generators

or you can use a secondary table for this

meikl ;-)

>so i really need to trap this errors

you can use the onposterror-event to trap this error,
the dataset must be refreshed and changes must be discarded,
the user must begin for new

anyway, you cannot avoid such "errors" (this are no real errors)
in a multiuser-databases

even so be carefully with locks/or transactions,
because you can run into a deadlock

meikl ;-)

I did not say that this is a good coding.
It is indeed bad coding but; similar situations might arrise -not a replica of this nature- but similar.

I'm just trying to show that ADO will not simply do it on its own.
The developer have to code in a way that helps ADO to do handle it.

GUID is not a good choice for primary key, better use integer as it will increase querying speed.
I'm not sure that you can use cursor location clUseServer for mdb (access) database (which is not a server, but simple db file)
according to ctDynamic explanatoin in msdn - this future depends on data provider you use - check future list of your data provider (i.e. OLE DB for Jet (recommended for mdb) or other)
alternatively you can ignore these events and check for deletion or modified record only when going to select, update or delete it.

wbr, mo.

mocarts ,
> "GUID is not a good choice for primary key"...
well, i know, but this db is designed for replication (synconization) with other dbs. so it's the only working choice i have (but that is a nother story :) ).

basically i'm interested to know how the hell MSAccess grid imidiatly show "#DELETED" in his grid when i delete a record in my delphi app. how does it "sense" that my dataset was changed if Dynamic cursor are not supported in access? why doesnt delphi grid cant do so?

If i delete a record from access while my delphi grid is opend and try to edit a record i get:
"Row cannot be located for updating. Some Values have been change since it was last read" error.
Am i to asume this record was deleted? but i get this error when records are edited but other users... i'm confused.

how to achive this behaviour in my app???

i will begin testing tommoro and evaluate the answers gave to me here.
sorry about the delay with grading this q.
best regards.

morgantop:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.

I did not get an answer to this, so im deleting this q, if experts does not mind.
thanks for trying to help.