473,326 Members | 2,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

unique table property

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
2 9911
-----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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Filips Benoit | last post by:
Dear All, W2000 Office2000 Access adp SQLserver DB Problem: Adding a new property for a company in the subform. The FIRST time I Select a property in combobox CPROP_PRP_ID the subform act
6
by: Bob Stearns | last post by:
I was under the impression that the primary key had to be a unique index. Since I usually create my primary indices before my primary keys, in order to get the indices in the same schema as their...
3
by: Beat Scheidiger | last post by:
I do not quite understand this property. Everything is seems clear to me, when I read the corresponding help text. But in practice I have a question: Why are there 3 identical records in the...
5
by: Marie | last post by:
Access97 I have a table containing addresses with a separate field for State. Is there a way to create a query that returns an unique list of the states in that table and still be updateable? I...
0
by: Filips Benoit | last post by:
Dear All, W2000 Office2000 Access adp SQLserver DB Problem: Adding a new property for a company in the subform. The FIRST time I Select a property in combobox CPROP_PRP_ID the subform act
2
by: SJM | last post by:
I have a report that displays records of real estate properties. It is possible for each property to appear a number of times for various reasons. Each record however is unique. What I would like...
29
by: Lauren Wilson | last post by:
Does anyone know how the following info is extracted from the user's computer by a Front Page form? HTTP User Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.5) Gecko/20041107...
6
by: shira | last post by:
Hi, Looking to see if someone might have an explanation for this behavior. Is it a bug? Corruption? I have been able to reproduce the problem with only 2 rows and 1 field. Here is the table:...
2
Zwoker
by: Zwoker | last post by:
Greetings everyone, I have a problem that I hope has a simple solution. I am using MS Access 2003. I have a table that is a list of financial transactions. I am using a make table query over...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.