DoCmd.OpenView error

[Shals]

Hi
I'm using Command

DoCmd.OpenView "dbo.vFDXQryBld gEdit3",acViewN ormal,acEdit

to open a view vFDXQryBldgEdit 3 (written in SQL Server) in the Edit
mode. This view is created on single table. but when I eecute this
command view is opened in Read only mode.
Am i missing something here or this command does not works in MS Access
..adp applications.

I'm using Access 2003 and SQL Server for Backend database.

Thanks for any suggestions anyone has.

[Tom van Stiphout]

On 14 Nov 2006 09:30:06 -0800, "Shals" <s_******@berke ley.eduwrote:

Perhaps there is no Primary Key on the underlying table?
-Tom.

>Hi
I'm using Command

DoCmd.OpenVi ew "dbo.vFDXQryBld gEdit3",acViewN ormal,acEdit

to open a view vFDXQryBldgEdit 3 (written in SQL Server) in the Edit
mode. This view is created on single table. but when I eecute this
command view is opened in Read only mode.
Am i missing something here or this command does not works in MS Access
.adp applications.

I'm using Access 2003 and SQL Server for Backend database.

Thanks for any suggestions anyone has.

[Shals]

there is a Primary Key on that table but still it doesn't work.
Tom van Stiphout wrote:

On 14 Nov 2006 09:30:06 -0800, "Shals" <s_******@berke ley.eduwrote:

Perhaps there is no Primary Key on the underlying table?
-Tom.

Hi
I'm using Command

DoCmd.OpenView "dbo.vFDXQryBld gEdit3",acViewN ormal,acEdit

to open a view vFDXQryBldgEdit 3 (written in SQL Server) in the Edit
mode. This view is created on single table. but when I eecute this
command view is opened in Read only mode.
Am i missing something here or this command does not works in MS Access
.adp applications.

I'm using Access 2003 and SQL Server for Backend database.

Thanks for any suggestions anyone has.

[Shals]

Any other Suggestions Please !!!
Shals wrote:

there is a Primary Key on that table but still it doesn't work.
Tom van Stiphout wrote:

On 14 Nov 2006 09:30:06 -0800, "Shals" <s_******@berke ley.eduwrote:

Perhaps there is no Primary Key on the underlying table?
-Tom.

>Hi
>I'm using Command
>
>DoCmd.OpenVi ew "dbo.vFDXQryBld gEdit3",acViewN ormal,acEdit
>
>to open a view vFDXQryBldgEdit 3 (written in SQL Server) in the Edit
>mode. This view is created on single table. but when I eecute this
>command view is opened in Read only mode.
>Am i missing something here or this command does not works in MS Access
>.adp applications.
>
>I'm using Access 2003 and SQL Server for Backend database.
>
>Thanks for any suggestions anyone has.

[Lyle Fairfield]

"Shals" <s_******@berke ley.eduwrote in
news:11******** *************@k 70g2000cwa.goog legroups.com:

Any other Suggestions Please !!!

Yes, this command works in an Access ADP.

Does the user have update etc permissions on the view/table?

Can you post a CREATE VIEW SQL string for the VIEW?

What happens with
DoCmd.OpenTable "TheUnderlyingT able", acViewNormal, acEdit
Is that editable?

Are you using Application Roles?
--
Lyle Fairfield

from http://msdn.microsoft.com/library/de...l=/library/en-
us/dnmdac/html/data_mdacroadma p.asp

Obsolete Data Access Technologies
Obsolete technologies are technologies that have not been enhanced or
updated in several product releases and that will be excluded from future
product releases. Do not use these technologies when you write new
applications. When you modify existing applications that are written using
these technologies, consider migrating those applications to ADO.NET.
The following components are considered obsolete:
....
Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and scripting languages. It was included with Microsoft Office 2000 and
Office XP. DAO 3.6 is the final version of this technology. It will not be
available on the 64-bit Windows operating system.
.....

[Shals]

Thanks for your input,

Here is the code for the view:

SELECT DISTINCT
TOP 100 PERCENT BuildingNumber, BuildingName,
YearConstructed , ConditionCode, DATEPART(yy, GETDATE()) -
YearConstructed AS age,
YearLatestImpro vement
FROM dbo.Buildings
WHERE (ConditionCode = '1') AND (DATEPART(yy, GETDATE()) -
YearConstructed 5)
ORDER BY BuildingNumber
Well I checked for Docmd.OpenTable on the Table Buildings it shows
editable datasheet and I can modify it but somehow its not showing
editable view on same table for which I have given the code.

I'm using Database roles not application roles. also I have given
permission for table as well as view for Insert, Update, Delete but no
luck in getting view editable.

please can you see if there is anything in the code. Thanks for all
your help

Lyle Fairfield wrote:

"Shals" <s_******@berke ley.eduwrote in
news:11******** *************@k 70g2000cwa.goog legroups.com:

Any other Suggestions Please !!!

Yes, this command works in an Access ADP.

