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

[Lyle Fairfield]

Lyle Fairfield wrote:

db*******@hotma il.com wrote:

I am much better at Access AND Sql than anyone i've
ever seen on this newsgroup.

We're all grateful that you have shared your expertise here in CDMA.
But some of us may have missed one or two of your better contributions.
Would you list the links to a few, say ten, of your creative and
original posts so that we can reference them as a small library of
excellence?

In addition, this may help us to remember to maintain the appropriate
level of deferential integrity in our discussions with you.

Better yet why not create a compendium of these wonderful ideas in an
SQL-Server Table with code and descriptions in Text fields? You could
then create and make freely available an ADP which would allow us all
to access your wonderful ideas. I'm sure, that you, as El Supremo
ADP/SQL can craft your application in such a way that there will be no
security breaches or concerns.
In that way you can not only show, but also demonstrate the magnificent
qualities of the ADP. What an opportunity!

[dbahooker]

Terry

fuck yourself

Terry Kreft wrote:

Ha, ha, ha, it's just marvellous how you have not the slightest piece of
self-respect.

Please continue to show us how wonderful you are<g>; I've quite given up
reading Dilbert as your contributions are so much more originally funny.

You might not know much about programming (except in your world of course)
but you certainly know how to amuse!

I've only seen one piece of code from you and it was so laughably wrong but
far too subtle I thought, most people probably thought you meant it to be
like that.
--

Terry Kreft
<db*******@hotm ail.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

Terry;

screw yourself; I am much better at Access AND Sql than anyone i've
ever seen on this newsgroup.

And most importantly; I dont just blindly use the first tool the comes
across my desk.

I use the best tool for whatever I am doing.

Most of your idiots are unnecessarily biased agasint Access Data
Projects.
I find that laughable.
-Aaron
Terry Kreft wrote:

I love the way that you have no compunction about showing how ignorant you are of both transact SQL and Access SQL and the way that you're not afraid of displaying your stupidity in public is really marvelous.
--

Terry Kreft
<aa*********@gm ail.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .
> good stuff
>
>
> IF EXISTS (SELECT TOP 1 'X' FROM newsgroup WHERE tool_of_choice =
> 'MDB')
> BEGIN
> RAISERROR ('oh wait a second mdb can't handle real tsql
> statements; it is for babies', 16, 127, 'horsecrap')
> END
>
>
>
>
>
>
> Terry Kreft wrote:
> > IF EXISTS (SELECT * FROM newsgroup WHERE user_name = 'aaron')
> > BEGIN
> > DELETE FROM newsgroup WHERE user_name = 'aaron'
> > RAISERROR ('Troll alert - %d talking garbage', 16, 127, 'aaron')
> > END
> >
> >
> > --
> >
> > Terry Kreft
> >
> >
> > <aa*********@gm ail.com> wrote in message
> > news:11******** *************@u 72g2000cwu.goog legroups.com...
> > > fucking retards
> > >
> > > SQL Server is more powerful
> > > anyone that uses MDB in the year 2006?
> > >
> > > you should spit on them.
> > > out of the blue; just walk up to them and spit.
> > >
> > > you can have constraints-- you can have triggers-- you can have custom > > > dataTypes
> > >
> > >
> > >
> > >
> > > rkc wrote:
> > > > Jamie Collins wrote:
> > > > > rkc wrote:
> > > > >
> > > > >>What does SQL have to do with referential integrity?
> > > > >
> > > > >
> > > > > http://en.wikipedia.org/wiki/SQL
> > > > >
> > > > > See the 'Concepts' section.
> > > >
> > > > I didn't see any mention of SQL in the Referential Integrity
> > > > entry under that topic.
> > >
>

[dbahooker]

for the record

sql server security is a LOT better than your silly MDB bullshit.

can you kids even prevent me from updating a single table?

I'm sorry that you idiots try to TALK_SHIT about people just because
they're willing to think OUTSIDE THE BOX.

Just because I dont have to compact and repair my database every 3
days?? that makes me less of a man?

Because I dont spend half my time re-syncing Tables and Queries.. is
that why you call me a wimp?

I use a platform that runs circles around you kids.

Billions of records with sub-second response times.
Don't sit around and talk shit just because your obsolete MDB bullshit
can't scale to a million records.



Lyle Fairfield wrote:

Lyle Fairfield wrote:

db*******@hotma il.com wrote:

I am much better at Access AND Sql than anyone i've
ever seen on this newsgroup.

We're all grateful that you have shared your expertise here in CDMA.
But some of us may have missed one or two of your better contributions.
Would you list the links to a few, say ten, of your creative and
original posts so that we can reference them as a small library of
excellence?

In addition, this may help us to remember to maintain the appropriate
level of deferential integrity in our discussions with you.

Better yet why not create a compendium of these wonderful ideas in an
SQL-Server Table with code and descriptions in Text fields? You could
then create and make freely available an ADP which would allow us all
to access your wonderful ideas. I'm sure, that you, as El Supremo
ADP/SQL can craft your application in such a way that there will be no
security breaches or concerns.
In that way you can not only show, but also demonstrate the magnificent
qualities of the ADP. What an opportunity!

[Keith Wilby]

"Andrew R" <an************ *@cashette.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

How a simple question has exploded!

Information AND entertainment - don't you just love newsgroups?!

:-)
Andrew

