DoCmd.OpenView error

Hi
I'm using Command

DoCmd.OpenView "dbo.vFDXQryBldgEdit3",acViewNormal,acEdit

to open a view vFDXQryBldgEdit3 (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.

re: DoCmd.OpenView error

On 14 Nov 2006 09:30:06 -0800, "Shals" <s_wadhwa@berkeley.eduwrote:

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

re: DoCmd.OpenView error

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

re: DoCmd.OpenView error

Any other Suggestions Please !!!
Shals wrote:

re: DoCmd.OpenView error

"Shals" <s_wadhwa@berkeley.eduwrote in
news:1163722278.485233.70980@k70g2000cwa.googlegro ups.com:
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 "TheUnderlyingTable", 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_mdacroadmap.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.
.....

re: DoCmd.OpenView error

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,
YearLatestImprovement
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:

re: DoCmd.OpenView error

Shals wrote: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.

re: DoCmd.OpenView error

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:

re: DoCmd.OpenView error

Shals wrote:
I created this error:

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

There was no error with:

ALTER PROCEDURE tmpProc AS
DECLARE @DivisionName varchar(255)
SELECT @DivisionName = (SELECT fldDivisionName FROM dbo.tblDivisions
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?

re: DoCmd.OpenView error

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:

re: DoCmd.OpenView error

Shals wrote:Well, !!!!perhaps!!!! maybe we can assume that Access uses a SPROC to
get the records for the sub form. But I can't account for Access.
A solution is likely to be to remove the linking fields from the sub
form and
in the current event code of the main form to do two things:
1. set the default value of the sub-form's building id to the main forms
building id
2. set the sub forms recordsource property to
"SELECT whatever fields from whatever table where BuilingID = " & Main
Form's Building ID

this is the only way I do sub forms in ADPs; I have more control this
way. Actually I don't make them sub forms, just forms that act like sub
forms.