How to create ForeignKeyConst raint?

[max]

Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I need to
change some data in the first table and let VB updates the same data in all
other four tables in the right places.
I know it would be possible by using the ForeignKeyConst raint object. I have
created the tables using the DataSet Visual Tool and I know it doesn't
create any ForeignKeyConst raint obj. I have found many codes examples on it,
but I don't know how to merge the code they in an existing dataset.

Hope somebody help.
Thanks in advance,
Max.

[Max]

"Branco Medeiros" It will always depend on how you generate these IDs (I
must warn you

that there are lots of lines of thought in this matter, every one
claiming to be the definitive one).

Personaly, I prefer having a table's ID/PK as an identity column
managed by the system (an autoincrement column, in Access), . Other
people may prefer to generate the keys themselves. Others still will
prefer to use something completely meaningless, say, a GUID, or
whatever (btw, I am, too, among the ones that think that the PK is
meant *only* to pinpoint a given record, but I prefer using a
light-weight identity column for that).

Autoincrement IDs are completely ruled out, for you. Because they're
managed by the DB engine, as soon as you have a situation with, say,
three visits only, the two other tables will become out of synch with
the other three. And it will only get worse after that.

Probably I cannot explain as I'd like. I think autoID is ok for me, because
the five tables must be changed independently from the rest of the
application. The visit tables, called tblPrest1... tblPrest5 (formed by
three columns: ID, Price, and type of visit) are just like a collection of
about 70 records. I mean, I have a menu--->tools--->Change visit table; it
loads a form with the first table; I make all the changes on it, then save
on the binding navigator. At this point, changes must be reflected on the
other four tables.
Next, when I want to open the Visit form, which writes in the Visit table, I
see my five drop down combobox, showing all the records loaded from the
visit table; next, I choose the type of visit, choose how many visits has
been made to that patient by clicking in combobox and leaving the other
combobox blank if visits are < 5, choose date, patient, doctor and stop.
Saving now writes a new record in the Visit table, which has an ID
independent from the other tables.
Thanks for your patience.

>
GUIDs are ruled out also, because, while autoincrement IDs are local to
each table, GUIDs are globally unique among every table, so you can't
have the same GUID for different tables (or so it seems. Forgive me if
I'm mistaken).

Finally, your only choice seems to be to generate your own keys, or
have a mix of tables with hand-made keys and one of the tables with
auto-generated keys (but which one?). Hand generated IDs have their
charm, but have some caveats. Most importantly, their generation logic
is left to the user, instead of remaining hidden inside the DB, and
this may give rise to inconsistencies and, ultimately, warts,
dizzyness, loss of sexual performance, the hell on earth... (=))) you
see I'm *a little* biased, here).

>About the visit pricing: there are more than 70 different type of medical
visits, and each one has a price.
So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.

Is this the table with information about each type of visit?

>If the boss decides to change the prices of, for i.e., 45 type of medical
visits, the changes must reflect to the other four tables.

Forgive my dumbness: I still don't get it. Are these prices supposed to
change (if they ever do) for a given visit, only? Or would they change
for a whole category of visits? Or, still, would the prices change only
for a given day (a Christmass promo, for instance =))) ?

>More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits,
the
other combo box will be blank);
Doctor's name.

That's it.
I just need to change the Price column in one table, and make
automaticall y
the changes (update, delete or insert) in the other four tables.
It sounds simple...

<snip>

It seems you want the price to be the same for all the visits of a
given patient in a given day.

Idealy, this would call for a master/detail relation:

Visit table: VisitID, PatientID, Price, Date

VisitDetail table: VisitDetailID, VisitID (the FK), VisitType, DoctorID
Alternatively (oh, the blasphemy!), you could have:

VisitID, PatientID, Price, Date, Visit1Type, Visit1Doctor, ... ,
Visit5Type, Visit5Doctor

HTH.

Regards,

Branco.

[Branco Medeiros]

