Temporarily remove referential integrity checks through VBA?

[Andrew R]

Hi

I'm creating a series of forms, each with with around 15-20 text boxes.
The text boxes will show data from tables, but are unbound to make them
more flexible.

I want the form to be used for both adding new data and modifying
existing data. I have created a save button on the form.

When the user clicks the save button, the code checks to see if there
is a value in the ID text box. If not, it assumes that the record is
new and uses an "Insert into..." SQL statement as the ADODB command
text. This works fine.

However, if there is already an ID value present, the logic is that the
record already exists, and therefore we are doing an update. Creating a
SQL statement along the lines of "UPDATE tblCustomers SET strFirstName
= " & txtFirstName.te xt & ", strLastName=" & txtLastName.tex t
...... and so on seems to be a lot of coding to update perhaps 14 fields
which haven't changed in addition to the one that may have.

Therefore, my question is this: Is there an easy way to detect which
field has been changed (without writing code behind each text box,
given that Access doesn't allow control arrays... It probably seems
like I'm being lazy, but with probably 8 or so forms, each with 15-20
text boxes, that would be a lot of work!)

Assuming not, my other thought was that it would be much simpler to
simply execute 2 commands -
"Delete from tblCustomers where ID=" & txtID.text
and then run the insert statement as before. However, this will of
course be prevented by the referential integrity constraints on the
table, thus my question - is there a way through the code to turn off
checks, run the delete and insert statements, then turn them on again?

Apologies for being long-winded! Any help gratefully received!

Regards
Andrew Richards

[Bri]

Keith Wilby wrote:

"Bri" <no*@here.com > wrote in message news:rzhkg.3269 6$iF6.28271@pd7 tw2no...

Great idea, exept that OldValue only works with Bound controls which
implies Bound Form and this is an Unbound Form.

Hence the phrase "you may be able to adapt it".

Keith.

Well, you gave the function in response to the issue of dealing with
controls that had changed their value and your function uses OldValue to
do that... so, what is left to adapt?

--
Bri

[David W. Fenton]

"Jamie Collins" <ja**********@x smail.com> wrote in
news:11******** *************@h 76g2000cwa.goog legroups.com:

David W. Fenton wrote:

If the schema necessitates regularly turning off RI, then it's
wrong. I don't need to know anything else about it.
We're fortunate in Access/Jet that the engine is quite good at
resolving cascade paths. Much better, for example, than SQL Sever:
as soon as it encounters two paths it chokes i.e. the classic
adjacency list, a popular common/popular design choice for trees
in SQL, cannot support cascading updates in SQL Server.

Well, I think cascading updates are a bad thing, as an updatable PK
is a bad thing.

