By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

unique table property

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need

...

'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Me.UniqueTable = "<table name>"
Set rs = Nothing

... etc. ...

Where <table name> is the name of the table in the View you are going
to update.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDPBKoechKqOuFEgEQK2gwCeO52UE8Od7Qe0ECVpmfbluw zB91EAn2m1
jVDDxLKipSmR5Gx9Z8H5b/Ms
=MkT5
-----END PGP SIGNATURE-----
Bill Holmes wrote:
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?

< SNIP >
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

Nov 12 '05 #2

P: n/a
Many thanks....
MGFoster <me@privacy.com> wrote in message news:<ti*****************@newsread1.news.pas.earth link.net>...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need

...

'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Me.UniqueTable = "<table name>"
Set rs = Nothing

... etc. ...

Where <table name> is the name of the table in the View you are going
to update.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDPBKoechKqOuFEgEQK2gwCeO52UE8Od7Qe0ECVpmfbluw zB91EAn2m1
jVDDxLKipSmR5Gx9Z8H5b/Ms
=MkT5
-----END PGP SIGNATURE-----
Bill Holmes wrote:
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?


< SNIP >
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.