Max wrote:
<snip>

Probably I cannot explain as I'd like.

No problem. We'll eventually get there, I hope.

I think autoID is ok for me, because
the five tables must be changed independently from the rest of the
application. The visit tables, called tblPrest1... tblPrest5 (formed by
three columns: ID, Price, and type of visit) are just like a collection of
about 70 records. I mean, I have a menu--->tools--->Change visit table; it
loads a form with the first table; I make all the changes on it, then save
on the binding navigator. At this point, changes must be reflected on the
other four tables.

<snip>

The only way I can think of to keep these tables in synch without
having to deal with a logic nightmare is to truncate and overwrite the
other four tables as soon as you finish editing the first one. It's a
long time since I used Access, so I'm not familiar with its current SQL
capabilities, but it would be something like this:

DELETE * FROM tblPrest2;
INSERT INTO tblPrest2
SELECT * FROM tblPrest1;
...
DELETE * FROM tblPrest5;
INSERT INTO tblPrest5
SELECT * FROM tblPrest1;

For this to work, the IDs of tables tblPrest2...tbl Prest5 can't be
autoincrement, but just regular ints (or so I suppose. You'd need to
perform some tests). Besides, I seem to recall that Access doesn't
allow multiple statements in a SQL command (I hope I'm wrong). If this
is the case, then you'll have to issue the commands *one by one*... =P

So, after having table tblPrest1 updated, this is the kind of code
you'd have to use to synchronize the tables:

<aircode>
'Assuming an *open* OleDbConnection Con

Dim Tables() As String = New String() { _
"tblPrest2" , "tblPrest3" , "tblPrest4" , "tblPrest5" }

Using Cmd As New OleDb.OleDbComm and()
Cmd.Connection = Con
For Each Table As String In Tables
Cmd.CommandText = "DELETE * FROM " & Table
Cmd.ExecuteNonQ uery()
Cmd.CommandText = "INSERT INTO " & Table _
& " SELECT * FROM tblPrest1"
Cmd.ExecuteNonQ uery()
Next
End Using
</aircode>

And it can only become uglier than this (or so it seems, given my
limited knowledge).

Another way to keep the tables in synch is quite obvious (don't laugh,
please): instead of having five tables, create just *one* table and
have other four views (or "queries" in Access parlance) mapping to the
first (and only) table:

tblPrest1: ID, Price, VisitType

tblPrest2 ... tblPrest5: four select queries created in Access with the
following SQL:
"SELECT * FROM tblPrest1"

This way you'd never have to update those "tables", and they'll be
permanently in synch. =)

HTH.

Regards,

Branco.

[Cor Ligthert [MVP]]

Max,

I really don't understand why you want 5 tables of the same rows instead of
one table with indified rows for each dokter, why are you doing that?

Cor

"Max" <no****@devnull .spamcop.netsch reef in bericht
news:45******** *************@n ews.tiscali.it. ..

>
"Branco Medeiros" It will always depend on how you generate these IDs (I
must warn you

>that there are lots of lines of thought in this matter, every one
claiming to be the definitive one).

Personaly, I prefer having a table's ID/PK as an identity column
managed by the system (an autoincrement column, in Access), . Other
people may prefer to generate the keys themselves. Others still will
prefer to use something completely meaningless, say, a GUID, or
whatever (btw, I am, too, among the ones that think that the PK is
meant *only* to pinpoint a given record, but I prefer using a
light-weight identity column for that).

Autoincremen t IDs are completely ruled out, for you. Because they're
managed by the DB engine, as soon as you have a situation with, say,
three visits only, the two other tables will become out of synch with
the other three. And it will only get worse after that.

