Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    INDIA
    Posts
    2

    Unhappy Unanswered: Adding New Column to Access DB with AutoNumber as DataType

    Hi
    I have an Access Database with a Table having 2lakh records. I want to add a new Column having DataType as AutoNumber and Field Type as Primary Key.

    I used the following SQL query to change the Table.
    "ALTER TABLE [customer] ADD UID COUNTER PRIMARY KEY;"

    It works well for some 20,000 records... but not for the table with 2lakh records. I am getting the error :"File sharing lock count exceeded.Increase MaxLocksPerFile registry entry" Please help me.....

    Pras

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    I've never used the "ALTER TABLE" keyword in an SQL statement, so I can't offer any insight to why that's not working.

    But

    I've never had any trouble doing it this way:
    Add an Autonumeric Field into a Table

  3. #3
    Join Date
    Aug 2003
    Location
    INDIA
    Posts
    2

    Talking

    Thank you for the response...I got the solution.

    objConn is as ADODB.Connection Object.

    For l = 0 To (objConn.Properties.Count - 1)
    If (StrComp(objConn.Properties.Item(l).Name, "Jet OLEDB:Max Locks Per File", vbTextCompare) = 0) Then
    objConn.Properties.Item(l).Value = 200000
    End If
    Next

    I was able to change the value during the runtime temporarily to execute the following SQL using ADO.Command Object.

    "ALTER TABLE [customer] ADD UID COUNTER PRIMARY KEY;"

    Regards,
    Prasanna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •