Cannot insert into views in Access

[Microsoft]

Can someone tell me why I might be unable to insert into a view in Access
2000 against SQL Server 7? I have created a view for a subform with an
order by clause so I can get the records created the way I want it (this is
to get around the bug Microsoft acknowledges with inserting in subforms
using SQL as the datasource for the form). If I look at the view in
Enterprise manager, I see that I have a new record at the bottom of the
resultset. However, if I open this view in Access, I cannot insert any
records. Also, the subform won't let me go to the new record. In case
anybody asks, I am logging into the SQL Server with the same account through
both tools, so it's not a permission problem.
Thanks,
Lance

 

[SFAxess]

If there is a join in your view, you may need to set the
Unique Table property in your subform.

 

[Microsoft]

Nope, the is simply a select form underlying table with an additional order
by clause.

 

[SFAxess]

Have you tried putting your table in as the Unique Table?
It wouldn't hurt to test it.

How do you have the SELECT statement saved? Is it just
SQL in the recordsource field? Is it saved as a stored
procedure object? Are you setting the recordsource
property from code? I was a little thrown, because Views
can't have an ORDER BY clause. Just wanted to check.

Also, I assume you checked the form property to allow
additions.

 

[Microsoft]

The datasource property on the subform is the name of the view; as using SQL
in the datasource is what causes the problem. I do have an ORDER BY in the
view, as you can build it in SQL Server Enterprise (the SQL Server engine
understands the ORDER BY); it just seems like Access doesn't like it. Could
this be the problem?

 

[SFAxess]

I think it may very well be the problem.
I suggest that you create a stored procedure in place of
the view (and give it the same name if it won't affect
your naming convention). Then try your form again.
i.e.

Create Procedure "spYourSproc"
As
SELECT * FROM tblYourTable ORDER BY fldYourField