Do recordsets behave differently under different connections?

[generallyconfused]

Hi all,

Try this out for me please.... I have 2 databases. The first, db1, only contains a form with a command button and some code under it (The code is below). The second, db2, has one table. This table is kinda weird however in that it is related to itself. This table stores part and assembly serial numbers. Some assemblies own other assemblies while some own parts etc. The table looks like this....

Table1:
ID <-Primary Key, Number
Parent_ID <-Foreign Key to Table1![ID], Number
Serial_Number <-The data, Text

The form in db1 has the following code under a command button. This code renumbers the IDs....

Private Sub Command0_Click()
Dim conn As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim num As Integer
conn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\db2.mdb;&quot;
rs1.Open &quot;Table1&quot;, conn, adOpenKeyset, adLockOptimistic
rs1.MoveLast
num = rs1.Fields(0).Value
rs1.MoveFirst
Do While Not rs1.EOF
rs1.Fields(0).Value = num + 1
num = num + 1
rs1.Update
rs1.MoveNext
Loop
rs1.Close
conn.Close
Set rs1 = Nothing
Set conn = Nothing
End Sub

Now if you build this and run this code you will receive...

Run-time error '-2147217887 (80040e21)':
Could not update; currently locked.

However if you run this exact same code inside of db2 (ie export the form from db1 into db2) the code works perfect.

My question is why? Why does the recordset think the record is locked when it accesses it through a connection to another file? Yet the same code accessing the mdb it is located inside, will work fine. It is like the recordset is locking itself somehow.

Please help.

 

[cmmrfrds]

This is taking the defaults on all the connection properties except the 2 listed.
conn.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\db2.mdb;&quot;
Do you have work group security on db2?

Change the following to static since the Access OLE-DB provider will force it to static anyway.
rs1.Open &quot;Table1&quot;, conn, adOpenKeyset, adLockOptimistic
rs1.Open &quot;Table1&quot;, conn, adOpenStatic, adLockOptimistic