updatable ado-recordset

Hi,

In the code below i have an updatable ado-recordset. However it is not
working how i thought it would be.
Maybe someone can help me out here?

The global connection (Thanks to Brendan):

Option Compare Database
Option Explicit

Public mconn As ADODB.Connection

Public Function GetConnection(ByVal boolOpenIfClosed As Boolean) As
ADODB.Connection

If mconn Is Nothing Then
Set mconn = New ADODB.Connection
With mconn
.Provider = "MSDataShape"
.ConnectionString = "DATA PROVIDER=SQLOLEDB;DATA
SOURCE=local;DATABASE=test;UID=sa;PWD=;"
.CursorLocation = adUseServer
.Open
End With
End If
If mconn.State = adStateClosed Then
If boolOpenIfClosed Then
mconn.Open
End If
End If

Set GetConnection = mconn

End Function
----------------
The OnOpen-Event of a form:

Private Sub Form_Open(Cancel As Integer)

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

With rst
.Source = "SELECT * FROM Test"
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.ActiveConnection = GetConnection(True)
.Open
End With

Set Me.Recordset = rst

Set rst = Nothing

End Sub

Private Sub Form_Unload(Cancel As Integer)
Dim cnn As ADODB.Connection
Set cnn = Me.Recordset.ActiveConnection
cnn.Close
Set cnn = Nothing
End Sub

The table which i'm connecting has 4 columns with a primary key and a
trigger for the lastmodified column.

The error i'm getting is this:

"Key column information is insufficient or incorrect. Too many rows were
affected by update."

I believe the trigger caused this because upon inserting there are no
problems. Also the updated records are not directly visible on the form,
only if close and open it.

So what i would like is if the above mentioned codes could work, if not
please correct me.

  >> Private Sub Form_Open

There is no Open event in VB. Is this an Access userform?

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
<a rel="nofollow" style='text-decoration: none;' href="http://www.veign.com/vrc_main.asp" target="_blank">http://www.veign.com/vrc_main.asp</a>
--

"Jason" wrote in message

 > Hi,
 >
 > In the code below i have an updatable ado-recordset. However it is not
 > working how i thought it would be.
 > Maybe someone can help me out here?
 >
 > The global connection (Thanks to Brendan):
 >
 > Option Compare Database
 > Option Explicit
 >
 > Public mconn As ADODB.Connection
 >
 > Public Function GetConnection(ByVal boolOpenIfClosed As Boolean) As
 > ADODB.Connection
 >
 > If mconn Is Nothing Then
 > Set mconn = New ADODB.Connection
 > With mconn
 > .Provider = "MSDataShape"
 > .ConnectionString = "DATA PROVIDER=SQLOLEDB;DATA
 > SOURCE=local;DATABASE=test;UID=sa;PWD=;"
 > .CursorLocation = adUseServer
 > .Open
 > End With
 > End If
 > If mconn.State = adStateClosed Then
 > If boolOpenIfClosed Then
 > mconn.Open
 > End If
 > End If
 >
 > Set GetConnection = mconn
 >
 > End Function
 > ----------------
 > The OnOpen-Event of a form:
 >
 > Private Sub Form_Open(Cancel As Integer)
 >
 > Dim rst As ADODB.Recordset
 >
 > Set rst = New ADODB.Recordset
 >
 > With rst
 > .Source = "SELECT * FROM Test"
 > .LockType = adLockOptimistic
 > .CursorType = adOpenDynamic
 > .CursorLocation = adUseClient
 > .ActiveConnection = GetConnection(True)
 > .Open
 > End With
 >
 > Set Me.Recordset = rst
 >
 > Set rst = Nothing
 >
 > End Sub
 >
 > Private Sub Form_Unload(Cancel As Integer)
 > Dim cnn As ADODB.Connection
 > Set cnn = Me.Recordset.ActiveConnection
 > cnn.Close
 > Set cnn = Nothing
 > End Sub
 >
 > The table which i'm connecting has 4 columns with a primary key and a
 > trigger for the lastmodified column.
 >
 > The error i'm getting is this:
 >
 > "Key column information is insufficient or incorrect. Too many rows were
 > affected by update."
 >
 > I believe the trigger caused this because upon inserting there are no
 > problems. Also the updated records are not directly visible on the form,
 > only if close and open it.
 >
 > So what i would like is if the above mentioned codes could work, if not
 > please correct me.
 >
 >