Can't edit view

  • Thread starter Thread starter Jake Campbell
  • Start date Start date
J

Jake Campbell

I hope this is the right group. Using Access 2000, SQL Server 2000.

I have a view with 2 tables with an inner join. I would like it to be the
recordsource for a form. Both tables have timestamps. It appears that
Access will not allow me to edit data in this view.

I can change/add/delete rows using SQL Server utilities e.g. Enterprise
Manager, Query Analyzer, but from within Access the view appears to be
read-only.

Is there a work-around?

TIA, Jake
 
Hi Jake,

I know when editing on a stored procedure that is bound that has joins you
must assign the unique table property for that form.

It can't do edits on both tables (that are joined) within the same form.

While I never used a view, I would probably gather it would have the same
limitation.

So if the two tables contain a information that can be editable (in each
table). you will either:
1) not use binding principles on the form and deal with it as if it is
unbound doing all the manual saving to the database.
2) Have a main form with a subform that will allow you to split the data
apart into two separate forms.

OR if it is only one table has the editable information and the other tables
just has static information (as only being used to display not edit) then you
can set the forms:

UniqueTable Property equal to the table that is editable.

ie form1.UniqueTable="tMyTable"
or set it in the Designer under the tab "Data" for that form.

I hope this helps,

Angela
 
The first thing I will do in your case will be to replace Access 2000 with
A2003. There are so many bugs with the 2000 version of ADP that probably
you won't be able to achieve your goal. The 2002 and 2003 are also ridden
with bugs but in much lower quantities.

For the rest, I don't know. With ADP, I've taken many years ago the good
habit to go with the simplest design as possible: use stored procedures
only, no views, no bit fields, no timestamp and no fancy stuff.

S. L.
 
If you make an access 2000 adp form that is bound to data on the sql server
you will have to ensure the following:

In form design view, data tab, you will have to set the "recordset type" to
"Updateable Snapshot".

In form design view, data tab, you may also have to set "Unique Table" to
the one table that you are going to update. In many cases you cannot update
both of two joined tables. These parameters can also be set in code on the
form open event, or when the user clicks into a control:
Me.formname.UniqueTable = <tablename>

If you want full flexibility, you will have to write a bit of VBA code in
your forms and possibly some stored procedures on the sql server.

I have used Access 2000 ADP and sql Server 2000 for years. They form a
perfect toolset when you want something up and running quickly.

Regards

Tore G.
 
Back
Top