Probably I cannot explain as I'd like. I think autoID is ok for me,
because the five tables must be changed independently from the rest of the
application. The visit tables, called tblPrest1... tblPrest5 (formed by
three columns: ID, Price, and type of visit) are just like a collection of
about 70 records. I mean, I have a menu--->tools--->Change visit table; it
loads a form with the first table; I make all the changes on it, then save
on the binding navigator. At this point, changes must be reflected on the
other four tables.
Next, when I want to open the Visit form, which writes in the Visit table,
I see my five drop down combobox, showing all the records loaded from the
visit table; next, I choose the type of visit, choose how many visits has
been made to that patient by clicking in combobox and leaving the other
combobox blank if visits are < 5, choose date, patient, doctor and stop.
Saving now writes a new record in the Visit table, which has an ID
independent from the other tables.
Thanks for your patience.

>>
GUIDs are ruled out also, because, while autoincrement IDs are local to
each table, GUIDs are globally unique among every table, so you can't
have the same GUID for different tables (or so it seems. Forgive me if
I'm mistaken).

Finally, your only choice seems to be to generate your own keys, or
have a mix of tables with hand-made keys and one of the tables with
auto-generated keys (but which one?). Hand generated IDs have their
charm, but have some caveats. Most importantly, their generation logic
is left to the user, instead of remaining hidden inside the DB, and
this may give rise to inconsistencies and, ultimately, warts,
dizzyness, loss of sexual performance, the hell on earth... (=))) you
see I'm *a little* biased, here).

>>About the visit pricing: there are more than 70 different type of
medical
visits, and each one has a price.
So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.

Is this the table with information about each type of visit?

>>If the boss decides to change the prices of, for i.e., 45 type of
medical
visits, the changes must reflect to the other four tables.

Forgive my dumbness: I still don't get it. Are these prices supposed to
change (if they ever do) for a given visit, only? Or would they change
for a whole category of visits? Or, still, would the prices change only
for a given day (a Christmass promo, for instance =))) ?

>>More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits,
the
other combo box will be blank);
Doctor's name.

That's it.
I just need to change the Price column in one table, and make
automatical ly
the changes (update, delete or insert) in the other four tables.
It sounds simple...

<snip>

It seems you want the price to be the same for all the visits of a
given patient in a given day.

Idealy, this would call for a master/detail relation:

Visit table: VisitID, PatientID, Price, Date

VisitDetail table: VisitDetailID, VisitID (the FK), VisitType, DoctorID
Alternativel y (oh, the blasphemy!), you could have:

VisitID, PatientID, Price, Date, Visit1Type, Visit1Doctor, ... ,
Visit5Type, Visit5Doctor

HTH.

Regards,

Branco.

[Max]

Cor,
as you may have understood, I'm really at the beginning with ado.net and
db's in vb. If the suggestion of Branco works fine, I would be very happy to
forget about FK and company; anyway, now, I'd like to understand how this
issue could be solved using five tables...
I'm learning, and I appreciate all of your suggestions.
I'll let you know.

"Cor Ligthert [MVP]" <no************ @planet.nlha scritto nel messaggio
news:Ow******** ********@TK2MSF TNGP04.phx.gbl. ..

Max,

I really don't understand why you want 5 tables of the same rows instead
of one table with indified rows for each dokter, why are you doing that?

Cor

"Max" <no****@devnull .spamcop.netsch reef in bericht
news:45******** *************@n ews.tiscali.it. ..

>>
"Branco Medeiros" It will always depend on how you generate these IDs
(I must warn you

>>that there are lots of lines of thought in this matter, every one
claiming to be the definitive one).

Personaly, I prefer having a table's ID/PK as an identity column
managed by the system (an autoincrement column, in Access), . Other
people may prefer to generate the keys themselves. Others still will
prefer to use something completely meaningless, say, a GUID, or
whatever (btw, I am, too, among the ones that think that the PK is
meant *only* to pinpoint a given record, but I prefer using a
light-weight identity column for that).

Autoincreme nt IDs are completely ruled out, for you. Because they're
managed by the DB engine, as soon as you have a situation with, say,
three visits only, the two other tables will become out of synch with
the other three. And it will only get worse after that.

