Locking a table

[derek0903]

How can I lock a table to insert x records with .addnew so no one else will be able to add a record durring this process. I need to have continous record (ID) numbers? VB 6 - ADO 2.7 - Using Jet or SQL Server (depending on the user setup)

Thanks

[Memnoch1207]

Table locking isn't really used anymore. I know that as of SQL Server 6.5 and later it isn't supported, and MS Access doesn't supported it either (I could be wrong). I'm not sure why you would need to lock the table anyway, since standard locking (Record Level) just prevents users from accessing records which are already being viewed by another user. My guess would be that you shouldn't need to lock anything, since the records you are adding won't be viewed by another user, since they won't exist prior to you inserting them.

[derek0903]

Maybe I'm confused. But couldn't another user do an .addnew in the middle of my .addnew - causing my records to be non sequential?

Thanks

[Mandelbrot]

If you want to truly lock the table so that it can't be used, then rename it, add your records, then name it back.

You should find that an append query is fast enough to add the records that you want before anyone else can interfere with the sequence.

[techyspecy]

R u okay ?
Renaming the table might cause serious problems in other programs and the users using the table. It won;t just ignore this thing but will raise serious errors.

[Mandelbrot]

Yes - I'm fine, thank-you.

How can I lock a table to insert x records with .addnew so no one else will be able to add a record durring this process.

So, hmmm ... let me think - what better way to lock a table than to remove it from the attachments of other programs?

All you do thereafter is ... put it back!.

[abhijit]

SQL Server supports table level locks. If you wish to implement a table level lock
You could do the following.
1) Use "HOLDLOCK" when you select from a table.
Like for instance -
"Select * From Employees (TABLOCK HOLDLOCK)" when you open a recordset.
Use this in a transaction. Other users should not be able to enter records in the database.

this is what I did to keep the table locked until the update had been done.

    Dim adoCn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL  As String
    strSQL = "Select * From Employees (TABLOCK HOLDLOCK)"
    adoCn.Open "Provider=SQLOleDB.1;Initial Catalog=Northwind;DataSource=ZEUS;User ID=sa;"
    adoCn.BeginTrans
        rs.Open strSQL, adoCn, adOpenKeyset, adLockPessimistic
            Do While Not rs.EOF
                Debug.Print rs.Fields(0)
            rs.MoveNext
            Loop
        rs.Close
    adoCn.CommitTrans

Cheers!
Abhijit