Secondly, every N:1 relationship can be replaced by an intermediate
join table. It's not quite as intuitive as your usual N:N join table
(it's a degenerate case of the N:N), but it works just the same.

The point is that there are ways to avoid the circular relationship
problem by redesigning your schema. It will still reflect the
entities being modelled. It just do it in a different fashion.
It would be a complex design in Access/Jet that would cause the
engine to choke and therefore there would be a lot of scope for
alternative design choices. The more complex the design the more
difficult it is to say for sure that the design is 'wrong' because
of the many design choices and compromises between logical model
and physical implementation which inevitably have been made along
the way.
Cascading updates indicates to me that somebody has gone badly wrong
in the first place.
Temporarily suspending DRI in Access/Jet is not desirable (e.g.
can I create a serialized transaction that would prevent others
making schema changes and allow me to rollback mine? I really
don't know) and I've never had to do it myself but I stop short of
saying that doing so is always wrong.

It's bloody stupid to have RI in place that has to be suspended in
order to insert data.

I also don't believe in cascading updates, since I am
philosophically completely opposed to using PKs that are ever
updated (I'm against natural keys in all but the most trivial
cases, such as lookup tables).

Some people choose to use natural keys in DRI. Are you saying they
are 'wrong'?

Yep. Any data that can be edited shouldn't be used as a PK, because
the function of the PK is to relate data, not to store information
about the entities represented in the table.

That doesn't mean that unique indexes shouldn't be maintained on the
natural key (which may be composite), but natural keys lead to just
this kind of problem.

The only exception I would make is for one-column lookup tables,
where there is no dependent data (i.e., the PK is the entire data
for the entity).

Sounds like an adjustment to the schema is needed. I've had
circular relationships in Jet databases (even replicated) and
have never had a problem. Perhaps one solution is changing the FK
to allow Null and not be required is all that's needed, since
I've never had a circular relationship with a required FK value
in the chain.

I've having trouble envisaging what you mean. Could you post your
schema and some test data, please.

I posted mine (or rather, that OP's) in the other thread. Perhaps
you could also post a 'fix' to that schema too?

I've explained at least three different ways to avoid circular
schema problems. None of them is complicated enough to need me to
draw you a picture.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Keith]

Bri wrote:

Well, you gave the function in response to the issue of dealing with
controls that had changed their value and your function uses OldValue to
do that... so, what is left to adapt?

You know, there will always be smart-arsed know-it-alls on these groups
who use their no doubt superior knowledge on a subject to try to
humiliate and score cheap points from other contributors. You fall into
this category. I always try to help other Access users where I can but
my contributions can sometimes be a bit off the mark and I'm happy and
grateful to accept corrections where necessary from those with better
knowledge, this is how I learn from my mistakes, but when those
corrections are offered in a holier-than-thou manner it really pisses me
off.

I am not so geek-like that I know about every method, every function,
every facet of Access. I am also sometimes guilty of reading posts too
quickly. In this instance, although I read the "unbound" part I failed
to digest it fully which is why I offered the code for adaptation and
not as a belt and braces solution. So I made a mistake, so what? I'm
sure the OP realised that without your cheap shots.

I read your exchange with David Fenton in the "Created on Access 2003,
but............ ..........." thread with interest. David is extremely
knowledgeable in Access but, IMO, his bedside manner can sometimes leave
a little to be desired, but it pales into insignificance compared to
yours. This is not something I would be proud of if I were you, which
thankfully I am not.

<PLONK>

[Bri]

Keith wrote:

Bri wrote:

Well, you gave the function in response to the issue of dealing with
controls that had changed their value and your function uses OldValue
to do that... so, what is left to adapt?

You know, there will always be smart-arsed know-it-alls on these groups
who use their no doubt superior knowledge on a subject to try to
humiliate and score cheap points from other contributors. You fall into
this category. I always try to help other Access users where I can but
my contributions can sometimes be a bit off the mark and I'm happy and
grateful to accept corrections where necessary from those with better
knowledge, this is how I learn from my mistakes, but when those
corrections are offered in a holier-than-thou manner it really pisses me
off.

I am not so geek-like that I know about every method, every function,
every facet of Access. I am also sometimes guilty of reading posts too
quickly. In this instance, although I read the "unbound" part I failed
to digest it fully which is why I offered the code for adaptation and
not as a belt and braces solution. So I made a mistake, so what? I'm
sure the OP realised that without your cheap shots.

I read your exchange with David Fenton in the "Created on Access 2003,
but............ ..........." thread with interest. David is extremely
knowledgeable in Access but, IMO, his bedside manner can sometimes leave
a little to be desired, but it pales into insignificance compared to
yours. This is not something I would be proud of if I were you, which
thankfully I am not.

<PLONK>

Whoa, now!! Back up the truck. I'm sorry if you feel that my reply was
an attempt to be a smart ass and to score cheap points at your expense.
I can assure you that that was not my intent. I really was trying to
point out that your solution wouldn't work and why it wouldn't for the
benefit of the OP primarily and for you as well. You then replied that
the OP was to adapt your code for his needs. I then responded that after
you remove the OldValue part of the function that there was nothing left
that could be used to solve his problem as the OldValue was the key part
of that function. I was wondering if, after the OldValue part of the
function was dismissed, what you thought was still there to adapt? It
was a question, in case I had missed something else in there. I'm sorry
you took offense to that. Perhaps I could have written it differently.

I'm not sure what there is about my responses that 'pales into
insignificance' . Perhaps, you would explain to me the error of my ways?
I certainly don't think of myself as hard to get along with, but perhaps
there is something in the way I write that comes off that way. I was
unaware of it. What could/should I have done differently?

In the exchange with David you refer to, he refused to acknowledge that
what I said I had done was true, he said I was a lier, and even when
faced with several sources of reference to back my story up, he still
refused to believe it. I can't see how you would place me as the
aggressor in that thread.

--
Bri

[Jamie Collins]

David W. Fenton wrote:

Some people choose to use natural keys in DRI. Are you saying they
are 'wrong'?

Yep.

OK, so you think ON UPDATE CASCADE is wrong, should never have been
invented and anyone who uses it is wrong. No offence intended but I
don't think you think you can ever be open minded to the legitimacy of
suspending DRI to manually perform a cascade which the engine is not
smart to figure out itself if you don't think the values should *ever*
be changed.

I am sure you are aware that many SQL gurus use natural keys for their
PKs so I won't try to convert you <g>.

Jamie.

--

[Keith]

Bri wrote:

Whoa, now!! Back up the truck. I'm sorry if you feel that my reply was
an attempt to be a smart ass and to score cheap points at your expense.
I can assure you that that was not my intent.
Fair enough. It did seem a tad aggressive to me, perhaps yesterday was
a "bad Day".
I really was trying to
point out that your solution wouldn't work and why it wouldn't for the
benefit of the OP primarily and for you as well. You then replied that
the OP was to adapt your code for his needs. I then responded that after
you remove the OldValue part of the function that there was nothing left
that could be used to solve his problem as the OldValue was the key part
of that function. I was wondering if, after the OldValue part of the
function was dismissed, what you thought was still there to adapt? It
was a question, in case I had missed something else in there. I'm sorry
you took offense to that. Perhaps I could have written it differently.
I think you could, I read that as being confrontational . As I said
before I read the OP very quickly and hadn't realised the significance
of the form being unbound and offered the code, warts and all, in case
the OP could have made use of it in one way or another. I just thought
that 'Great idea, except that ...' came across as sarcasm.

I'm not sure what there is about my responses that 'pales into
insignificance' . Perhaps, you would explain to me the error of my ways?
I certainly don't think of myself as hard to get along with, but perhaps
there is something in the way I write that comes off that way. I was
unaware of it. What could/should I have done differently?
I'm not going to attempt to preach to you how you should and should not
post on a public forum, I am in no position to do that. Having said that
I think that this is the first time I have ranted on here like I did
yesterday, perhaps I saw a red rag that wasn't really there. The
'pales' jibe was just my temper talking, please disregard it and accept
my apologies.

In the exchange with David you refer to, he refused to acknowledge that
what I said I had done was true, he said I was a lier, and even when
faced with several sources of reference to back my story up, he still
refused to believe it. I can't see how you would place me as the
aggressor in that thread.

It came across as two stags locking horns and I guess I thought you were
attempting a similar tack with me. You have stated that that was not
your intention and I believe you, I would not call you a liar or a lier
(yes I did spot that typo first time around). :-)

Have a good day, I'm hoping to have a better one than I did yesterday.

Regards,
Keith.

[David W. Fenton]

"Jamie Collins" <ja**********@x smail.com> wrote in
news:11******** *************@h 76g2000cwa.goog legroups.com:

I am sure you are aware that many SQL gurus use natural keys for
their PKs so I won't try to convert you <g>.

I think many SQL gurus are more concerned with theory than with ease
of use and maintenance.

One particular SQL guru strikes as a complete blowhard asshole.

As to natural PKs, well, they cause problems, and that's one of the
reasons why surrogate keys are better, because you avoid precisely
the kinds of problems encountered with the circular relationships.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bri]