You should be around when "PC Datasheet" is here. Actually, both
trouble-making parties should really be ignored but sometimes some of us
give in to temptation too easily ;-)

Keith.

[dbahooker]

no self respect?

at least I dont use a PUSSY database engine

uh compact and repair?
in the year 2006?

GAG

grow some balls kids and stop fighting for the pink team


Terry Kreft wrote:

Ha, ha, ha, it's just marvellous how you have not the slightest piece of
self-respect.

Please continue to show us how wonderful you are<g>; I've quite given up
reading Dilbert as your contributions are so much more originally funny.

You might not know much about programming (except in your world of course)
but you certainly know how to amuse!

I've only seen one piece of code from you and it was so laughably wrong but
far too subtle I thought, most people probably thought you meant it to be
like that.
--

Terry Kreft
<db*******@hotm ail.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

Terry;

screw yourself; I am much better at Access AND Sql than anyone i've
ever seen on this newsgroup.

And most importantly; I dont just blindly use the first tool the comes
across my desk.

I use the best tool for whatever I am doing.

Most of your idiots are unnecessarily biased agasint Access Data
Projects.
I find that laughable.
-Aaron
Terry Kreft wrote:

I love the way that you have no compunction about showing how ignorant you are of both transact SQL and Access SQL and the way that you're not afraid of displaying your stupidity in public is really marvelous.
--

