Exclusive table locking using ODBC and Access97 or SQL 6.5 
Author Message
 Exclusive table locking using ODBC and Access97 or SQL 6.5



Quote:
> I currently writing an accounting type application using Access97 or SQL
> 6.5 back-end via ODBC/RDO2 under VB5.

> I need the capability to lock row in a table for exclusive access,
> increment a numeric field and return that new value back to my program.
> Very similiar to AutoNumber or Identity type fields (but managed myself).
> I also have other uses for this same concept.
> If another application is performing this same action at the same time,
how
> do I manage the contention?  How do I trap the locking violation and
retry?

> Now, if this was just SQL 6.5 I could generate a stored procedure with
> BEGIN TRANS/COMMIT TRANS type code.  But, since this is Access97 or SQL
> 6.5, how do I achieve the same thing from VB code?

> Adam

There are alternative third party database engines that are designed for VB
software distributors that offer you record level locking and superb
multi-user handling.  Some of these such as Oterro, http://www.*-*-*.com/
are file server based so they don't have the overhead expense and
maintenance of a client/server system, yet offer good 50+ concurrent user
performance.


Mon, 21 Feb 2000 03:00:00 GMT  
 Exclusive table locking using ODBC and Access97 or SQL 6.5

Quote:



> > I currently writing an accounting type application using Access97 or
> SQL
> > 6.5 back-end via ODBC/RDO2 under VB5.

> > I need the capability to lock row in a table for exclusive access,
> > increment a numeric field and return that new value back to my
> program.
> > Very similiar to AutoNumber or Identity type fields (but managed
> myself).

> > I also have other uses for this same concept.
> > If another application is performing this same action at the same
> time,
> how
> > do I manage the contention?  How do I trap the locking violation and

> retry?

> > Now, if this was just SQL 6.5 I could generate a stored procedure
> with
> > BEGIN TRANS/COMMIT TRANS type code.  But, since this is Access97 or
> SQL
> > 6.5, how do I achieve the same thing from VB code?

> > Adam

> There are alternative third party database engines that are designed
> for VB
> software distributors that offer you record level locking and superb
> multi-user handling.  Some of these such as Oterro,
> http://www.oterro.com
> are file server based so they don't have the overhead expense and
> maintenance of a client/server system, yet offer good 50+ concurrent
> user
> performance.

   I'm facing same problem as Adam. If I would stay with VB5, SQL 6.5 -
how to?


Sun, 05 Mar 2000 03:00:00 GMT  
 Exclusive table locking using ODBC and Access97 or SQL 6.5

Quote:



> > I currently writing an accounting type application using Access97 or
> SQL
> > 6.5 back-end via ODBC/RDO2 under VB5.

> > I need the capability to lock row in a table for exclusive access,
> > increment a numeric field and return that new value back to my
> program.
> > Very similiar to AutoNumber or Identity type fields (but managed
> myself).

> > I also have other uses for this same concept.
> > If another application is performing this same action at the same
> time,
> how
> > do I manage the contention?  How do I trap the locking violation and

> retry?

> > Now, if this was just SQL 6.5 I could generate a stored procedure
> with
> > BEGIN TRANS/COMMIT TRANS type code.  But, since this is Access97 or
> SQL
> > 6.5, how do I achieve the same thing from VB code?

> > Adam

> There are alternative third party database engines that are designed
> for VB
> software distributors that offer you record level locking and superb
> multi-user handling.  Some of these such as Oterro,
> http://www.oterro.com
> are file server based so they don't have the overhead expense and
> maintenance of a client/server system, yet offer good 50+ concurrent
> user
> performance.

   I'm facing same problem as Adam. If I would stay with VB5, SQL 6.5 -
how to?


Sun, 05 Mar 2000 03:00:00 GMT  
 Exclusive table locking using ODBC and Access97 or SQL 6.5

Quote:



> > I currently writing an accounting type application using Access97 or
> SQL
> > 6.5 back-end via ODBC/RDO2 under VB5.

> > I need the capability to lock row in a table for exclusive access,
> > increment a numeric field and return that new value back to my
> program.
> > Very similiar to AutoNumber or Identity type fields (but managed
> myself).

> > I also have other uses for this same concept.
> > If another application is performing this same action at the same
> time,
> how
> > do I manage the contention?  How do I trap the locking violation and

> retry?

> > Now, if this was just SQL 6.5 I could generate a stored procedure
> with
> > BEGIN TRANS/COMMIT TRANS type code.  But, since this is Access97 or
> SQL
> > 6.5, how do I achieve the same thing from VB code?

> > Adam

> There are alternative third party database engines that are designed
> for VB
> software distributors that offer you record level locking and superb
> multi-user handling.  Some of these such as Oterro,
> http://www.oterro.com
> are file server based so they don't have the overhead expense and
> maintenance of a client/server system, yet offer good 50+ concurrent
> user
> performance.

   I'm facing same problem as Adam. If I would stay with VB5, SQL 6.5 -
how to?


Sun, 05 Mar 2000 03:00:00 GMT  
 Exclusive table locking using ODBC and Access97 or SQL 6.5

Quote:
>    I'm facing same problem as Adam. If I would stay with VB5, SQL 6.5 -
> how to?

Just wrap the update and select statement into a single transaction. The
transaction isolation level 'read committed' that prevents reading of
uncommitted data should be enough and this is supported by both SQL6.5 and
Access.


Sun, 05 Mar 2000 03:00:00 GMT  
 Exclusive table locking using ODBC and Access97 or SQL 6.5

No, I still need an exclusive access - so that other users know that the
record is being hold by someone.

Quote:

> >    I'm facing same problem as Adam. If I would stay with VB5, SQL
> 6.5 -
> > how to?

> Just wrap the update and select statement into a single transaction.
> The
> transaction isolation level 'read committed' that prevents reading of
> uncommitted data should be enough and this is supported by both SQL6.5
> and
> Access.



Tue, 07 Mar 2000 03:00:00 GMT  
 Exclusive table locking using ODBC and Access97 or SQL 6.5

Quote:
> No, I still need an exclusive access - so that other users know that the
> record is being hold by someone.

You will know that the record was locked when the transaction fails. If you
don't like transactions you have to implement your own locking functions.
Most DBMS will give you no other synchronisation facility.


Fri, 10 Mar 2000 03:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Locking in VB5 and SQL Server 6.5 using stored procs

2. Question regarding Access97 linking to SQL Server 6.5

3. Access97/SQL 6.5 NULLs and Pass-through queries

4. Migrate from Access97 to SQL Server 6.5

5. Pesimistic Locking with Access97 from and SQL Server table

6. SQL Server 6.5 Locking

7. Record Locking in MS SQL Server 6.5

8. Record Locking, SQL Server 6.5

9. USing ASP migrating from SQL 6.5 to SQL 7.0

10. Get Username or Computername after lock (or deadlock) errors from MS SQL server (7 or 6.5)

11. ADO's Connection.beginTran will lock SQL Server 6.5

12. ODBC Direct and SQL Server 6.5

 

 
Powered by phpBB® Forum Software