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

[Jamie Collins]

rkc wrote:

Referential integrity is not an SQL concept.
It's a relational database design concept.

You got it in the end!

To relate back your original enquiry, 'What does SQL have to do with
referential integrity?' If you want declarative referential integrity,
SQL's got it (Jet SQL's got it so I guess you can say Access's got it
too).

HTH,
Jamie.

--

[aaron.kempf]

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.

[Arno R]

Don't trolls get bored of themselves ??
Not even sometimes ??

You are a horsecrap baby indeed ...

Arno R
<aa*********@gm ail.com> schreef in bericht 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.
>

[aaron.kempf]

no i dont get tired.

you pansies just look funny dancing around with your pink mdb files

oh; look at the little babies playing with MDB.. how CUTE!!!

-Aaron
Arno R wrote:

Don't trolls get bored of themselves ??
Not even sometimes ??

You are a horsecrap baby indeed ...

Arno R
<aa*********@gm ail.com> schreef in bericht 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]

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]

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:

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.

[Terry Kreft]

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.
> >

[Keith Wilby]

<aa*********@gm ail.com> wrote in message
news:11******** **************@ h76g2000cwa.goo glegroups.com.. .

no i dont get tired.

I'm sure that one day we'll read a news item about you that concludes "...
before turning the gun on himself."

[Andrew R]

How a simple question has exploded!

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

:-)
Andrew

Keith Wilby wrote:

<aa*********@gm ail.com> wrote in message
news:11******** **************@ h76g2000cwa.goo glegroups.com.. .

no i dont get tired.

I'm sure that one day we'll read a news item about you that concludes "...
before turning the gun on himself."