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

[Terry Kreft]

It's funny though, isn't it, how some trolls are amusing, especially when
they're trying to be obnoxious and others are just plain obnoxious?

--

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

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

[Lyle Fairfield]

Terry Kreft wrote:

It's funny though, isn't it, how some trolls are amusing, especially when
they're trying to be obnoxious and others are just plain obnoxious?

IIRC Aaron has made some helpful and informative posts in ADP groups. A
year or so ago he seemed more temperate, reasoned and capable, ...
even. But, he's not contributing much any more; he hasn't even
introduced me to any new "words". Life goes on.

[aaron.kempf]

SQL RI works across the 2gb limit and the 2gb limit in Access??

I can sneeze and make 2gb of data out of it.

I've seen spreadsheets that hit the 2gb limit. i mean for christ sakes.

you can't rely on RI in an MDB.. not for only 2 tables with 2 records
each and only 2 users.

in practical implementations ; it is impossible to do.

-Aaron
Jamie Collins wrote:

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.

--

[David W. Fenton]

"Terry Kreft" <te*********@mp s.co.uk> wrote in
news:45******** ************@ka roo.co.uk:

It's funny though, isn't it, how some trolls are amusing,
especially when they're trying to be obnoxious and others are just
plain obnoxious?

Amusement is in the eye of the beholder.

This beholder's eyes are complete devoid of any amusement.

If you give a rat's ass.

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

[Terry Kreft]

In the eye of the beholder is Amusement.

Devoid of any amusement this beholder's eyes are complete.

If you give a rat's ass.
--

Terry Kreft
"David W. Fenton" <XX*******@dfen ton.com.invalid > wrote in message
news:Xn******** *************** ***********@127 .0.0.1...

"Terry Kreft" <te*********@mp s.co.uk> wrote in
news:45******** ************@ka roo.co.uk:

It's funny though, isn't it, how some trolls are amusing,
especially when they're trying to be obnoxious and others are just
plain obnoxious?

Amusement is in the eye of the beholder.

This beholder's eyes are complete devoid of any amusement.

If you give a rat's ass.

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

[Jamie Collins]

aa*********@gma il.com wrote:

SQL [Server] RI works across the 2gb limit

File size (and other limits) aside, DRI is better implemented in Jet
4.0 than in SQL Server 2005 e.g. try this simple example:

CREATE TABLE OrgChart (
employee_number INTEGER NOT NULL PRIMARY KEY,
boss INTEGER
REFERENCES OrgChart (employee_numbe r)
ON DELETE CASCADE
ON UPDATE CASCADE
);

SQL Server 2005 worries that this 'may cause cycles or multiple cascade
paths' when even a moron (e.g. Jet 4.0) can figure it out.

Do I choose Jet over SQL Server because its implementation is better in
some areas e.g. DRI, CHECK constraints, etc? Of course not.

The fact remains that I sometimes design DRI/CHECKs in Jet 4.0 before
having to 'dumb down' for SQL Server 2005 e.g. a trigger with the
comment 'Replace this trigger with proper functionality when the SQL
Server team stop obsessing over CLR and get round finishing the
implementation of the SQL-92 standard, or at least get as smarts as
Access was FIVE years ago.'

Jamie.

--

[Arno R]

<aa*********@gm ail.com> schreef in bericht news:11******** **************@ u72g2000cwu.goo glegroups.com.. .

SQL RI works across the 2gb limit and the 2gb limit in Access??

I can sneeze and make 2gb of data out of it.

I've seen spreadsheets that hit the 2gb limit. i mean for christ sakes.

you can't rely on RI in an MDB.. not for only 2 tables with 2 records
each and only 2 users.

in practical implementations ; it is impossible to do.

-Aaron

Can't you just get lost with your horsecrap ??

BTW: Horsecrap *is* your language isn't it ??

Arno R

[aaron.kempf]

Terry;

it is so hilarious that you guys sit around and defend an obsolete
database format.. that is TEN YEARS out of date.

Grow some balls and lose the training wheels; kids.

Access Data Projects _SLAUGHTERS_ MDB format. It slaughters ACCDB
format also.

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

Oh, are you back?

I thought that you had possibly gone off to learn a new tune, or at least
add anoher string to your bow.

It's obvious that you haven't been for an eye-test, as every problem is
still looking like a nail to you.

--

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

Terry;

it is so hilarious that you guys sit around and defend an obsolete
database format.. that is TEN YEARS out of date.

Grow some balls and lose the training wheels; kids.

Access Data Projects _SLAUGHTERS_ MDB format. It slaughters ACCDB
format also.

-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******** **************@ c74g2000cwc.goo glegroups.com.. .

Grow some balls

I shounds like you lost yoursh in a terrible shmelting accident.