Does the user have update etc permissions on the view/table?

Can you post a CREATE VIEW SQL string for the VIEW?

What happens with
DoCmd.OpenTable "TheUnderlyingT able", acViewNormal, acEdit
Is that editable?

Are you using Application Roles?
--
Lyle Fairfield

from http://msdn.microsoft.com/library/de...l=/library/en-
us/dnmdac/html/data_mdacroadma p.asp

Obsolete Data Access Technologies
Obsolete technologies are technologies that have not been enhanced or
updated in several product releases and that will be excluded from future
product releases. Do not use these technologies when you write new
applications. When you modify existing applications that are written using
these technologies, consider migrating those applications to ADO.NET.
The following components are considered obsolete:
...
Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and scripting languages. It was included with Microsoft Office 2000 and
Office XP. DAO 3.6 is the final version of this technology. It will not be
available on the 64-bit Windows operating system.
....

[Lyle Fairfield]

Shals wrote:

Here is the code for the view:

SELECT DISTINCT

TTBOMK Views whose definition contains DISTINCT are not updateable.

As you have indicated that the table has a primary index (on Building
Number?) DISTINCT should be unnecessary.

[Shals]

Thanks a lot Lyle,

Without your help I would have never got that error corrected.
its working now :-)

If you can help me in one more error. I would be very thankful to you.

the problem is I have a Main Form and Sub Form within that Main Form.
The subform is linked through master and Child Fields with the Main
Form.

The Main Form is getting data from Buildings Table which has Primary
Key BuildingNumber and sub form is getting data from another table
BldgFloor which has Primary Key BuildingNumber and FloorLevel.

now when I click a button to open the main form it opens correctly but
when I focus from subform back to main form it throws an error

"Subquery returned more than 1 value.This is not permitted when
subquery follows =,!=,<,<=,>,>= or when subquery is used as an
expression."

I tried recreating the Master and CHild linked fields, as well as I
tried recreating the form again but no luck same error persists.

The strange thing is I have same structure database with some other
name but some what different data records in it. when I connect my adp
application to that database it doesn't gives that error and then when
I reconnect it back to same database it gives the error.

I tried checking the foreign key constraint on the tables and verified
the data but it doesn't looks bad. Also this error is not coming while
debugging the application in VBA code but comes just after that.

I have no idea whats going on. If you can suggest some things I can try
that, please let me know.

Thanks again for all your help.
Lyle Fairfield wrote:

Shals wrote:

Here is the code for the view:

SELECT DISTINCT

TTBOMK Views whose definition contains DISTINCT are not updateable.

As you have indicated that the table has a primary index (on Building
Number?) DISTINCT should be unnecessary.

[Lyle Fairfield]

Shals wrote:

"Subquery returned more than 1 value.This is not permitted when
subquery follows =,!=,<,<=,>,>= or when subquery is used as an
expression."

I created this error:

ALTER PROCEDURE tmpProc AS
DECLARE @DivisionName varchar(255)
SELECT @DivisionName = (SELECT fldDivisionName FROM dbo.tblDivision s)
RETURN

There was no error with:

ALTER PROCEDURE tmpProc AS
DECLARE @DivisionName varchar(255)
SELECT @DivisionName = (SELECT fldDivisionName FROM dbo.tblDivision s
WHERE fldDivisionID = 33)
RETURN

As you can see, the subquery in the first SPROC will return more than
one row, and SQL-Server will not know from which row to get the value
of @DivisionName.

In the second SPROC only one row is returned and no error occurs.

Is it possible that in the SQL that surrounds your form or subform you
have such a SPROC. Is it possible that intially only one value is
returned but after you work in the subform two or more values are
returned?

[Shals]

Actually there are no Stored Procedure or Views or Trigger associated
with the Main form or Sub form they both are getting values from tables
directly attached through the RecordSources Property.

the only other field I'm calculating is the sum total of all the
records that are in the subform datasheet.
there is no other code attached with the forms.

Lyle Fairfield wrote:

Shals wrote:

"Subquery returned more than 1 value.This is not permitted when
subquery follows =,!=,<,<=,>,>= or when subquery is used as an
expression."

I created this error:

ALTER PROCEDURE tmpProc AS
DECLARE @DivisionName varchar(255)
SELECT @DivisionName = (SELECT fldDivisionName FROM dbo.tblDivision s)
RETURN

There was no error with:

ALTER PROCEDURE tmpProc AS
DECLARE @DivisionName varchar(255)
SELECT @DivisionName = (SELECT fldDivisionName FROM dbo.tblDivision s
WHERE fldDivisionID = 33)
RETURN

As you can see, the subquery in the first SPROC will return more than
one row, and SQL-Server will not know from which row to get the value
of @DivisionName.

In the second SPROC only one row is returned and no error occurs.

Is it possible that in the SQL that surrounds your form or subform you
have such a SPROC. Is it possible that intially only one value is
returned but after you work in the subform two or more values are
returned?