Key Violations - This might help someone

[MVP - WannaB]

Hello, I've had my own problems with update queries and "Key Violations" and
thought that what I used the last time I ran an update query that kept giving
me "Key Violations" might be usfull to someone else.
First none of this is my own, I've learned it all here. It's just the way
in which I used what I learned here that might be helpful to someone else.
When you simply run an update or an append query and get that OH SO
descriptive MS message that tell you there are violation of one sort of
another, this may help because it provide more information.
Create a Module, Paste this VBA code in, replace <ENTER QUERY NAME HERE>
with your query name, and step through the procedure with F8.

Public Sub QryTest()

Dim StrSql As String
Dim dbs As DAO.Database

Set dbs = CurrentDb()

StrSql = "<ENTER QUERY NAME HERE>"
dbs.Execute StrSql, dbFailOnError

Set dbs = Nothing
End Sub

 

[a a r o n . k e m p f]

I assume that your problem with Key Violations is because you cannot
enforce referential integrity.
RI is a critical piece of reliable database systems-- and you throw
this out the window when you start using Linked Tables in Access.

Linked Tables in Access really don't support Referential Integrity.
Only by moving to Access Data Projects and SQL Server-- will you be
able to be confident in your data.

-Aaron

 

[Tony Toews [MVP]]

RI is a critical piece of reliable database systems--
Correct.

and you throw
this out the window when you start using Linked Tables in Access.

Linked Tables in Access really don't support Referential Integrity.
Only by moving to Access Data Projects and SQL Server-- will you be
able to be confident in your data.

The above statements are wrong. If referential integrity is setup
properly in the BE then the FE will respect that. Which is the same
as how it works for SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/