Terry Kreft
<aa*********@gm ail.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .
> good stuff
>
>
> IF EXISTS (SELECT TOP 1 'X' FROM newsgroup WHERE tool_of_choice =
> 'MDB')
> BEGIN
> RAISERROR ('oh wait a second mdb can't handle real tsql
> statements; it is for babies', 16, 127, 'horsecrap')
> END
>
>
>
>
>
>
> Terry Kreft wrote:
> > IF EXISTS (SELECT * FROM newsgroup WHERE user_name = 'aaron')
> > BEGIN
> > DELETE FROM newsgroup WHERE user_name = 'aaron'
> > RAISERROR ('Troll alert - %d talking garbage', 16, 127, 'aaron')
> > END
> >
> >
> > --
> >
> > Terry Kreft
> >
> >
> > <aa*********@gm ail.com> wrote in message
> > news:11******** *************@u 72g2000cwu.goog legroups.com...
> > > fucking retards
> > >
> > > SQL Server is more powerful
> > > anyone that uses MDB in the year 2006?
> > >
> > > you should spit on them.
> > > out of the blue; just walk up to them and spit.
> > >
> > > you can have constraints-- you can have triggers-- you can have custom > > > dataTypes
> > >
> > >
> > >
> > >
> > > rkc wrote:
> > > > Jamie Collins wrote:
> > > > > rkc wrote:
> > > > >
> > > > >>What does SQL have to do with referential integrity?
> > > > >
> > > > >
> > > > > http://en.wikipedia.org/wiki/SQL
> > > > >
> > > > > See the 'Concepts' section.
> > > >
> > > > I didn't see any mention of SQL in the Referential Integrity
> > > > entry under that topic.
> > >
>

[Terry Kreft]

LOL, your wit almost exceeds your demonstrated technical ability.

With considered demonstrable arguments such as this I don't see how anyone
could possibly disagree.

--

Terry Kreft
<db*******@hotm ail.com> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. .

Terry

fuck yourself

Terry Kreft wrote:

Ha, ha, ha, it's just marvellous how you have not the slightest piece of self-respect.

Please continue to show us how wonderful you are<g>; I've quite given up reading Dilbert as your contributions are so much more originally funny.

You might not know much about programming (except in your world of course) but you certainly know how to amuse!

I've only seen one piece of code from you and it was so laughably wrong but far too subtle I thought, most people probably thought you meant it to be like that.
--

Terry Kreft
<db*******@hotm ail.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

Terry;

screw yourself; I am much better at Access AND Sql than anyone i've
ever seen on this newsgroup.

And most importantly; I dont just blindly use the first tool the comes
across my desk.

I use the best tool for whatever I am doing.

Most of your idiots are unnecessarily biased agasint Access Data
Projects.
I find that laughable.
-Aaron
Terry Kreft wrote:
> I love the way that you have no compunction about showing how ignorant

you

> are of both transact SQL and Access SQL and the way that you're not

afraid

> of displaying your stupidity in public is really marvelous.
>
>
> --
>
> Terry Kreft
>
>
> <aa*********@gm ail.com> wrote in message
> news:11******** **************@ p79g2000cwp.goo glegroups.com.. .
> > good stuff
> >
> >
> > IF EXISTS (SELECT TOP 1 'X' FROM newsgroup WHERE tool_of_choice =
> > 'MDB')
> > BEGIN
> > RAISERROR ('oh wait a second mdb can't handle real tsql
> > statements; it is for babies', 16, 127, 'horsecrap')
> > END
> >
> >
> >
> >
> >
> >
> > Terry Kreft wrote:
> > > IF EXISTS (SELECT * FROM newsgroup WHERE user_name = 'aaron')
> > > BEGIN
> > > DELETE FROM newsgroup WHERE user_name = 'aaron'
> > > RAISERROR ('Troll alert - %d talking garbage', 16, 127,

'aaron') > > > END
> > >
> > >
> > > --
> > >
> > > Terry Kreft
> > >
> > >
> > > <aa*********@gm ail.com> wrote in message
> > > news:11******** *************@u 72g2000cwu.goog legroups.com...
> > > > fucking retards
> > > >
> > > > SQL Server is more powerful
> > > > anyone that uses MDB in the year 2006?
> > > >
> > > > you should spit on them.
> > > > out of the blue; just walk up to them and spit.
> > > >
> > > > you can have constraints-- you can have triggers-- you can

have custom

> > > > dataTypes
> > > >
> > > >
> > > >
> > > >
> > > > rkc wrote:
> > > > > Jamie Collins wrote:
> > > > > > rkc wrote:
> > > > > >
> > > > > >>What does SQL have to do with referential integrity?
> > > > > >
> > > > > >
> > > > > > http://en.wikipedia.org/wiki/SQL
> > > > > >
> > > > > > See the 'Concepts' section.
> > > > >
> > > > > I didn't see any mention of SQL in the Referential Integrity
> > > > > entry under that topic.
> > > >
> >

[Lyle Fairfield]

db*******@hotma il.com wrote:

for the record

sql server security is a LOT better than your silly MDB bullshit.

can you kids even prevent me from updating a single table?

I'm sorry that you idiots try to TALK_SHIT about people just because
they're willing to think OUTSIDE THE BOX.

Just because I dont have to compact and repair my database every 3
days?? that makes me less of a man?

Because I dont spend half my time re-syncing Tables and Queries.. is
that why you call me a wimp?

I use a platform that runs circles around you kids.

Billions of records with sub-second response times.
Don't sit around and talk shit just because your obsolete MDB bullshit
can't scale to a million records.

Is that a No? You mean you won't share your expertise? What a shame!

[Terry Kreft]

I think you've got a spurious ? in there on the first line, wasn't that
meant to be a statement?

To be honest I'm not sure which database engine you do use as you've done
such a good job of obfuscating your technical ability, I know which one you
claim, but then we can all claim anything we want, can't we.

--

Terry Kreft
<db*******@hotm ail.com> wrote in message
news:11******** **************@ y41g2000cwy.goo glegroups.com.. .

no self respect?

at least I dont use a PUSSY database engine

uh compact and repair?
in the year 2006?

GAG

grow some balls kids and stop fighting for the pink team


Terry Kreft wrote:

Ha, ha, ha, it's just marvellous how you have not the slightest piece of self-respect.

Please continue to show us how wonderful you are<g>; I've quite given up reading Dilbert as your contributions are so much more originally funny.

You might not know much about programming (except in your world of course) but you certainly know how to amuse!

I've only seen one piece of code from you and it was so laughably wrong but far too subtle I thought, most people probably thought you meant it to be like that.
--

Terry Kreft
<db*******@hotm ail.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

Terry;

screw yourself; I am much better at Access AND Sql than anyone i've
ever seen on this newsgroup.

And most importantly; I dont just blindly use the first tool the comes
across my desk.

I use the best tool for whatever I am doing.

Most of your idiots are unnecessarily biased agasint Access Data
Projects.
I find that laughable.
-Aaron
Terry Kreft wrote:
> I love the way that you have no compunction about showing how ignorant

you

> are of both transact SQL and Access SQL and the way that you're not

afraid

> of displaying your stupidity in public is really marvelous.
>
>
> --
>
> Terry Kreft
>
>
> <aa*********@gm ail.com> wrote in message
> news:11******** **************@ p79g2000cwp.goo glegroups.com.. .
> > good stuff
> >
> >
> > IF EXISTS (SELECT TOP 1 'X' FROM newsgroup WHERE tool_of_choice =
> > 'MDB')
> > BEGIN
> > RAISERROR ('oh wait a second mdb can't handle real tsql
> > statements; it is for babies', 16, 127, 'horsecrap')
> > END
> >
> >
> >
> >
> >
> >
> > Terry Kreft wrote:
> > > IF EXISTS (SELECT * FROM newsgroup WHERE user_name = 'aaron')
> > > BEGIN
> > > DELETE FROM newsgroup WHERE user_name = 'aaron'
> > > RAISERROR ('Troll alert - %d talking garbage', 16, 127,

'aaron') > > > END
> > >
> > >
> > > --
> > >
> > > Terry Kreft
> > >
> > >
> > > <aa*********@gm ail.com> wrote in message
> > > news:11******** *************@u 72g2000cwu.goog legroups.com...
> > > > fucking retards
> > > >
> > > > SQL Server is more powerful
> > > > anyone that uses MDB in the year 2006?
> > > >
> > > > you should spit on them.
> > > > out of the blue; just walk up to them and spit.
> > > >
> > > > you can have constraints-- you can have triggers-- you can

have custom

> > > > dataTypes
> > > >
> > > >
> > > >
> > > >
> > > > rkc wrote:
> > > > > Jamie Collins wrote:
> > > > > > rkc wrote:
> > > > > >
> > > > > >>What does SQL have to do with referential integrity?
> > > > > >
> > > > > >
> > > > > > http://en.wikipedia.org/wiki/SQL
> > > > > >
> > > > > > See the 'Concepts' section.
> > > > >
> > > > > I didn't see any mention of SQL in the Referential Integrity
> > > > > entry under that topic.
> > > >
> >

[Terry Kreft]

Keith,
Didn't your mother ever warn you not to invoke Trolls by name ?

<g>

--

Terry Kreft
"Keith Wilby" <he**@there.com > wrote in message
news:44******** **@glkas0286.gr eenlnk.net...

"Andrew R" <an************ *@cashette.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.com.. .

How a simple question has exploded!

Information AND entertainment - don't you just love newsgroups?!

:-)
Andrew

You should be around when "XX XATASHEET" is here. Actually, both
trouble-making parties should really be ignored but sometimes some of us
give in to temptation too easily ;-)

Keith.

[Keith Wilby]

"Terry Kreft" <te*********@mp s.co.uk> wrote in message
news:eM******** ******@TK2MSFTN GP02.phx.gbl...

Keith,
Didn't your mother ever warn you not to invoke Trolls by name ?

<g>

My mistake m'lud. :-) It has been awfully quiet on that front and I quite
like it.