Keith wrote:

Bri wrote:

Whoa, now!! Back up the truck. I'm sorry if you feel that my reply was
an attempt to be a smart ass and to score cheap points at your
expense. I can assure you that that was not my intent.
Fair enough. It did seem a tad aggressive to me, perhaps yesterday was
a "bad Day".

It happens, hope you're having a better day today.
I think you could, I read that as being confrontational . As I said
before I read the OP very quickly and hadn't realized the significance
of the form being unbound and offered the code, warts and all, in case
the OP could have made use of it in one way or another. I just thought
that 'Great idea, except that ...' came across as sarcasm.
Actually, it was sincere. I thought it was great code for an audit
trail. In fact, I mentioned that I used something similar myself.
I'm not going to attempt to preach to you how you should and should not
post on a public forum, I am in no position to do that. Having said that
I think that this is the first time I have ranted on here like I did
yesterday, perhaps I saw a red rag that wasn't really there. The
'pales' jibe was just my temper talking, please disregard it and accept
my apologies.
Apology accepted.

By pointing out where you saw sarcasm, you did what I was asking. Now I
have a reference for where something I wrote was taken differently than
intended, I can watch for it in the future.
It came across as two stags locking horns and I guess I thought you were
attempting a similar tack with me. You have stated that that was not
your intention and I believe you, I would not call you a liar or a lier
(yes I did spot that typo first time around). :-)
Well, I'm not going to let someone call me a liar (not a typo, spelling
is not my strongest suit and the spell checker didn't catch it) and not
try to defend myself. If you followed the whole thread, you would have
seen that the force of my writing escalated with each reply where he
refused to even check the references I supplied. I didn't start out
writing to him that way (or at least I didn't intend to).
Have a good day, I'm hoping to have a better one than I did yesterday.

Regards,
Keith.

Having a great day, thanks. And to you.

--
Bri

[Jamie Collins]

David W. Fenton wrote:

I think many SQL gurus are more concerned with theory than with ease
of use and maintenance.

One particular SQL guru strikes as a complete blowhard asshole.

Can you imagine Fabian posting to this thread, 'I don't see any
situations where DRI should be disabled,' and only after interrogation
qualified with, 'because I consider SQL to be a bad thing'?

Does this sound like a certain someone's involvement in this thread,
David <g>?

Jamie.

--

[rkc]

Jamie Collins wrote:

David W. Fenton wrote:

I think many SQL gurus are more concerned with theory than with ease
of use and maintenance.

One particular SQL guru strikes as a complete blowhard asshole.

Can you imagine Fabian posting to this thread, 'I don't see any
situations where DRI should be disabled,' and only after interrogation
qualified with, 'because I consider SQL to be a bad thing'?

Does this sound like a certain someone's involvement in this thread,
David <g>?

What does SQL have to do with referential integrity?