when is table updateable / appendable?

  • Thread starter Thread starter Neil Jarman
  • Start date Start date
N

Neil Jarman

Hi,

I'm using Access as a front end to SQLserver tables.

When I have certain joined queries, I can't update or create new records,
even though the two joined tables are linked by a primary key.

I know in my mind that the data should be updateable, and can't see where
I'm going wrong.

I'm not sure what rules are being broken, but is ther a reliable way of
understanding what I can and can't do in these cases?

many thanks,

NEIL
 
Hi

From BOL:
"The DELETE, INSERT, and UPDATE statements can reference a view as long as
SQL Server can translate the user's update request unambiguously to updates
in the base tables referenced in the view's definition."

Generally, no SELECT * allowed, only once column with the same underlying
name can be returned (e.g. no table1.col1, table2, col1).

Post your DDL and DML so we can see what the limitation is.

Regards
Mike
 
If you are using Access with linked SQL Server tables, you are stuck with
the Access rules for when a query is updateable. You can find a list of
when a query may or may not be updateable in Access in Help. Open Help. In
the Answer Wizard tab type "when is a query updateable", then choose "When
can I update data from a query" in the listbox below.

Another problem to be aware of with Access hitting SQL Server tables is when
you have a Bit field in your SQL Server table without a default value.
This may not be your problem, but it's worth knowing about if you use Access
with linked SQL Server tables. Here's a link about it:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q278696


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Back
Top