Form based upon ADODB-recordset not updatable

All

According http://support.microsoft.com/kb/281998/en-us I would be able to create an updateable form based upon an ADODB-recordset.

However, I did a copy paste the code and changed some parameters. I do get the form with the expected records, but I cannot change them nor add records. The account I use does have write-access, the form's update-property is true, the table got a primary key,...

The Microsoft article uses the "User ID" and "Password"-values, but I need to use "Integrated Security".

I use MS Access 2007 as interface, the database is stored on MS SQL Server.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").value = "SQLOLEDB"
.Properties("Data Source").value = strSQLServer
.Properties("Initial Catalog").value = strSQLDatabase
.Properties("Integrated Security").value = "SSPI"
.Open
End With
strSQL = "select * from dbo.tblReportType"
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = strSQL
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End If

Click to expand...

I am able to do batch-updates via next code and I tried to assign me.recordset=rscap (but this results in an error message)

Set cnCap = New ADODB.Connection
With cnCap
.Provider = "SQLOLEDB"
.Properties("Data Source").value = strSQLServer
.Properties("Initial Catalog").value = strSQLDatabase
.Properties("Integrated Security").value = "SSPI"
.Open
End With
strSQL = "select * from dbo.tblReportType"
Set rsCap = New ADODB.Recordset
rsCap.Open strSQL, cnCap, adOpenDynamic, adLockOptimistic
rsCap.MoveFirst
rsCap.Edit
rsCap!Value = "newvalue"
rsCap.Update

Click to expand...

Make sure that the SQL Table has a primary key defined.

And you might use this connection string instead:

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;
(from here: http://www.connectionstrings.com/sql-server-2008)

This also results in "The connection cannot be used to perform this operation. It is either closed or invalid in this context"

The form contains the ID-field which is also the primary key.


I created my form as follows:

• First I created a link between MS-Access and the MySQL table so the table became visible in the Access Table list at the left side
• Next, I used the Form Wizard to create a form based upon this table
• When I open this form, I am able to modify the values. However, I am not allowed to work with such linked tables
• So, I removed the "Record Source" in the form properties and created the code.
• The code provided by Microsoft does display the records, but I can't change them anymore (status bar displays: "This recordset is not updatable")
• All other connection strings I tried give me error message "The connection cannot be used to perform this operation. It is either closed or invalid in this context"

I found the error. Although I was sure the table got a primary key, it seems it did not. I was confused and thought "Identity Column" containted the value of the primary key. I just set the primary key via the correct way and I was able to edit the data in Access. Issue is solved.