How to create ForeignKeyConstraint?

  • Thread starter Thread starter max
  • Start date Start date
"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.
Click to expand...

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.
 
Max wrote:
Probably I cannot explain as I'd like.
Click to expand...

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.
Click to expand...
<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...tblPrest5 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.OleDbCommand()
Cmd.Connection = Con
For Each Table As String In Tables
Cmd.CommandText = "DELETE * FROM " & Table
Cmd.ExecuteNonQuery()
Cmd.CommandText = "INSERT INTO " & Table _
& " SELECT * FROM tblPrest1"
Cmd.ExecuteNonQuery()
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,

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
 
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.
 
Max said:
I want to try both your suggestions and I'll let you know...
Click to expand...

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

I'm gonna try also this (more elegant) method, and let you know.
Thanks a lot!
 
Max wrote:
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.
Click to expand...
<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.
 
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.OleDbCommand()
Cmd.Connection = Con
For Each Table As String In Tables
Cmd.CommandText = "DELETE * FROM " & Table
Cmd.ExecuteNonQuery()
Cmd.CommandText = "INSERT INTO " & Table _
& " SELECT * FROM tblPrest1"
Cmd.ExecuteNonQuery()
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
 
Back
Top