Temporarily remove referential integrity checks through VBA?

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.text & ", strLastName=" & txtLastName.text
...... 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11477-temporarily-remove-referential-integrity-checks-through.html
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?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477

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

Regards
Andrew Richards

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

"Andrew R" schreef in bericht news:1150354744.619090.134670@p79g2000cwp.googlegr oups.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
> record already exists, and therefore we are doing an update. Creating a
> SQL statement along the lines of "UPDATE tblCustomers SET strFirstName
> = " & txtFirstName.text & ", strLastName=" & txtLastName.text
> ..... 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
> checks, run the delete and insert statements, then turn them on again?
>
> Apologies for being long-winded! Any help gratefully received!
>
> Regards
> Andrew Richards

Removing RI is *not* the way to go
Deleting the tblCustomers-record is *not* the way to go

These are both very bad and dangerous idea's IMO

What is wrong with bound textboxes??
You could edit, add, delete, cancel changes and so on.
You could save yourself a lot of coding indeed! and... you would not have these problems.

Arno R

Hi, Andrew.

> The text boxes will show data from tables, but are unbound to make them
> more flexible.

That extra flexibility comes at the risk of compromising data integrity and
at a cost of extra work on the database developer's part. Are you sure that
you can't do what you need to do with bound forms?

> Is there an easy way to detect which
> field has been changed (without writing code behind each text box,

Yes. You can do a visual inpection of the records first. But SQL (or even
VBA code) to update these records would be far more efficient if you ask me.

> 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!)

Uh, . . . isn't that what you're getting paid for? To do hours and hours of
work? Every day you come to work? ;-)

> is there a way through the code to turn off
> checks, run the delete and insert statements, then turn them on again?

Jet doesn't have deferred constraints like client/server databases do, so
you'll have to drop the constraints, not just "turn them off." Honestly, an
update query is the best way to handle this, but if you want to do it the
hard way, you can delete, then insert the appropriate records, but you'll
have to take special precautions.

First, back up the database in case something goes wrong. Next, create a
table level write lock on these related tables, because you don't want other
users to be changing data while referential integrity isn't being enforced.
(Alternatively, you can open the database in exclusive mode if the users
won't tar and feather you for blocking them from doing their work.) Drop
the foreign key constraints between these tables. Delete the record and
insert a new one with the same ID for the primary key. Repeat for as many
records as needed, then recreate the foreign key constraints again.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.


"Andrew R" wrote in message
news:1150354744.619090.134670@p79g2000cwp.googlegr oups.com...
> 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.text & ", strLastName=" & txtLastName.text
> ..... 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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
> 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
>

You either use a bound form and save yourself a bunch of coding but you are
tied to the bound way of doing things

or

You use unbound and have to do a bunch of coding but you have the
flexibility to do exactly what you want.

You've chosen unbound and now you have to bite the bullet and write the
code.

Try thinking about writing a SQL builder function which you can use from any
of your forms. You pass the tablename and the values from your fields and
let the SQL builder function build the SQL for the update. Do it once, get
it right and then just use it.

--

Terry Kreft


"Andrew R" wrote in message
news:1150354744.619090.134670@p79g2000cwp.googlegr oups.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
> 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.text & ", strLastName=" & txtLastName.text
> ..... 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
>

"Andrew R" wrote in message
news:1150354744.619090.134670@p79g2000cwp.googlegr oups.com...
>
> 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!)
>

I wrote a function to do this with any form for an audit trail, you just
pass the form to the function along with the record's unique ID. Here's an
extract from the code, you may be able to adapt it and probably make it more
elegant in the process:

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
Calling code:
Call libHistory(Me, Me.txtID)

Public Function libHistory(frmForm As Form, lngID As Long)

Dim ctl As Control

For Each ctl In frmForm
'Ignore controls such as labels
If ctl.Name Like "txt*" Or ctl.Name Like "cbo*" Or ctl.Name Like
"ogr*" Or ctl.Name Like "chk*" Then
'Record null to value, value to null, and value changes
If ((IsNull(ctl.OldValue) And Not IsNull(ctl.Value)) Or
(IsNull(ctl.Value) And Not IsNull(ctl.OldValue)) _
Or ctl.OldValue <> ctl.Value) Then
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
'DO STUFF
End If
End If
Next

End Function

Regards,
Keith.
www.keithwilby.com

Andrew R wrote:
> Creating a
> SQL statement along the lines of "UPDATE tblCustomers SET strFirstName
> = " & txtFirstName.text & ", strLastName=" & txtLastName.text
> ..... 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.

Under the covers, in the engine will perform an update by first
deleting the old row then inserting a new row with the new values (it
does it in a more controlled way than you could 'by hand'). Trying to
detect which column values have changed or otherwise is most likely a
waste of your time and effort. The engine won't care so why should you?
Just send all the values across in one hit.

I can appreciate why you don't like creating a dynamic UPDATE
statement. As you are using ADO, you could create a PROCEDURE with
(optional) parameters, using default parameter values to detect values
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
'missing' from the call. Post back if you'd like to see an example.

There are circumstances where you need to disable DRI, CHECK
constraints, etc temporarily while you complete a set of operations.
You obviously need to do this in a transaction in case the new data
prevents you switching them back (i.e. constraints have been violated)
so you can rollback the changes. However, yours does not sound like one
of those circumstances.

Jamie.

--

"'69 Camaro"
wrote in news:OYrv#WFkGHA.456@TK2MSFTNGP05.phx.gbl:

> Jet doesn't have deferred constraints like client/server databases
> do, so you'll have to drop the constraints, not just "turn them
> off."

I can't imagine a situation in which this would be advisable. What
happens if someone else adds non-conformant data to the database
during the time when the constraints have been turned off?

Changes to the schema should *never* happen incidentally as a part
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
of daily operation of an application. If the application depends on
that, then there's a horrendously bad design in place and that
should be fixed.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477

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

"Jamie Collins" wrote in
news:1150360905.216967.31720@u72g2000cwu.googlegro ups.com:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477

> There are circumstances where you need to disable DRI, CHECK
> constraints, etc temporarily while you complete a set of
> operations.

I don't agree with this. I don't see any situations where RI should
be disabled.

--
David W. Fenton http://www.dfenton.com/
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11477
David W. Fenton wrote:
> > There are circumstances where you need to disable DRI, CHECK
> > constraints, etc temporarily while you complete a set of
> > operations.
>
> I don't agree with this. I don't see any situations where RI should
> be disabled.

Perhaps 'need' was the wrong word; substitute 'may find it conducive'.

What about the situation where you need to alter a key's value and
can't use ON UPDATE CASADE on all you tables because there are
potential cycles or multiple update paths that the engine isn't smart
enough to resolve?

Jamie.

--