473,883 Members | 1,798 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Jun 15 '06 #1
80 7925

"Andrew R" <an************ *@cashette.com> schreef in bericht news:11******** **************@ p79g2000cwp.goo glegroups.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.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


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
Jun 15 '06 #2
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" <an************ *@cashette.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.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.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

Jun 15 '06 #3
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" <an************ *@cashette.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.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.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

Jun 15 '06 #4
"Andrew R" <an************ *@cashette.com> wrote in message
news:11******** **************@ p79g2000cwp.goo glegroups.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:

Calling code:
Call libHistory(Me, Me.txtID)

Public Function libHistory(frmF orm 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.Ol dValue) And Not IsNull(ctl.Valu e)) Or
(IsNull(ctl.Val ue) And Not IsNull(ctl.OldV alue)) _
Or ctl.OldValue <> ctl.Value) Then
'DO STUFF
End If
End If
Next

End Function

Regards,
Keith.
www.keithwilby.com
Jun 15 '06 #5

Andrew R wrote:
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.


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

--

Jun 15 '06 #6
"'69 Camaro" <Fo************ **************@ Spameater.orgZE RO_SPAM>
wrote in news:OY******** *****@TK2MSFTNG P05.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
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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 15 '06 #7
"Jamie Collins" <ja**********@x smail.com> wrote in
news:11******** *************@u 72g2000cwu.goog legroups.com:
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/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 15 '06 #8

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.

--

Jun 15 '06 #9
"Jamie Collins" <ja**********@x smail.com> wrote in
news:11******** **************@ p79g2000cwp.goo glegroups.com:

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?


Then your schema is wrong.

Fix that and the problem goes away.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 15 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3694
by: Grant McLean | last post by:
Hi First a simple question ... I have a table "access_log" that has foreign keys "app_id" and "app_user_id" that reference the "application_type" and "app_user" tables. When I insert into "access_log", the referential integrity triggers generate these queries: SELECT 1 FROM ONLY "public"."application_type" x
0
9942
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9792
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11142
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10745
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9575
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7130
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5798
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4221
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3234
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.