ENtering data in a new column UniqueID in a table (1 Viewer)

[aman]

Hi guys

I have a table in which there are present about 14000000 records. Now my problem is in the table there is no unique key defined and if I try to define an autonumber field then it gives me "file sharing lock count exceeded. increase maxlocksperfile registry entry." error message and I can't change it becoause I don't have admin rights.

Now I have created a new column (UniqueID) with datatype Text. How can I insert values from 1 to last record count in the UniqueID field.I suppose I need to update UniqueID field in a loop.Please can anyone tell me how to do it.

Thanks

 

[SimonB1978]

Hi,

This should give you an idea....

Sub AddID()

    Dim rst As DAO.Recordset
    Dim lngID As Long
    
    Set rst = CurrentDb.OpenRecordset("Table1")
    
    lngID = 1
    
    While Not rst.EOF
        rst.Edit
        rst.Fields("ID") = CStr(lngID)
        rst.Update
        
        lngID = lngID + 1
        
        rst.MoveNext
    Wend
    
End Sub

HTH,

Simon

 

[aman]

Thanks Simon. But ehrn I run this code then it gives me "runtime error 3001", Invalid argument.

Please help me out.

Thanks

 

[stopher]

Just an idea...

Create a new table with the same structure but with an autonumber added. Then run an Append query to put the records from the old table in the new table.

Chris

 

[aman]

Stephen, If I try to append data in a new table uwisng the following code then it gives me runtime error 3001 Invalid argument :

Private Sub Command0_Click()
Dim ssql As String
ssql = "Insert into test select * from maintable;"
[COLOR=red]DoCmd.RunSQL ssql
[/COLOR]End Sub

Please help me.

 

[JANR]

Google runtime error 3001 Invalid argument and a few tips will emerge.

- You don't have a table called test OR mastertable
- You have reached access 2 GB limit or are going to when you run the insert.
- Corruption

The code runs perfectly on my test.

JR

 

[aman]

Hi

In my table there are present 1378500 records and I think why this error is coming when I try to insert data from one table to another.
SO if you can look at my first post then can you please tell me how to insert data in UniqueID field in all the records.

Thanks

 

[stopher]

I just tried to insert using 1.5million records and it worked fine. The autonumber was generated correctly. In fact I did it twice for good measure.

But in your first post you said you had 14 million records. That's quite different to the 1.4 million you mention in your last post. I'm not sure how Access will cope with 14 million.

So which is it? 1.4 million or 14 million?

Chris

 

[aman]

Thanks Stopher. It worked fine.