Probably I cannot explain as I'd like. I think autoID is ok for me,
because the five tables must be changed independently from the rest of
the application. The visit tables, called tblPrest1... tblPrest5 (formed
by three columns: ID, Price, and type of visit) are just like a
collection of about 70 records. I mean, I have a menu--->tools--->Change
visit table; it loads a form with the first table; I make all the changes
on it, then save on the binding navigator. At this point, changes must be
reflected on the other four tables.
Next, when I want to open the Visit form, which writes in the Visit
table, I see my five drop down combobox, showing all the records loaded
from the visit table; next, I choose the type of visit, choose how many
visits has been made to that patient by clicking in combobox and leaving
the other combobox blank if visits are < 5, choose date, patient, doctor
and stop. Saving now writes a new record in the Visit table, which has an
ID independent from the other tables.
Thanks for your patience.

>>>
GUIDs are ruled out also, because, while autoincrement IDs are local to
each table, GUIDs are globally unique among every table, so you can't
have the same GUID for different tables (or so it seems. Forgive me if
I'm mistaken).

Finally, your only choice seems to be to generate your own keys, or
have a mix of tables with hand-made keys and one of the tables with
auto-generated keys (but which one?). Hand generated IDs have their
charm, but have some caveats. Most importantly, their generation logic
is left to the user, instead of remaining hidden inside the DB, and
this may give rise to inconsistencies and, ultimately, warts,
dizzyness, loss of sexual performance, the hell on earth... (=))) you
see I'm *a little* biased, here).

About the visit pricing: there are more than 70 different type of
medical
visits, and each one has a price.
So in a row, I have:
VisitID;
Price;
VisitType.
Just three columns.

Is this the table with information about each type of visit?

If the boss decides to change the prices of, for i.e., 45 type of
medical
visits, the changes must reflect to the other four tables.

Forgive my dumbness: I still don't get it. Are these prices supposed to
change (if they ever do) for a given visit, only? Or would they change
for a whole category of visits? Or, still, would the prices change only
for a given day (a Christmass promo, for instance =))) ?

More over these 'famous' five tables, there are only three more tables:
The Patients table (just ID, and name and surname);
The Doctors table (just ID, and name and surname);
The Visit table (called by a form) would consist of:
VisitID;
Date;
Patient name;
VisitType 1...VisitType 5 (combo box; if there are less than 5 visits,
the
other combo box will be blank);
Doctor's name.

That's it.
I just need to change the Price column in one table, and make
automaticall y
the changes (update, delete or insert) in the other four tables.
It sounds simple...
<snip>

It seems you want the price to be the same for all the visits of a
given patient in a given day.

Idealy, this would call for a master/detail relation:

Visit table: VisitID, PatientID, Price, Date

VisitDetail table: VisitDetailID, VisitID (the FK), VisitType, DoctorID
Alternative ly (oh, the blasphemy!), you could have:

VisitID, PatientID, Price, Date, Visit1Type, Visit1Doctor, ... ,
Visit5Type, Visit5Doctor

HTH.

Regards,

Branco.

[Max]

I want to try both your suggestions and I'll let you know...

"Branco Medeiros" <br************ *@gmail.comha scritto nel messaggio
news:11******** **************@ h40g2000cwb.goo glegroups.com.. .

Max wrote:
<snip>

>Probably I cannot explain as I'd like.

No problem. We'll eventually get there, I hope.

>I think autoID is ok for me, because
the five tables must be changed independently from the rest of the
application. The visit tables, called tblPrest1... tblPrest5 (formed by
three columns: ID, Price, and type of visit) are just like a collection
of
about 70 records. I mean, I have a menu--->tools--->Change visit table;
it
loads a form with the first table; I make all the changes on it, then
save
on the binding navigator. At this point, changes must be reflected on the
other four tables.

<snip>

