I have an A2003 front end (mdb) using ado to link the form/subforms to
SQL server 2k tables and views. In SQL server, there is a view
between 2 tables with a 1-1 relationship. I can edit records from
this view in SQL server. However, in access, I have a form/subform
that displays the records but when I try to edit I get the message
"Form is read-only, because the unique table property is not set." If
I remove one of the tables from the SQL view (so just 1 table remains)
it works fine from access both read and write.
Is it possible set the unique field in the ado script? or is there
another method to allow editing of multiple table view?
Using standard code to bind the Access Form to ADO Recordset - MS
Knowledge Base Article: 281998
Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
'Create a new ADO Connection object
Set cn = New ADODB.Connection
'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "MySQLServer"
.Properties("User ID").Value = "sa"
.Properties("Password").Value = ""
.Properties("Initial Catalog").Value = "NorthwindCS"
.Open
End With
'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM Customers"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End Sub