Hello,
       My name is Noman. I am working on vb6 as front-end application and SQL Server 2000 for data store.My problem is that i want to block a user to insert records in a particular table while other user is using it so is there any way to block any user through vb6 or SQL Server while other user is using it.
            I clarify it more.. for example if i take some records from account's tables to look the Report and for this i put these records in a temporary table but in the same time if another user want to see the report and he takes some records to insert in the same temporary table so i want to prevent second user not to insert these records while another user is working on it. So is there any way to prevent the user not to insert the records.
               Please solve this problem i shall be grateful to you.
                              Thank's a lot.

Noman

Look into the types of temp tables.
If you create a temp table with ## then it is available to all connection. If you use # then it is only available to the connection that created it. If you are doing connection sharing/pooling, and the temp table needs to be used for more than one transaction, this isn't as simple, but if you are doing one transaction, or you are not using connection sharing/pooling, then using a single # may solve your problem.


John R Lick
[email protected]

Well, I wish I had a bit more time to devote to this and hopefully I can point you in the right direction.

The first thing that come to my mind is the Lock Type used when creating the recordset. I would look at using pessimistic locking. This would give the first user to hit the data object full control of the data and read only access to all other users.

You can do this several ways if your using stored procedures you can use Lock Hints. If you are creating the recordset objects in code you can set the Lock Type there.

Hope this helps,

This is form the SQL Server help files, Locking, Hints

Locking Hints
A range of table-level locking hints can be specified using the SELECT, INSERT, UPDATE, and DELETE statements to direct Microsoft® SQL Server™ 2000 to the type of locks to be used. Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. These locking hints override the current transaction isolation level for the session.



Note The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary. Disallowing a locking level can affect concurrency adversely.


Locking hint Description
HOLDLOCK Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.
NOLOCK Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
PAGLOCK Use page locks where a single table lock would usually be taken.
READCOMMITTED Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server 2000 operates at this isolation level.
READPAST Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
READUNCOMMITTED Equivalent to NOLOCK.
REPEATABLEREAD Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.
ROWLOCK Use row-level locks instead of the coarser-grained page- and table-level locks.
SERIALIZABLE Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.
TABLOCK Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.
TABLOCKX Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.
UPDLOCK Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
XLOCK Use an exclusive lock that will be held until the end of the transaction on all data processed by the statement. This lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive lock applies to the appropriate level of granularity.


For example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.

USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors WITH (NOLOCK)
GO



I copied this from the MSDN help files, reference ADO Object Model, Recordset, Properties.

LockType Property (ADO)

Indicates the type of locks placed on records during editing.

Settings and Return Values

Sets or returns one of the following LockTypeEnum values.

Constant Description
adLockReadOnly Default. Read-only—you cannot alter the data.
adLockPessimistic Pessimistic locking, record by record—the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing.
adLockOptimistic Optimistic locking, record by record—the provider uses optimistic locking, locking records only when you call the Update method.
adLockBatchOptimistic Optimistic batch updates—required for batch update mode as opposed to immediate update mode.


Remarks

Set the LockType property before opening a Recordset to specify what type of locking the provider should use when opening it. Read the property to return the type of locking in use on an open Recordset object. The LockType property is read/write when the Recordset is closed and read-only when it is open.





Larry Asher

More on temp tables (from SQL Server Books Online):

Temporary Tables
You can also create temporary tables. Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use.

The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.

For example, if you create a table named employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect.



John R Lick
[email protected]