The only way I can think of to keep these tables in synch without
having to deal with a logic nightmare is to truncate and overwrite the
other four tables as soon as you finish editing the first one. It's a
long time since I used Access, so I'm not familiar with its current SQL
capabilities, but it would be something like this:

DELETE * FROM tblPrest2;
INSERT INTO tblPrest2
SELECT * FROM tblPrest1;
...
DELETE * FROM tblPrest5;
INSERT INTO tblPrest5
SELECT * FROM tblPrest1;

For this to work, the IDs of tables tblPrest2...tbl Prest5 can't be
autoincrement, but just regular ints (or so I suppose. You'd need to
perform some tests). Besides, I seem to recall that Access doesn't
allow multiple statements in a SQL command (I hope I'm wrong). If this
is the case, then you'll have to issue the commands *one by one*... =P

So, after having table tblPrest1 updated, this is the kind of code
you'd have to use to synchronize the tables:

<aircode>
'Assuming an *open* OleDbConnection Con

Dim Tables() As String = New String() { _
"tblPrest2" , "tblPrest3" , "tblPrest4" , "tblPrest5" }

Using Cmd As New OleDb.OleDbComm and()
Cmd.Connection = Con
For Each Table As String In Tables
Cmd.CommandText = "DELETE * FROM " & Table
Cmd.ExecuteNonQ uery()
Cmd.CommandText = "INSERT INTO " & Table _
& " SELECT * FROM tblPrest1"
Cmd.ExecuteNonQ uery()
Next
End Using
</aircode>

And it can only become uglier than this (or so it seems, given my
limited knowledge).

Another way to keep the tables in synch is quite obvious (don't laugh,
please): instead of having five tables, create just *one* table and
have other four views (or "queries" in Access parlance) mapping to the
first (and only) table:

tblPrest1: ID, Price, VisitType

tblPrest2 ... tblPrest5: four select queries created in Access with the
following SQL:
"SELECT * FROM tblPrest1"

This way you'd never have to update those "tables", and they'll be
permanently in synch. =)

HTH.

Regards,

Branco.

[Max]

"Max" <no****@devnull .spamcop.netwro te:

>I want to try both your suggestions and I'll let you know...

>"Branco Medeiros" <br************ *@gmail.comwrot e:

<snip>

>The only way I can think of to keep these tables in synch without
having to deal with a logic nightmare is to truncate and overwrite the
other four tables as soon as you finish editing the first one. It's a
long time since I used Access, so I'm not familiar with its current SQL
capabilities , but it would be something like this:

DELETE * FROM tblPrest2;
INSERT INTO tblPrest2
SELECT * FROM tblPrest1;
...
DELETE * FROM tblPrest5;
INSERT INTO tblPrest5
SELECT * FROM tblPrest1;

For this to work, the IDs of tables tblPrest2...tbl Prest5 can't be
autoincremen t, but just regular ints (or so I suppose. You'd need to
perform some tests). Besides, I seem to recall that Access doesn't
allow multiple statements in a SQL command (I hope I'm wrong). If this
is the case, then you'll have to issue the commands *one by one*... =P

So, after having table tblPrest1 updated, this is the kind of code
you'd have to use to synchronize the tables:

<aircode>
'Assuming an *open* OleDbConnection Con

Dim Tables() As String = New String() { _
"tblPrest2" , "tblPrest3" , "tblPrest4" , "tblPrest5" }

Using Cmd As New OleDb.OleDbComm and()
Cmd.Connection = Con
For Each Table As String In Tables
Cmd.CommandText = "DELETE * FROM " & Table
Cmd.ExecuteNonQ uery()
Cmd.CommandText = "INSERT INTO " & Table _
& " SELECT * FROM tblPrest1"
Cmd.ExecuteNonQ uery()
Next
End Using
</aircode>

And it can only become uglier than this (or so it seems, given my
limited knowledge).

IT WORKS! It's GREAT for all five tables in one step!

>Another way to keep the tables in synch is quite obvious (don't laugh,
please): instead of having five tables, create just *one* table and
have other four views (or "queries" in Access parlance) mapping to the
first (and only) table:

tblPrest1: ID, Price, VisitType

tblPrest2 ... tblPrest5: four select queries created in Access with the
following SQL:
"SELECT * FROM tblPrest1"

This way you'd never have to update those "tables", and they'll be
permanently in synch. =)

