J
Jason
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.
Thnx
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.
Thnx