HTH.

Regards,

Branco.

I'm gonna try also this (more elegant) method, and let you know.
Thanks a lot!

[Branco Medeiros]

Max wrote:
<snip>

Next, when I want to open the Visit form, which writes in the Visit table, I
see my five drop down combobox, showing all the records loaded from the
visit table; next, I choose the type of visit, choose how many visits has
been made to that patient by clicking in combobox and leaving the other
combobox blank if visits are < 5, choose date, patient, doctor and stop.
Saving now writes a new record in the Visit table, which has an ID
independent from the other tables.

<snip>

Oh, *now* I got it!

You don't need five tables. You only need one (tblPrest1).

I suppose you're working with the Visits table in record view (as
opposed to grid view).

If this is the case, open the Visit form and drag the tblPrest1 table
from the Data Sources panel *over* the combo box of the VisitType1
field that is in the form. Then select the combo box and set the
DisplayMember property to the name of the field from tblPrest1 you want
to be displayed (probably the visit type). Set the ValueMember property
to the name of the field from tblPrest1 that you want to *store*
(probably its ID).

Do exactly the same thing with the other four Combos: drag *tblPrest1*
from the data sources panel over the combo box, set the fields, etc.

You're all set to go.

HTH.

Regards,

Branco.

[Max]

Thanks Branco,
it works! But it also works your suggest in your previous post about SQL
statements to Access db:

*************** *************** ******
'Assuming an *open* OleDbConnection Con

Dim Tables() As String = New String() { _
"tblPrest2" , "tblPrest3" , "tblPrest4" , "tblPrest5" }

Using Cmd As New OleDb.OleDbComm and()
Cmd.Connection = Con
For Each Table As String In Tables
Cmd.CommandText = "DELETE * FROM " & Table
Cmd.ExecuteNonQ uery()
Cmd.CommandText = "INSERT INTO " & Table _
& " SELECT * FROM tblPrest1"
Cmd.ExecuteNonQ uery()
Next
End Using
*************** *************** ******

By now, I don't want know anymore else about FK, because I risk to be
involved in the patient table of my db, and in a 'psychiatric' medical
visit... :)))
Thanks a lot, and happy to know there are so many nice people, helping
'absolute beginner' as mine...
You'll see me in my next issue! :)
Max

"Branco Medeiros" <br************ *@gmail.comha scritto nel messaggio
news:11******** **************@ 48g2000cwx.goog legroups.com...

Max wrote:
<snip>

>Next, when I want to open the Visit form, which writes in the Visit
table, I
see my five drop down combobox, showing all the records loaded from the
visit table; next, I choose the type of visit, choose how many visits has
been made to that patient by clicking in combobox and leaving the other
combobox blank if visits are < 5, choose date, patient, doctor and stop.
Saving now writes a new record in the Visit table, which has an ID
independent from the other tables.

<snip>

Oh, *now* I got it!

You don't need five tables. You only need one (tblPrest1).

I suppose you're working with the Visits table in record view (as
opposed to grid view).

If this is the case, open the Visit form and drag the tblPrest1 table
from the Data Sources panel *over* the combo box of the VisitType1
field that is in the form. Then select the combo box and set the
DisplayMember property to the name of the field from tblPrest1 you want
to be displayed (probably the visit type). Set the ValueMember property
to the name of the field from tblPrest1 that you want to *store*
(probably its ID).

Do exactly the same thing with the other four Combos: drag *tblPrest1*
from the data sources panel over the combo box, set the fields, etc.

You're all set to go.

HTH.

Regards,

Branco.