"max" <m**@max.max> wrote in message
news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>
> 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 ForeignKeyConstraint object.
> I have created the tables using the DataSet Visual Tool and I know it
> doesn't create any ForeignKeyConstraint 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.
>
>

"RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
news:0e6dndYZibmr4xXYnZ2dnUVZ_ompnZ2d@comcast.com...
>
> What kind of database are you using? VB2003 or VB2005?
> .Net 1.1 or .Net 2.0?
>
> How are you filling your tables?
>
> Robin S.
> -------------------------------------
>
> "max" <m**@max.max> wrote in message
> news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>>
>> 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 ForeignKeyConstraint object. I
>> have created the tables using the DataSet Visual Tool and I know it
>> doesn't create any ForeignKeyConstraint 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" <m**@max.max> wrote in message
news:197ih.9490$BR1.7672@tornado.fastwebnet.it...
>
> Thanks for yor answer. I'm using VB2005, .Net 2.0.
> Tables structure and data was developed in Access and loaded in a
> dataset using the Data Source Configuration Wizard. Then I created a
> form, dragged the table from the Dataset which creates a
> bindingnavigator obj on top of form; a second form load this form by a
> button and I correctly see the data. There are only three columns in
> the tables: an incremental ID (I gave it primary key), a number, and a
> name. Then I need to change, insert and delete records in the first
> table and replicate this change in all the other four tables, that are
> identical in structure and data. I can change and update data in the
> first table (because I see that changes was really on it), but I
> cannot replicate in the others. I have already tried to make relations
> using Data Designer, but it doesn't work; I have read in some
> newsgroup that ForeignKeyConstraint cannot be created by this way.
> I'd like to understand where and how insert vb code to create
> relationship between columns in the first table to all the others. I
> can't realize in which vb module, vb code must be added.
> Thanks for your attention and sorry for my English.
> Max
>
> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
> news:0e6dndYZibmr4xXYnZ2dnUVZ_ompnZ2d@comcast.com...
>>
>> What kind of database are you using? VB2003 or VB2005?
>> .Net 1.1 or .Net 2.0?
>>
>> How are you filling your tables?
>>
>> Robin S.
>> -------------------------------------
>>
>> "max" <m**@max.max> wrote in message
>> news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>>>
>>> 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 ForeignKeyConstraint
>>> object. I have created the tables using the DataSet Visual Tool and
>>> I know it doesn't create any ForeignKeyConstraint 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.
>>>
>>>
>>
>>
>>
>
>
>

"RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>
>
> Do you have no relationships defined between the tables
> in Access? Do they not show up in the Dataset Designer?
>
> I'm not going to swear this will work. But you could
> try adding your own foreign key constraints. I'm also not
> sure where you would do this, but I would guess *after* the
> dataset is filled, and *before* you do the binding. (I'm
> assuming you're doing the binding in code, but maybe not?)
>
> Here's an example on setting up a foreign key constraint.
> This is from David Sceppa's book ADO.Net 2.0 The Core
> Reference.
>
> This uses the Customers and Orders tables from the Northwind
> database.
>
> 'how to define a foreign key constraint
> 'first, fill your dataset with the two tables involved
> 'this is mine; this is loading from SQLServer, but
> '  load it however you're loading it from Access
> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
> da.TableMappings.Add("Table","Customers")
> da.TableMappings.Add("Table1","Orders")
> Dim ds as New DataSet()
> da.Fill(ds)
>
> 'now add the foreign key constraint
> Dim fk as ForeignKeyConstraint
> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>  ds.Tables("Customers").Columns("CustomerID"), _
>  ds.Tables("Orders").Columns("CustomerID"))
>
> Maybe if you play around with the code in this example,
> you can figure out some way to get it to work. If you want
> more help, you're going to need to post some code.
>
> Robin S.
> ----------------
> "max" <m**@max.max> wrote in message
> news:197ih.9490$BR1.7672@tornado.fastwebnet.it...
>>
>> Thanks for yor answer. I'm using VB2005, .Net 2.0.
>> Tables structure and data was developed in Access and loaded in a dataset
>> using the Data Source Configuration Wizard. Then I created a form,
>> dragged the table from the Dataset which creates a bindingnavigator obj
>> on top of form; a second form load this form by a button and I correctly
>> see the data. There are only three columns in the tables: an incremental
>> ID (I gave it primary key), a number, and a name. Then I need to change,
>> insert and delete records in the first table and replicate this change in
>> all the other four tables, that are identical in structure and data. I
>> can change and update data in the first table (because I see that changes
>> was really on it), but I cannot replicate in the others. I have already
>> tried to make relations using Data Designer, but it doesn't work; I have
>> read in some newsgroup that ForeignKeyConstraint cannot be created by
>> this way.
>> I'd like to understand where and how insert vb code to create
>> relationship between columns in the first table to all the others. I
>> can't realize in which vb module, vb code must be added.
>> Thanks for your attention and sorry for my English.
>> Max
>>
>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>> news:0e6dndYZibmr4xXYnZ2dnUVZ_ompnZ2d@comcast.com...
>>>
>>> What kind of database are you using? VB2003 or VB2005?
>>> .Net 1.1 or .Net 2.0?
>>>
>>> How are you filling your tables?
>>>
>>> Robin S.
>>> -------------------------------------
>>>
>>> "max" <m**@max.max> wrote in message
>>> news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>>>>
>>>> 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 ForeignKeyConstraint object. I
>>>> have created the tables using the DataSet Visual Tool and I know it
>>>> doesn't create any ForeignKeyConstraint 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" <m**@max.max> wrote in message
news:9dyih.1750$AA.434@tornado.fastwebnet.it...
>
> I'm sorry, but I really can't solve the problem.
> I don't have any relationships defined in Access, neither I have set
> in Dataset Designer.
> Trying to use your code, I'm in trouble about connection string, this
> is mine (I put the code in a Form_Load environment):
> Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
> I get an error in "Provider", it's not recognized by SqlDataAdapter.
>
> I have this code:
>
> Public Class Form_modPrest1
> Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
> System.Object, ByVal e As System.EventArgs) Handles
> TblPrest1BindingNavigatorSaveItem.Click
> Me.Validate()
> Me.TblPrest1BindingSource.EndEdit()
> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
> End Sub
> Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e
> As System.EventArgs) Handles MyBase.Load
> Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)
>
> This cames automatically from dragging the tblPrest1 from dataset to
> the form, in a datagrid format. I put your code under last line. And
> it doesn't work.
> Because I have other four tables identical to this, I get also five
> forms identical, changing only the names of form (modPrest2...) and
> names of tables (tblPrest2...)
> Then I load these forms by a menu, and I see the contents of all
> tables in datagrid format.
> Then I make a change in the tblPrest1, and save the change by clicking
> in the floppy disk icon. When I reload the table, changes are
> effectively in use.
> It seems this procedure already fill the table in dataset... or not?
> I can't understand how automatically changing the other four tables
> without calling each one by one.
> I'm sorry for this but I'm really at the beginning about database
> programming.
> Please help and thanks for your patience.
>
> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
> news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>>
>>
>> Do you have no relationships defined between the tables
>> in Access? Do they not show up in the Dataset Designer?
>>
>> I'm not going to swear this will work. But you could
>> try adding your own foreign key constraints. I'm also not
>> sure where you would do this, but I would guess *after* the
>> dataset is filled, and *before* you do the binding. (I'm
>> assuming you're doing the binding in code, but maybe not?)
>>
>> Here's an example on setting up a foreign key constraint.
>> This is from David Sceppa's book ADO.Net 2.0 The Core
>> Reference.
>>
>> This uses the Customers and Orders tables from the Northwind
>> database.
>>
>> 'how to define a foreign key constraint
>> 'first, fill your dataset with the two tables involved
>> 'this is mine; this is loading from SQLServer, but
>> '  load it however you're loading it from Access
>> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
>> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
>> da.TableMappings.Add("Table","Customers")
>> da.TableMappings.Add("Table1","Orders")
>> Dim ds as New DataSet()
>> da.Fill(ds)
>>
>> 'now add the foreign key constraint
>> Dim fk as ForeignKeyConstraint
>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>  ds.Tables("Customers").Columns("CustomerID"), _
>>  ds.Tables("Orders").Columns("CustomerID"))
>>
>> Maybe if you play around with the code in this example,
>> you can figure out some way to get it to work. If you want
>> more help, you're going to need to post some code.
>>
>> Robin S.
>> ----------------
>> "max" <m**@max.max> wrote in message
>> news:197ih.9490$BR1.7672@tornado.fastwebnet.it...
>>>
>>> Thanks for yor answer. I'm using VB2005, .Net 2.0.
>>> Tables structure and data was developed in Access and loaded in a
>>> dataset using the Data Source Configuration Wizard. Then I created a
>>> form, dragged the table from the Dataset which creates a
>>> bindingnavigator obj on top of form; a second form load this form by
>>> a button and I correctly see the data. There are only three columns
>>> in the tables: an incremental ID (I gave it primary key), a number,
>>> and a name. Then I need to change, insert and delete records in the
>>> first table and replicate this change in all the other four tables,
>>> that are identical in structure and data. I can change and update
>>> data in the first table (because I see that changes was really on
>>> it), but I cannot replicate in the others. I have already tried to
>>> make relations using Data Designer, but it doesn't work; I have read
>>> in some newsgroup that ForeignKeyConstraint cannot be created by
>>> this way.
>>> I'd like to understand where and how insert vb code to create
>>> relationship between columns in the first table to all the others. I
>>> can't realize in which vb module, vb code must be added.
>>> Thanks for your attention and sorry for my English.
>>> Max
>>>
>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>> news:0e6dndYZibmr4xXYnZ2dnUVZ_ompnZ2d@comcast.com...
>>>>
>>>> What kind of database are you using? VB2003 or VB2005?
>>>> .Net 1.1 or .Net 2.0?
>>>>
>>>> How are you filling your tables?
>>>>
>>>> Robin S.
>>>> -------------------------------------
>>>>
>>>> "max" <m**@max.max> wrote in message
>>>> news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>>>>>
>>>>> 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 ForeignKeyConstraint
>>>>> object. I have created the tables using the DataSet Visual Tool
>>>>> and I know it doesn't create any ForeignKeyConstraint 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.
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>>
>
>
>

"RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
news:ALWdnT5eXZlm6xbYnZ2dnUVZ_qSrnZ2d@comcast.com...
> Argh. Sorry; I didn't realize you were going to use my dataset
> code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
> But you don't need that stuff if you're using a DataSet that
> you set up in your DataSet designer; you only need the code defining
> the Foreign Key Restraint. I included the other just to show
> position.
>
> I'm assuming you have *two* datatables in your dataset, one
> being the parent and one being the child? Your code only
> seems to fill one table. Where is the other one?
>
> ds is my dataset name.
> Customers is my parent table.
> Orders is my child table.
> CustomerID is the field in both tables that relates them
> to each other.
>
>>> 'now add the foreign key constraint
>>> Dim fk as ForeignKeyConstraint
>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>  ds.Tables("Orders").Columns("CustomerID"))
>
> If you still can't figure it out, please re-post your code
> showing the filling of the tables, probably in your form_load
> routine.
>
> And if you post more code, if you paste it into notepad and
> then copy and paste it into your posting, it will be
> indented properly and be easier to read.
>
> Robin S.
> -----------------------
>
> "max" <m**@max.max> wrote in message
> news:9dyih.1750$AA.434@tornado.fastwebnet.it...
>>
>> I'm sorry, but I really can't solve the problem.
>> I don't have any relationships defined in Access, neither I have set in
>> Dataset Designer.
>> Trying to use your code, I'm in trouble about connection string, this is
>> mine (I put the code in a Form_Load environment):
>> Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
>> I get an error in "Provider", it's not recognized by SqlDataAdapter.
>>
>> I have this code:
>>
>> Public Class Form_modPrest1
>> Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>> System.Object, ByVal e As System.EventArgs) Handles
>> TblPrest1BindingNavigatorSaveItem.Click
>> Me.Validate()
>> Me.TblPrest1BindingSource.EndEdit()
>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>> End Sub
>> Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e As
>> System.EventArgs) Handles MyBase.Load
>> Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)
>>
>> This cames automatically from dragging the tblPrest1 from dataset to the
>> form, in a datagrid format. I put your code under last line. And it
>> doesn't work.
>> Because I have other four tables identical to this, I get also five forms
>> identical, changing only the names of form (modPrest2...) and names of
>> tables (tblPrest2...)
>> Then I load these forms by a menu, and I see the contents of all tables
>> in datagrid format.
>> Then I make a change in the tblPrest1, and save the change by clicking in
>> the floppy disk icon. When I reload the table, changes are effectively in
>> use.
>> It seems this procedure already fill the table in dataset... or not?
>> I can't understand how automatically changing the other four tables
>> without calling each one by one.
>> I'm sorry for this but I'm really at the beginning about database
>> programming.
>> Please help and thanks for your patience.
>>
>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>> news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>>>
>>>
>>> Do you have no relationships defined between the tables
>>> in Access? Do they not show up in the Dataset Designer?
>>>
>>> I'm not going to swear this will work. But you could
>>> try adding your own foreign key constraints. I'm also not
>>> sure where you would do this, but I would guess *after* the
>>> dataset is filled, and *before* you do the binding. (I'm
>>> assuming you're doing the binding in code, but maybe not?)
>>>
>>> Here's an example on setting up a foreign key constraint.
>>> This is from David Sceppa's book ADO.Net 2.0 The Core
>>> Reference.
>>>
>>> This uses the Customers and Orders tables from the Northwind
>>> database.
>>>
>>> 'how to define a foreign key constraint
>>> 'first, fill your dataset with the two tables involved
>>> 'this is mine; this is loading from SQLServer, but
>>> '  load it however you're loading it from Access
>>> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
>>> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
>>> da.TableMappings.Add("Table","Customers")
>>> da.TableMappings.Add("Table1","Orders")
>>> Dim ds as New DataSet()
>>> da.Fill(ds)
>>>
>>> 'now add the foreign key constraint
>>> Dim fk as ForeignKeyConstraint
>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>
>>> Maybe if you play around with the code in this example,
>>> you can figure out some way to get it to work. If you want
>>> more help, you're going to need to post some code.
>>>
>>> Robin S.
>>> ----------------
>>> "max" <m**@max.max> wrote in message
>>> news:197ih.9490$BR1.7672@tornado.fastwebnet.it...
>>>>
>>>> Thanks for yor answer. I'm using VB2005, .Net 2.0.
>>>> Tables structure and data was developed in Access and loaded in a
>>>> dataset using the Data Source Configuration Wizard. Then I created a
>>>> form, dragged the table from the Dataset which creates a
>>>> bindingnavigator obj on top of form; a second form load this form by a
>>>> button and I correctly see the data. There are only three columns in
>>>> the tables: an incremental ID (I gave it primary key), a number, and a
>>>> name. Then I need to change, insert and delete records in the first
>>>> table and replicate this change in all the other four tables, that are
>>>> identical in structure and data. I can change and update data in the
>>>> first table (because I see that changes was really on it), but I cannot
>>>> replicate in the others. I have already tried to make relations using
>>>> Data Designer, but it doesn't work; I have read in some newsgroup that
>>>> ForeignKeyConstraint cannot be created by this way.
>>>> I'd like to understand where and how insert vb code to create
>>>> relationship between columns in the first table to all the others. I
>>>> can't realize in which vb module, vb code must be added.
>>>> Thanks for your attention and sorry for my English.
>>>> Max
>>>>
>>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>>> news:0e6dndYZibmr4xXYnZ2dnUVZ_ompnZ2d@comcast.com...
>>>>>
>>>>> What kind of database are you using? VB2003 or VB2005?
>>>>> .Net 1.1 or .Net 2.0?
>>>>>
>>>>> How are you filling your tables?
>>>>>
>>>>> Robin S.
>>>>> -------------------------------------
>>>>>
>>>>> "max" <m**@max.max> wrote in message
>>>>> news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>>>>>>
>>>>>> 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 ForeignKeyConstraint object.
>>>>>> I have created the tables using the DataSet Visual Tool and I know it
>>>>>> doesn't create any ForeignKeyConstraint 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.
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>>
>
>


"RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
news:ALWdnT5eXZlm6xbYnZ2dnUVZ_qSrnZ2d@comcast.com...
> Argh. Sorry; I didn't realize you were going to use my dataset
> code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
> But you don't need that stuff if you're using a DataSet that
> you set up in your DataSet designer; you only need the code defining
> the Foreign Key Restraint. I included the other just to show
> position.
>
> I'm assuming you have *two* datatables in your dataset, one
> being the parent and one being the child? Your code only
> seems to fill one table. Where is the other one?
>
> ds is my dataset name.
> Customers is my parent table.
> Orders is my child table.
> CustomerID is the field in both tables that relates them
> to each other.
>
>>> 'now add the foreign key constraint
>>> Dim fk as ForeignKeyConstraint
>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>  ds.Tables("Orders").Columns("CustomerID"))
>
> If you still can't figure it out, please re-post your code
> showing the filling of the tables, probably in your form_load
> routine.
>
> And if you post more code, if you paste it into notepad and
> then copy and paste it into your posting, it will be
> indented properly and be easier to read.
>
> Robin S.
> -----------------------
>
> "max" <m**@max.max> wrote in message
> news:9dyih.1750$AA.434@tornado.fastwebnet.it...
>>
>> I'm sorry, but I really can't solve the problem.
>> I don't have any relationships defined in Access, neither I have set in
>> Dataset Designer.
>> Trying to use your code, I'm in trouble about connection string, this is
>> mine (I put the code in a Form_Load environment):
>> Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
>> I get an error in "Provider", it's not recognized by SqlDataAdapter.
>>
>> I have this code:
>>
>> Public Class Form_modPrest1
>> Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>> System.Object, ByVal e As System.EventArgs) Handles
>> TblPrest1BindingNavigatorSaveItem.Click
>> Me.Validate()
>> Me.TblPrest1BindingSource.EndEdit()
>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>> End Sub
>> Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e As
>> System.EventArgs) Handles MyBase.Load
>> Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)
>>
>> This cames automatically from dragging the tblPrest1 from dataset to the
>> form, in a datagrid format. I put your code under last line. And it
>> doesn't work.
>> Because I have other four tables identical to this, I get also five forms
>> identical, changing only the names of form (modPrest2...) and names of
>> tables (tblPrest2...)
>> Then I load these forms by a menu, and I see the contents of all tables
>> in datagrid format.
>> Then I make a change in the tblPrest1, and save the change by clicking in
>> the floppy disk icon. When I reload the table, changes are effectively in
>> use.
>> It seems this procedure already fill the table in dataset... or not?
>> I can't understand how automatically changing the other four tables
>> without calling each one by one.
>> I'm sorry for this but I'm really at the beginning about database
>> programming.
>> Please help and thanks for your patience.
>>
>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>> news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>>>
>>>
>>> Do you have no relationships defined between the tables
>>> in Access? Do they not show up in the Dataset Designer?
>>>
>>> I'm not going to swear this will work. But you could
>>> try adding your own foreign key constraints. I'm also not
>>> sure where you would do this, but I would guess *after* the
>>> dataset is filled, and *before* you do the binding. (I'm
>>> assuming you're doing the binding in code, but maybe not?)
>>>
>>> Here's an example on setting up a foreign key constraint.
>>> This is from David Sceppa's book ADO.Net 2.0 The Core
>>> Reference.
>>>
>>> This uses the Customers and Orders tables from the Northwind
>>> database.
>>>
>>> 'how to define a foreign key constraint
>>> 'first, fill your dataset with the two tables involved
>>> 'this is mine; this is loading from SQLServer, but
>>> '  load it however you're loading it from Access
>>> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
>>> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
>>> da.TableMappings.Add("Table","Customers")
>>> da.TableMappings.Add("Table1","Orders")
>>> Dim ds as New DataSet()
>>> da.Fill(ds)
>>>
>>> 'now add the foreign key constraint
>>> Dim fk as ForeignKeyConstraint
>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>
>>> Maybe if you play around with the code in this example,
>>> you can figure out some way to get it to work. If you want
>>> more help, you're going to need to post some code.
>>>
>>> Robin S.
>>> ----------------
>>> "max" <m**@max.max> wrote in message
>>> news:197ih.9490$BR1.7672@tornado.fastwebnet.it...
>>>>
>>>> Thanks for yor answer. I'm using VB2005, .Net 2.0.
>>>> Tables structure and data was developed in Access and loaded in a
>>>> dataset using the Data Source Configuration Wizard. Then I created a
>>>> form, dragged the table from the Dataset which creates a
>>>> bindingnavigator obj on top of form; a second form load this form by a
>>>> button and I correctly see the data. There are only three columns in
>>>> the tables: an incremental ID (I gave it primary key), a number, and a
>>>> name. Then I need to change, insert and delete records in the first
>>>> table and replicate this change in all the other four tables, that are
>>>> identical in structure and data. I can change and update data in the
>>>> first table (because I see that changes was really on it), but I cannot
>>>> replicate in the others. I have already tried to make relations using
>>>> Data Designer, but it doesn't work; I have read in some newsgroup that
>>>> ForeignKeyConstraint cannot be created by this way.
>>>> I'd like to understand where and how insert vb code to create
>>>> relationship between columns in the first table to all the others. I
>>>> can't realize in which vb module, vb code must be added.
>>>> Thanks for your attention and sorry for my English.
>>>> Max
>>>>
>>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>>> news:0e6dndYZibmr4xXYnZ2dnUVZ_ompnZ2d@comcast.com...
>>>>>
>>>>> What kind of database are you using? VB2003 or VB2005?
>>>>> .Net 1.1 or .Net 2.0?
>>>>>
>>>>> How are you filling your tables?
>>>>>
>>>>> Robin S.
>>>>> -------------------------------------
>>>>>
>>>>> "max" <m**@max.max> wrote in message
>>>>> news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>>>>>>
>>>>>> 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 ForeignKeyConstraint object.
>>>>>> I have created the tables using the DataSet Visual Tool and I know it
>>>>>> doesn't create any ForeignKeyConstraint 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.
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>>
>
>


"RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
news:ALWdnT5eXZlm6xbYnZ2dnUVZ_qSrnZ2d@comcast.com...
> Argh. Sorry; I didn't realize you were going to use my dataset
> code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
> But you don't need that stuff if you're using a DataSet that
> you set up in your DataSet designer; you only need the code defining
> the Foreign Key Restraint. I included the other just to show
> position.
>
> I'm assuming you have *two* datatables in your dataset, one
> being the parent and one being the child? Your code only
> seems to fill one table. Where is the other one?
>
> ds is my dataset name.
> Customers is my parent table.
> Orders is my child table.
> CustomerID is the field in both tables that relates them
> to each other.
>
>>> 'now add the foreign key constraint
>>> Dim fk as ForeignKeyConstraint
>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>  ds.Tables("Orders").Columns("CustomerID"))
>
> If you still can't figure it out, please re-post your code
> showing the filling of the tables, probably in your form_load
> routine.
>
> And if you post more code, if you paste it into notepad and
> then copy and paste it into your posting, it will be
> indented properly and be easier to read.
>
> Robin S.
> -----------------------
>
> "max" <m**@max.max> wrote in message
> news:9dyih.1750$AA.434@tornado.fastwebnet.it...
>>
>> I'm sorry, but I really can't solve the problem.
>> I don't have any relationships defined in Access, neither I have set in
>> Dataset Designer.
>> Trying to use your code, I'm in trouble about connection string, this is
>> mine (I put the code in a Form_Load environment):
>> Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
>> I get an error in "Provider", it's not recognized by SqlDataAdapter.
>>
>> I have this code:
>>
>> Public Class Form_modPrest1
>> Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>> System.Object, ByVal e As System.EventArgs) Handles
>> TblPrest1BindingNavigatorSaveItem.Click
>> Me.Validate()
>> Me.TblPrest1BindingSource.EndEdit()
>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>> End Sub
>> Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e As
>> System.EventArgs) Handles MyBase.Load
>> Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)
>>
>> This cames automatically from dragging the tblPrest1 from dataset to the
>> form, in a datagrid format. I put your code under last line. And it
>> doesn't work.
>> Because I have other four tables identical to this, I get also five forms
>> identical, changing only the names of form (modPrest2...) and names of
>> tables (tblPrest2...)
>> Then I load these forms by a menu, and I see the contents of all tables
>> in datagrid format.
>> Then I make a change in the tblPrest1, and save the change by clicking in
>> the floppy disk icon. When I reload the table, changes are effectively in
>> use.
>> It seems this procedure already fill the table in dataset... or not?
>> I can't understand how automatically changing the other four tables
>> without calling each one by one.
>> I'm sorry for this but I'm really at the beginning about database
>> programming.
>> Please help and thanks for your patience.
>>
>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>> news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>>>
>>>
>>> Do you have no relationships defined between the tables
>>> in Access? Do they not show up in the Dataset Designer?
>>>
>>> I'm not going to swear this will work. But you could
>>> try adding your own foreign key constraints. I'm also not
>>> sure where you would do this, but I would guess *after* the
>>> dataset is filled, and *before* you do the binding. (I'm
>>> assuming you're doing the binding in code, but maybe not?)
>>>
>>> Here's an example on setting up a foreign key constraint.
>>> This is from David Sceppa's book ADO.Net 2.0 The Core
>>> Reference.
>>>
>>> This uses the Customers and Orders tables from the Northwind
>>> database.
>>>
>>> 'how to define a foreign key constraint
>>> 'first, fill your dataset with the two tables involved
>>> 'this is mine; this is loading from SQLServer, but
>>> '  load it however you're loading it from Access
>>> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
>>> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
>>> da.TableMappings.Add("Table","Customers")
>>> da.TableMappings.Add("Table1","Orders")
>>> Dim ds as New DataSet()
>>> da.Fill(ds)
>>>
>>> 'now add the foreign key constraint
>>> Dim fk as ForeignKeyConstraint
>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>
>>> Maybe if you play around with the code in this example,
>>> you can figure out some way to get it to work. If you want
>>> more help, you're going to need to post some code.
>>>
>>> Robin S.
>>> ----------------
>>> "max" <m**@max.max> wrote in message
>>> news:197ih.9490$BR1.7672@tornado.fastwebnet.it...
>>>>
>>>> Thanks for yor answer. I'm using VB2005, .Net 2.0.
>>>> Tables structure and data was developed in Access and loaded in a
>>>> dataset using the Data Source Configuration Wizard. Then I created a
>>>> form, dragged the table from the Dataset which creates a
>>>> bindingnavigator obj on top of form; a second form load this form by a
>>>> button and I correctly see the data. There are only three columns in
>>>> the tables: an incremental ID (I gave it primary key), a number, and a
>>>> name. Then I need to change, insert and delete records in the first
>>>> table and replicate this change in all the other four tables, that are
>>>> identical in structure and data. I can change and update data in the
>>>> first table (because I see that changes was really on it), but I cannot
>>>> replicate in the others. I have already tried to make relations using
>>>> Data Designer, but it doesn't work; I have read in some newsgroup that
>>>> ForeignKeyConstraint cannot be created by this way.
>>>> I'd like to understand where and how insert vb code to create
>>>> relationship between columns in the first table to all the others. I
>>>> can't realize in which vb module, vb code must be added.
>>>> Thanks for your attention and sorry for my English.
>>>> Max
>>>>
>>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>>> news:0e6dndYZibmr4xXYnZ2dnUVZ_ompnZ2d@comcast.com...
>>>>>
>>>>> What kind of database are you using? VB2003 or VB2005?
>>>>> .Net 1.1 or .Net 2.0?
>>>>>
>>>>> How are you filling your tables?
>>>>>
>>>>> Robin S.
>>>>> -------------------------------------
>>>>>
>>>>> "max" <m**@max.max> wrote in message
>>>>> news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>>>>>>
>>>>>> 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 ForeignKeyConstraint object.
>>>>>> I have created the tables using the DataSet Visual Tool and I know it
>>>>>> doesn't create any ForeignKeyConstraint 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" <nobody@devnull.spamcop.net> wrote in message
news:458c656a$0$20818$5fc30a8@news.tiscali.it...
> Ok. I loaded in this form the secon table, too, and I fill it (I
> hope). Then
> nothing, the second table remians unchanged. I'm really getting crazy.
> This is the code:
>
> Public Class Form_modPrest1
>    Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
> System.Object, ByVal e As System.EventArgs) Handles
> TblPrest1BindingNavigatorSaveItem.Click
>        Me.Validate()
>        Me.TblPrest1BindingSource.EndEdit()
>        Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>    End Sub
>    Private Sub Form_modPrest1_Load(ByVal sender As System.Object,
> ByVal e
> As System.EventArgs) Handles MyBase.Load
>        Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet.tblPrest2)
>        'TODO: questa riga di codice carica i dati nella tabella
> 'GestPrestDataSet.tblPrest1'. È possibile spostarla o rimuoverla se
> necessario.
>        Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet.tblPrest1)
>        Dim fk As ForeignKeyConstraint
>        fk = New ForeignKeyConstraint("FK_tbl1_tbl2", _
>            GestPrestDataSet.Tables("tblPrest1").Columns("NomePrest"),
> _
>            GestPrestDataSet.Tables("tblPrest2").Columns("NomePrest"))
>    End Sub
> End Class
>
> Thanks Robin
>
> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
> news:ALWdnT5eXZlm6xbYnZ2dnUVZ_qSrnZ2d@comcast.com...
>> Argh. Sorry; I didn't realize you were going to use my dataset
>> code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
>> But you don't need that stuff if you're using a DataSet that
>> you set up in your DataSet designer; you only need the code defining
>> the Foreign Key Restraint. I included the other just to show
>> position.
>>
>> I'm assuming you have *two* datatables in your dataset, one
>> being the parent and one being the child? Your code only
>> seems to fill one table. Where is the other one?
>>
>> ds is my dataset name.
>> Customers is my parent table.
>> Orders is my child table.
>> CustomerID is the field in both tables that relates them
>> to each other.
>>
>>>> 'now add the foreign key constraint
>>>> Dim fk as ForeignKeyConstraint
>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>
>> If you still can't figure it out, please re-post your code
>> showing the filling of the tables, probably in your form_load
>> routine.
>>
>> And if you post more code, if you paste it into notepad and
>> then copy and paste it into your posting, it will be
>> indented properly and be easier to read.
>>
>> Robin S.
>> -----------------------
>>
>> "max" <m**@max.max> wrote in message
>> news:9dyih.1750$AA.434@tornado.fastwebnet.it...
>>>
>>> I'm sorry, but I really can't solve the problem.
>>> I don't have any relationships defined in Access, neither I have set
>>> in Dataset Designer.
>>> Trying to use your code, I'm in trouble about connection string,
>>> this is mine (I put the code in a Form_Load environment):
>>> Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
>>> I get an error in "Provider", it's not recognized by SqlDataAdapter.
>>>
>>> I have this code:
>>>
>>> Public Class Form_modPrest1
>>> Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>>> System.Object, ByVal e As System.EventArgs) Handles
>>> TblPrest1BindingNavigatorSaveItem.Click
>>> Me.Validate()
>>> Me.TblPrest1BindingSource.EndEdit()
>>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>>> End Sub
>>> Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal
>>> e As System.EventArgs) Handles MyBase.Load
>>> Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)
>>>
>>> This cames automatically from dragging the tblPrest1 from dataset to
>>> the form, in a datagrid format. I put your code under last line. And
>>> it doesn't work.
>>> Because I have other four tables identical to this, I get also five
>>> forms identical, changing only the names of form (modPrest2...) and
>>> names of tables (tblPrest2...)
>>> Then I load these forms by a menu, and I see the contents of all
>>> tables in datagrid format.
>>> Then I make a change in the tblPrest1, and save the change by
>>> clicking in the floppy disk icon. When I reload the table, changes
>>> are effectively in use.
>>> It seems this procedure already fill the table in dataset... or not?
>>> I can't understand how automatically changing the other four tables
>>> without calling each one by one.
>>> I'm sorry for this but I'm really at the beginning about database
>>> programming.
>>> Please help and thanks for your patience.
>>>
>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>> news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>>>>
>>>>
>>>> Do you have no relationships defined between the tables
>>>> in Access? Do they not show up in the Dataset Designer?
>>>>
>>>> I'm not going to swear this will work. But you could
>>>> try adding your own foreign key constraints. I'm also not
>>>> sure where you would do this, but I would guess *after* the
>>>> dataset is filled, and *before* you do the binding. (I'm
>>>> assuming you're doing the binding in code, but maybe not?)
>>>>
>>>> Here's an example on setting up a foreign key constraint.
>>>> This is from David Sceppa's book ADO.Net 2.0 The Core
>>>> Reference.
>>>>
>>>> This uses the Customers and Orders tables from the Northwind
>>>> database.
>>>>
>>>> 'how to define a foreign key constraint
>>>> 'first, fill your dataset with the two tables involved
>>>> 'this is mine; this is loading from SQLServer, but
>>>> '  load it however you're loading it from Access
>>>> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
>>>> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
>>>> da.TableMappings.Add("Table","Customers")
>>>> da.TableMappings.Add("Table1","Orders")
>>>> Dim ds as New DataSet()
>>>> da.Fill(ds)
>>>>
>>>> 'now add the foreign key constraint
>>>> Dim fk as ForeignKeyConstraint
>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>>
>>>> Maybe if you play around with the code in this example,
>>>> you can figure out some way to get it to work. If you want
>>>> more help, you're going to need to post some code.
>>>>
>>>> Robin S.
>>>> ----------------
>>>> "max" <m**@max.max> wrote in message
>>>> news:197ih.9490$BR1.7672@tornado.fastwebnet.it...
>>>>>
>>>>> Thanks for yor answer. I'm using VB2005, .Net 2.0.
>>>>> Tables structure and data was developed in Access and loaded in a
>>>>> dataset using the Data Source Configuration Wizard. Then I created
>>>>> a form, dragged the table from the Dataset which creates a
>>>>> bindingnavigator obj on top of form; a second form load this form
>>>>> by a button and I correctly see the data. There are only three
>>>>> columns in the tables: an incremental ID (I gave it primary key),
>>>>> a number, and a name. Then I need to change, insert and delete
>>>>> records in the first table and replicate this change in all the
>>>>> other four tables, that are identical in structure and data. I can
>>>>> change and update data in the first table (because I see that
>>>>> changes was really on it), but I cannot replicate in the others. I
>>>>> have already tried to make relations using Data Designer, but it
>>>>> doesn't work; I have read in some newsgroup that
>>>>> ForeignKeyConstraint cannot be created by this way.
>>>>> I'd like to understand where and how insert vb code to create
>>>>> relationship between columns in the first table to all the others.
>>>>> I can't realize in which vb module, vb code must be added.
>>>>> Thanks for your attention and sorry for my English.
>>>>> Max
>>>>>
>>>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>>>> news:0e6dndYZibmr4xXYnZ2dnUVZ_ompnZ2d@comcast.com...
>>>>>>
>>>>>> What kind of database are you using? VB2003 or VB2005?
>>>>>> .Net 1.1 or .Net 2.0?
>>>>>>
>>>>>> How are you filling your tables?
>>>>>>
>>>>>> Robin S.
>>>>>> -------------------------------------
>>>>>>
>>>>>> "max" <m**@max.max> wrote in message
>>>>>> news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>>>>>>>
>>>>>>> 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 ForeignKeyConstraint
>>>>>>> object. I have created the tables using the DataSet Visual Tool
>>>>>>> and I know it doesn't create any ForeignKeyConstraint 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.
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
> news:ALWdnT5eXZlm6xbYnZ2dnUVZ_qSrnZ2d@comcast.com...
>> Argh. Sorry; I didn't realize you were going to use my dataset
>> code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
>> But you don't need that stuff if you're using a DataSet that
>> you set up in your DataSet designer; you only need the code defining
>> the Foreign Key Restraint. I included the other just to show
>> position.
>>
>> I'm assuming you have *two* datatables in your dataset, one
>> being the parent and one being the child? Your code only
>> seems to fill one table. Where is the other one?
>>
>> ds is my dataset name.
>> Customers is my parent table.
>> Orders is my child table.
>> CustomerID is the field in both tables that relates them
>> to each other.
>>
>>>> 'now add the foreign key constraint
>>>> Dim fk as ForeignKeyConstraint
>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>
>> If you still can't figure it out, please re-post your code
>> showing the filling of the tables, probably in your form_load
>> routine.
>>
>> And if you post more code, if you paste it into notepad and
>> then copy and paste it into your posting, it will be
>> indented properly and be easier to read.
>>
>> Robin S.
>> -----------------------
>>
>> "max" <m**@max.max> wrote in message
>> news:9dyih.1750$AA.434@tornado.fastwebnet.it...
>>>
>>> I'm sorry, but I really can't solve the problem.
>>> I don't have any relationships defined in Access, neither I have set
>>> in Dataset Designer.
>>> Trying to use your code, I'm in trouble about connection string,
>>> this is mine (I put the code in a Form_Load environment):
>>> Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
>>> I get an error in "Provider", it's not recognized by SqlDataAdapter.
>>>
>>> I have this code:
>>>
>>> Public Class Form_modPrest1
>>> Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>>> System.Object, ByVal e As System.EventArgs) Handles
>>> TblPrest1BindingNavigatorSaveItem.Click
>>> Me.Validate()
>>> Me.TblPrest1BindingSource.EndEdit()
>>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>>> End Sub
>>> Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal
>>> e As System.EventArgs) Handles MyBase.Load
>>> Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)
>>>
>>> This cames automatically from dragging the tblPrest1 from dataset to
>>> the form, in a datagrid format. I put your code under last line. And
>>> it doesn't work.
>>> Because I have other four tables identical to this, I get also five
>>> forms identical, changing only the names of form (modPrest2...) and
>>> names of tables (tblPrest2...)
>>> Then I load these forms by a menu, and I see the contents of all
>>> tables in datagrid format.
>>> Then I make a change in the tblPrest1, and save the change by
>>> clicking in the floppy disk icon. When I reload the table, changes
>>> are effectively in use.
>>> It seems this procedure already fill the table in dataset... or not?
>>> I can't understand how automatically changing the other four tables
>>> without calling each one by one.
>>> I'm sorry for this but I'm really at the beginning about database
>>> programming.
>>> Please help and thanks for your patience.
>>>
>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>> news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>>>>
>>>>
>>>> Do you have no relationships defined between the tables
>>>> in Access? Do they not show up in the Dataset Designer?
>>>>
>>>> I'm not going to swear this will work. But you could
>>>> try adding your own foreign key constraints. I'm also not
>>>> sure where you would do this, but I would guess *after* the
>>>> dataset is filled, and *before* you do the binding. (I'm
>>>> assuming you're doing the binding in code, but maybe not?)
>>>>
>>>> Here's an example on setting up a foreign key constraint.
>>>> This is from David Sceppa's book ADO.Net 2.0 The Core
>>>> Reference.
>>>>
>>>> This uses the Customers and Orders tables from the Northwind
>>>> database.
>>>>
>>>> 'how to define a foreign key constraint
>>>> 'first, fill your dataset with the two tables involved
>>>> 'this is mine; this is loading from SQLServer, but
>>>> '  load it however you're loading it from Access
>>>> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
>>>> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
>>>> da.TableMappings.Add("Table","Customers")
>>>> da.TableMappings.Add("Table1","Orders")
>>>> Dim ds as New DataSet()
>>>> da.Fill(ds)
>>>>
>>>> 'now add the foreign key constraint
>>>> Dim fk as ForeignKeyConstraint
>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>>
>>>> Maybe if you play around with the code in this example,
>>>> you can figure out some way to get it to work. If you want
>>>> more help, you're going to need to post some code.
>>>>
>>>> Robin S.
>>>> ----------------
>>>> "max" <m**@max.max> wrote in message
>>>> news:197ih.9490$BR1.7672@tornado.fastwebnet.it...
>>>>>
>>>>> Thanks for yor answer. I'm using VB2005, .Net 2.0.
>>>>> Tables structure and data was developed in Access and loaded in a
>>>>> dataset using the Data Source Configuration Wizard. Then I created
>>>>> a form, dragged the table from the Dataset which creates a
>>>>> bindingnavigator obj on top of form; a second form load this form
>>>>> by a button and I correctly see the data. There are only three
>>>>> columns in the tables: an incremental ID (I gave it primary key),
>>>>> a number, and a name. Then I need to change, insert and delete
>>>>> records in the first table and replicate this change in all the
>>>>> other four tables, that are identical in structure and data. I can
>>>>> change and update data in the first table (because I see that
>>>>> changes was really on it), but I cannot replicate in the others. I
>>>>> have already tried to make relations using Data Designer, but it
>>>>> doesn't work; I have read in some newsgroup that
>>>>> ForeignKeyConstraint cannot be created by this way.
>>>>> I'd like to understand where and how insert vb code to create
>>>>> relationship between columns in the first table to all the others.
>>>>> I can't realize in which vb module, vb code must be added.
>>>>> Thanks for your attention and sorry for my English.
>>>>> Max
>>>>>
>>>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>>>> news:0e6dndYZibmr4xXYnZ2dnUVZ_ompnZ2d@comcast.com...
>>>>>>
>>>>>> What kind of database are you using? VB2003 or VB2005?
>>>>>> .Net 1.1 or .Net 2.0?
>>>>>>
>>>>>> How are you filling your tables?
>>>>>>
>>>>>> Robin S.
>>>>>> -------------------------------------
>>>>>>
>>>>>> "max" <m**@max.max> wrote in message
>>>>>> news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>>>>>>>
>>>>>>> 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 ForeignKeyConstraint
>>>>>>> object. I have created the tables using the DataSet Visual Tool
>>>>>>> and I know it doesn't create any ForeignKeyConstraint 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.
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
> news:ALWdnT5eXZlm6xbYnZ2dnUVZ_qSrnZ2d@comcast.com...
>> Argh. Sorry; I didn't realize you were going to use my dataset
>> code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
>> But you don't need that stuff if you're using a DataSet that
>> you set up in your DataSet designer; you only need the code defining
>> the Foreign Key Restraint. I included the other just to show
>> position.
>>
>> I'm assuming you have *two* datatables in your dataset, one
>> being the parent and one being the child? Your code only
>> seems to fill one table. Where is the other one?
>>
>> ds is my dataset name.
>> Customers is my parent table.
>> Orders is my child table.
>> CustomerID is the field in both tables that relates them
>> to each other.
>>
>>>> 'now add the foreign key constraint
>>>> Dim fk as ForeignKeyConstraint
>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>
>> If you still can't figure it out, please re-post your code
>> showing the filling of the tables, probably in your form_load
>> routine.
>>
>> And if you post more code, if you paste it into notepad and
>> then copy and paste it into your posting, it will be
>> indented properly and be easier to read.
>>
>> Robin S.
>> -----------------------
>>
>> "max" <m**@max.max> wrote in message
>> news:9dyih.1750$AA.434@tornado.fastwebnet.it...
>>>
>>> I'm sorry, but I really can't solve the problem.
>>> I don't have any relationships defined in Access, neither I have set
>>> in Dataset Designer.
>>> Trying to use your code, I'm in trouble about connection string,
>>> this is mine (I put the code in a Form_Load environment):
>>> Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
>>> I get an error in "Provider", it's not recognized by SqlDataAdapter.
>>>
>>> I have this code:
>>>
>>> Public Class Form_modPrest1
>>> Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>>> System.Object, ByVal e As System.EventArgs) Handles
>>> TblPrest1BindingNavigatorSaveItem.Click
>>> Me.Validate()
>>> Me.TblPrest1BindingSource.EndEdit()
>>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>>> End Sub
>>> Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal
>>> e As System.EventArgs) Handles MyBase.Load
>>> Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)
>>>
>>> This cames automatically from dragging the tblPrest1 from dataset to
>>> the form, in a datagrid format. I put your code under last line. And
>>> it doesn't work.
>>> Because I have other four tables identical to this, I get also five
>>> forms identical, changing only the names of form (modPrest2...) and
>>> names of tables (tblPrest2...)
>>> Then I load these forms by a menu, and I see the contents of all
>>> tables in datagrid format.
>>> Then I make a change in the tblPrest1, and save the change by
>>> clicking in the floppy disk icon. When I reload the table, changes
>>> are effectively in use.
>>> It seems this procedure already fill the table in dataset... or not?
>>> I can't understand how automatically changing the other four tables
>>> without calling each one by one.
>>> I'm sorry for this but I'm really at the beginning about database
>>> programming.
>>> Please help and thanks for your patience.
>>>
>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>> news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>>>>
>>>>
>>>> Do you have no relationships defined between the tables
>>>> in Access? Do they not show up in the Dataset Designer?
>>>>
>>>> I'm not going to swear this will work. But you could
>>>> try adding your own foreign key constraints. I'm also not
>>>> sure where you would do this, but I would guess *after* the
>>>> dataset is filled, and *before* you do the binding. (I'm
>>>> assuming you're doing the binding in code, but maybe not?)
>>>>
>>>> Here's an example on setting up a foreign key constraint.
>>>> This is from David Sceppa's book ADO.Net 2.0 The Core
>>>> Reference.
>>>>
>>>> This uses the Customers and Orders tables from the Northwind
>>>> database.
>>>>
>>>> 'how to define a foreign key constraint
>>>> 'first, fill your dataset with the two tables involved
>>>> 'this is mine; this is loading from SQLServer, but
>>>> '  load it however you're loading it from Access
>>>> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
>>>> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
>>>> da.TableMappings.Add("Table","Customers")
>>>> da.TableMappings.Add("Table1","Orders")
>>>> Dim ds as New DataSet()
>>>> da.Fill(ds)
>>>>
>>>> 'now add the foreign key constraint
>>>> Dim fk as ForeignKeyConstraint
>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>>
>>>> Maybe if you play around with the code in this example,
>>>> you can figure out some way to get it to work. If you want
>>>> more help, you're going to need to post some code.
>>>>
>>>> Robin S.
>>>> ----------------
>>>> "max" <m**@max.max> wrote in message
>>>> news:197ih.9490$BR1.7672@tornado.fastwebnet.it...
>>>>>
>>>>> Thanks for yor answer. I'm using VB2005, .Net 2.0.
>>>>> Tables structure and data was developed in Access and loaded in a
>>>>> dataset using the Data Source Configuration Wizard. Then I created
>>>>> a form, dragged the table from the Dataset which creates a
>>>>> bindingnavigator obj on top of form; a second form load this form
>>>>> by a button and I correctly see the data. There are only three
>>>>> columns in the tables: an incremental ID (I gave it primary key),
>>>>> a number, and a name. Then I need to change, insert and delete
>>>>> records in the first table and replicate this change in all the
>>>>> other four tables, that are identical in structure and data. I can
>>>>> change and update data in the first table (because I see that
>>>>> changes was really on it), but I cannot replicate in the others. I
>>>>> have already tried to make relations using Data Designer, but it
>>>>> doesn't work; I have read in some newsgroup that
>>>>> ForeignKeyConstraint cannot be created by this way.
>>>>> I'd like to understand where and how insert vb code to create
>>>>> relationship between columns in the first table to all the others.
>>>>> I can't realize in which vb module, vb code must be added.
>>>>> Thanks for your attention and sorry for my English.
>>>>> Max
>>>>>
>>>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>>>> news:0e6dndYZibmr4xXYnZ2dnUVZ_ompnZ2d@comcast.com...
>>>>>>
>>>>>> What kind of database are you using? VB2003 or VB2005?
>>>>>> .Net 1.1 or .Net 2.0?
>>>>>>
>>>>>> How are you filling your tables?
>>>>>>
>>>>>> Robin S.
>>>>>> -------------------------------------
>>>>>>
>>>>>> "max" <m**@max.max> wrote in message
>>>>>> news:GYUhh.8661$BR1.4348@tornado.fastwebnet.it...
>>>>>>>
>>>>>>> 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 ForeignKeyConstraint
>>>>>>> object. I have created the tables using the DataSet Visual Tool
>>>>>>> and I know it doesn't create any ForeignKeyConstraint 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.
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>

"RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
news:9LmdnQkTo77VExHYnZ2dnUVZ_tyinZ2d@comcast.com...
>I forgot to tell you to add the constraint to the table definition.
> Oops. Here's some code, with the line(s) I missed (look for the
> asterisks).
>
> Here's a working example. I set up a dataset for Northwind
> with Customers and Orders and deleted the constraint that was
> generated so I could create one in code. Customers corresponds
> to your table1; Orders corresponds to your table2.
>
> I didn't try modifying any of my data because I don't want to muck
> up my data, but it should work. (famous last words)
>
> ----------
> Dim ds As NorthwindDataSet = New NorthwindDataSet()
> Dim custAdapter As CustomersTableAdapter = New CustomersTableAdapter()
> custAdapter.Fill(ds.Customers)
> Dim orderAdapter As OrdersTableAdapter = New OrdersTableAdapter()
> orderAdapter.Fill(ds.Orders)
>
> Dim fk As ForeignKeyConstraint
> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>  ds.Tables("Customers").Columns("CustomerID"), _
>  ds.Tables("Orders").Columns("CustomerID"))
>
> '***THIS IS THE STEP I FORGOT TO INCLUDE -- Oops!
> ds.Tables("Orders").Constraints.Add(fk)
> ds.EnforceConstraints = True
>
> 'Note: You can use the data relation created by the fk
> '  constraint to read through your data:
>
> Dim rowCustomer As NorthwindDataSet.CustomersRow
> Dim rowOrder As NorthwindDataSet.OrdersRow
> Dim rel As DataRelation = ds.Relations("FK_Customers_Orders")
> For Each rowCustomer In ds.Customers
>    Console.WriteLine("Orders for {0}", rowCustomer.CompanyName)
>    For Each rowOrder In rowCustomer.GetChildRows(rel)
>        Console.WriteLine("    {0} - {1:d}", rowOrder.OrderID,
> rowOrder.OrderDate)
>    Next rowOrder
> Next rowCustomer
>
> And here's something else I figured out: You *can* add the constraint
> to your DataSet in the DataSet Designer.
>
> To add a DataRelation, right-click on any item in the DataSet designer
> (like in your table definition) and select Add and then Relation.
>
> You can select the parent and child tables using the drop-down lists
> at the top of the dialog box, and then the datacolumns just below
> that.
>
> The lower half of that dialog box lets you control whether the action
> will create a DataRelation, a ForeignKeyConstraint, or both. The
> default is just DataRelation, but you should choose ForeignKey or
> Both. You can also specify values for the UpdateRule, DeleteRule,
> and AcceptRejectChangesRule on the FK constraint. It defaults to
> Cascade, Cascade, and None, which is fine.
>
> If you have all of your tables in one DataSet that you created
> through the DAtaSet designer, you can try adding the constraints
> there. If you ever re-create the DataSet, you have to remember
> to add the constraints, too, though.
>
> Hope this helps.
> Robin S.
> -----------------------------------------------------------------
> "Max" <nobody@devnull.spamcop.net> wrote in message
> news:458c656a$0$20818$5fc30a8@news.tiscali.it...
>> Ok. I loaded in this form the secon table, too, and I fill it (I hope).
>> Then
>> nothing, the second table remians unchanged. I'm really getting crazy.
>> This is the code:
>>
>> Public Class Form_modPrest1
>>    Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>> System.Object, ByVal e As System.EventArgs) Handles
>> TblPrest1BindingNavigatorSaveItem.Click
>>        Me.Validate()
>>        Me.TblPrest1BindingSource.EndEdit()
>>        Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>>    End Sub
>>    Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e
>> As System.EventArgs) Handles MyBase.Load
>>        Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet.tblPrest2)
>>        'TODO: questa riga di codice carica i dati nella tabella
>> 'GestPrestDataSet.tblPrest1'. È possibile spostarla o rimuoverla se
>> necessario.
>>        Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet.tblPrest1)
>>        Dim fk As ForeignKeyConstraint
>>        fk = New ForeignKeyConstraint("FK_tbl1_tbl2", _
>>            GestPrestDataSet.Tables("tblPrest1").Columns("NomePrest"), _
>>            GestPrestDataSet.Tables("tblPrest2").Columns("NomePrest"))
>>    End Sub
>> End Class
>>
>> Thanks Robin
>>
>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>> news:ALWdnT5eXZlm6xbYnZ2dnUVZ_qSrnZ2d@comcast.com...
>>> Argh. Sorry; I didn't realize you were going to use my dataset
>>> code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
>>> But you don't need that stuff if you're using a DataSet that
>>> you set up in your DataSet designer; you only need the code defining
>>> the Foreign Key Restraint. I included the other just to show
>>> position.
>>>
>>> I'm assuming you have *two* datatables in your dataset, one
>>> being the parent and one being the child? Your code only
>>> seems to fill one table. Where is the other one?
>>>
>>> ds is my dataset name.
>>> Customers is my parent table.
>>> Orders is my child table.
>>> CustomerID is the field in both tables that relates them
>>> to each other.
>>>
>>>>> 'now add the foreign key constraint
>>>>> Dim fk as ForeignKeyConstraint
>>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>
>>> If you still can't figure it out, please re-post your code
>>> showing the filling of the tables, probably in your form_load
>>> routine.
>>>
>>> And if you post more code, if you paste it into notepad and
>>> then copy and paste it into your posting, it will be
>>> indented properly and be easier to read.
>>>
>>> Robin S.
>>> -----------------------
>>>
>>> "max" <m**@max.max> wrote in message
>>> news:9dyih.1750$AA.434@tornado.fastwebnet.it...
>>>>
>>>> I'm sorry, but I really can't solve the problem.
>>>> I don't have any relationships defined in Access, neither I have set in
>>>> Dataset Designer.
>>>> Trying to use your code, I'm in trouble about connection string, this
>>>> is mine (I put the code in a Form_Load environment):
>>>> Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
>>>> I get an error in "Provider", it's not recognized by SqlDataAdapter.
>>>>
>>>> I have this code:
>>>>
>>>> Public Class Form_modPrest1
>>>> Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>>>> System.Object, ByVal e As System.EventArgs) Handles
>>>> TblPrest1BindingNavigatorSaveItem.Click
>>>> Me.Validate()
>>>> Me.TblPrest1BindingSource.EndEdit()
>>>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>>>> End Sub
>>>> Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal e
>>>> As System.EventArgs) Handles MyBase.Load
>>>> Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)
>>>>
>>>> This cames automatically from dragging the tblPrest1 from dataset to
>>>> the form, in a datagrid format. I put your code under last line. And it
>>>> doesn't work.
>>>> Because I have other four tables identical to this, I get also five
>>>> forms identical, changing only the names of form (modPrest2...) and
>>>> names of tables (tblPrest2...)
>>>> Then I load these forms by a menu, and I see the contents of all tables
>>>> in datagrid format.
>>>> Then I make a change in the tblPrest1, and save the change by clicking
>>>> in the floppy disk icon. When I reload the table, changes are
>>>> effectively in use.
>>>> It seems this procedure already fill the table in dataset... or not?
>>>> I can't understand how automatically changing the other four tables
>>>> without calling each one by one.
>>>> I'm sorry for this but I'm really at the beginning about database
>>>> programming.
>>>> Please help and thanks for your patience.
>>>>
>>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>>> news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>>>>>
>>>>>
>>>>> Do you have no relationships defined between the tables
>>>>> in Access? Do they not show up in the Dataset Designer?
>>>>>
>>>>> I'm not going to swear this will work. But you could
>>>>> try adding your own foreign key constraints. I'm also not
>>>>> sure where you would do this, but I would guess *after* the
>>>>> dataset is filled, and *before* you do the binding. (I'm
>>>>> assuming you're doing the binding in code, but maybe not?)
>>>>>
>>>>> Here's an example on setting up a foreign key constraint.
>>>>> This is from David Sceppa's book ADO.Net 2.0 The Core
>>>>> Reference.
>>>>>
>>>>> This uses the Customers and Orders tables from the Northwind
>>>>> database.
>>>>>
>>>>> 'how to define a foreign key constraint
>>>>> 'first, fill your dataset with the two tables involved
>>>>> 'this is mine; this is loading from SQLServer, but
>>>>> '  load it however you're loading it from Access
>>>>> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
>>>>> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
>>>>> da.TableMappings.Add("Table","Customers")
>>>>> da.TableMappings.Add("Table1","Orders")
>>>>> Dim ds as New DataSet()
>>>>> da.Fill(ds)
>>>>>
>>>>> 'now add the foreign key constraint
>>>>> Dim fk as ForeignKeyConstraint
>>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>>>
>>>>> Maybe if you play around with the code in this example,
>>>>> you can figure out some way to get it to work. If you want
>>>>> more help, you're going to need to post some code.
>>>>>
>>>>> Robin S.

"Max" <nobody@devnull.spamcop.net> wrote in message
news:458dc7e6$0$20814$5fc30a8@news.tiscali.it...
> I'm still trying Robin, but seems I cannot fill the second tabel after
> implementing your code in my form_load. I see it after the
> console.writeline routine. Data are present only in 'tblPrest1' tabel,
> while, moving mouse around the console window, I see 'nothing' when I
> go to examinate tblPrest2' table. In effect, the program skips the
> section regarding 'each rowTblPrest2...', because the value is
> 'nothing'. But I can't understand why.
> Anyway, I used the DataSet designer as the very first time to create
> relationships, but it didn't work; I didn't get error messages, just
> no change in 'tblPrest2'. Later, looking in newsgroup, I found
> somebody talking about it's not possible to create it in this **
> friendly ** way, but only by writing code...
> Thanks. I'll let you know.
> PS: Are you looking at the newsgroup also in these 'holidays days'?
>
> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
> news:9LmdnQkTo77VExHYnZ2dnUVZ_tyinZ2d@comcast.com...
>>I forgot to tell you to add the constraint to the table definition.
>> Oops. Here's some code, with the line(s) I missed (look for the
>> asterisks).
>>
>> Here's a working example. I set up a dataset for Northwind
>> with Customers and Orders and deleted the constraint that was
>> generated so I could create one in code. Customers corresponds
>> to your table1; Orders corresponds to your table2.
>>
>> I didn't try modifying any of my data because I don't want to muck
>> up my data, but it should work. (famous last words)
>>
>> ----------
>> Dim ds As NorthwindDataSet = New NorthwindDataSet()
>> Dim custAdapter As CustomersTableAdapter = New
>> CustomersTableAdapter()
>> custAdapter.Fill(ds.Customers)
>> Dim orderAdapter As OrdersTableAdapter = New OrdersTableAdapter()
>> orderAdapter.Fill(ds.Orders)
>>
>> Dim fk As ForeignKeyConstraint
>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>  ds.Tables("Customers").Columns("CustomerID"), _
>>  ds.Tables("Orders").Columns("CustomerID"))
>>
>> '***THIS IS THE STEP I FORGOT TO INCLUDE -- Oops!
>> ds.Tables("Orders").Constraints.Add(fk)
>> ds.EnforceConstraints = True
>>
>> 'Note: You can use the data relation created by the fk
>> '  constraint to read through your data:
>>
>> Dim rowCustomer As NorthwindDataSet.CustomersRow
>> Dim rowOrder As NorthwindDataSet.OrdersRow
>> Dim rel As DataRelation = ds.Relations("FK_Customers_Orders")
>> For Each rowCustomer In ds.Customers
>>    Console.WriteLine("Orders for {0}", rowCustomer.CompanyName)
>>    For Each rowOrder In rowCustomer.GetChildRows(rel)
>>        Console.WriteLine("    {0} - {1:d}", rowOrder.OrderID,
>> rowOrder.OrderDate)
>>    Next rowOrder
>> Next rowCustomer
>>
>> And here's something else I figured out: You *can* add the constraint
>> to your DataSet in the DataSet Designer.
>>
>> To add a DataRelation, right-click on any item in the DataSet
>> designer
>> (like in your table definition) and select Add and then Relation.
>>
>> You can select the parent and child tables using the drop-down lists
>> at the top of the dialog box, and then the datacolumns just below
>> that.
>>
>> The lower half of that dialog box lets you control whether the action
>> will create a DataRelation, a ForeignKeyConstraint, or both. The
>> default is just DataRelation, but you should choose ForeignKey or
>> Both. You can also specify values for the UpdateRule, DeleteRule,
>> and AcceptRejectChangesRule on the FK constraint. It defaults to
>> Cascade, Cascade, and None, which is fine.
>>
>> If you have all of your tables in one DataSet that you created
>> through the DAtaSet designer, you can try adding the constraints
>> there. If you ever re-create the DataSet, you have to remember
>> to add the constraints, too, though.
>>
>> Hope this helps.
>> Robin S.
>> -----------------------------------------------------------------
>> "Max" <nobody@devnull.spamcop.net> wrote in message
>> news:458c656a$0$20818$5fc30a8@news.tiscali.it...
>>> Ok. I loaded in this form the secon table, too, and I fill it (I
>>> hope). Then
>>> nothing, the second table remians unchanged. I'm really getting
>>> crazy.
>>> This is the code:
>>>
>>> Public Class Form_modPrest1
>>>    Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender
>>> As
>>> System.Object, ByVal e As System.EventArgs) Handles
>>> TblPrest1BindingNavigatorSaveItem.Click
>>>        Me.Validate()
>>>        Me.TblPrest1BindingSource.EndEdit()
>>>
>>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>>>    End Sub
>>>    Private Sub Form_modPrest1_Load(ByVal sender As System.Object,
>>> ByVal e
>>> As System.EventArgs) Handles MyBase.Load
>>>        Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet.tblPrest2)
>>>        'TODO: questa riga di codice carica i dati nella tabella
>>> 'GestPrestDataSet.tblPrest1'. È possibile spostarla o rimuoverla se
>>> necessario.
>>>        Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet.tblPrest1)
>>>        Dim fk As ForeignKeyConstraint
>>>        fk = New ForeignKeyConstraint("FK_tbl1_tbl2", _
>>>
>>> GestPrestDataSet.Tables("tblPrest1").Columns("NomePrest"), _
>>>
>>> GestPrestDataSet.Tables("tblPrest2").Columns("NomePrest"))
>>>    End Sub
>>> End Class
>>>
>>> Thanks Robin
>>>
>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>> news:ALWdnT5eXZlm6xbYnZ2dnUVZ_qSrnZ2d@comcast.com...
>>>> Argh. Sorry; I didn't realize you were going to use my dataset
>>>> code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
>>>> But you don't need that stuff if you're using a DataSet that
>>>> you set up in your DataSet designer; you only need the code
>>>> defining
>>>> the Foreign Key Restraint. I included the other just to show
>>>> position.
>>>>
>>>> I'm assuming you have *two* datatables in your dataset, one
>>>> being the parent and one being the child? Your code only
>>>> seems to fill one table. Where is the other one?
>>>>
>>>> ds is my dataset name.
>>>> Customers is my parent table.
>>>> Orders is my child table.
>>>> CustomerID is the field in both tables that relates them
>>>> to each other.
>>>>
>>>>>> 'now add the foreign key constraint
>>>>>> Dim fk as ForeignKeyConstraint
>>>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>>
>>>> If you still can't figure it out, please re-post your code
>>>> showing the filling of the tables, probably in your form_load
>>>> routine.
>>>>
>>>> And if you post more code, if you paste it into notepad and
>>>> then copy and paste it into your posting, it will be
>>>> indented properly and be easier to read.
>>>>
>>>> Robin S.
>>>> -----------------------
>>>>
>>>> "max" <m**@max.max> wrote in message
>>>> news:9dyih.1750$AA.434@tornado.fastwebnet.it...
>>>>>
>>>>> I'm sorry, but I really can't solve the problem.
>>>>> I don't have any relationships defined in Access, neither I have
>>>>> set in Dataset Designer.
>>>>> Trying to use your code, I'm in trouble about connection string,
>>>>> this is mine (I put the code in a Form_Load environment):
>>>>> Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
>>>>> I get an error in "Provider", it's not recognized by
>>>>> SqlDataAdapter.
>>>>>
>>>>> I have this code:
>>>>>
>>>>> Public Class Form_modPrest1
>>>>> Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender
>>>>> As System.Object, ByVal e As System.EventArgs) Handles
>>>>> TblPrest1BindingNavigatorSaveItem.Click
>>>>> Me.Validate()
>>>>> Me.TblPrest1BindingSource.EndEdit()
>>>>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>>>>> End Sub
>>>>> Private Sub Form_modPrest1_Load(ByVal sender As System.Object,
>>>>> ByVal e As System.EventArgs) Handles MyBase.Load
>>>>> Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)
>>>>>
>>>>> This cames automatically from dragging the tblPrest1 from dataset
>>>>> to the form, in a datagrid format. I put your code under last
>>>>> line. And it doesn't work.
>>>>> Because I have other four tables identical to this, I get also
>>>>> five forms identical, changing only the names of form
>>>>> (modPrest2...) and names of tables (tblPrest2...)
>>>>> Then I load these forms by a menu, and I see the contents of all
>>>>> tables in datagrid format.
>>>>> Then I make a change in the tblPrest1, and save the change by
>>>>> clicking in the floppy disk icon. When I reload the table, changes
>>>>> are effectively in use.
>>>>> It seems this procedure already fill the table in dataset... or
>>>>> not?
>>>>> I can't understand how automatically changing the other four
>>>>> tables without calling each one by one.
>>>>> I'm sorry for this but I'm really at the beginning about database
>>>>> programming.
>>>>> Please help and thanks for your patience.
>>>>>
>>>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>>>> news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>>>>>>
>>>>>>
>>>>>> Do you have no relationships defined between the tables
>>>>>> in Access? Do they not show up in the Dataset Designer?
>>>>>>
>>>>>> I'm not going to swear this will work. But you could
>>>>>> try adding your own foreign key constraints. I'm also not
>>>>>> sure where you would do this, but I would guess *after* the
>>>>>> dataset is filled, and *before* you do the binding. (I'm
>>>>>> assuming you're doing the binding in code, but maybe not?)
>>>>>>
>>>>>> Here's an example on setting up a foreign key constraint.
>>>>>> This is from David Sceppa's book ADO.Net 2.0 The Core
>>>>>> Reference.
>>>>>>
>>>>>> This uses the Customers and Orders tables from the Northwind
>>>>>> database.
>>>>>>
>>>>>> 'how to define a foreign key constraint
>>>>>> 'first, fill your dataset with the two tables involved
>>>>>> 'this is mine; this is loading from SQLServer, but
>>>>>> '  load it however you're loading it from Access
>>>>>> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM
>>>>>> Orders"
>>>>>> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
>>>>>> da.TableMappings.Add("Table","Customers")
>>>>>> da.TableMappings.Add("Table1","Orders")
>>>>>> Dim ds as New DataSet()
>>>>>> da.Fill(ds)
>>>>>>
>>>>>> 'now add the foreign key constraint
>>>>>> Dim fk as ForeignKeyConstraint
>>>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>>>>
>>>>>> Maybe if you play around with the code in this example,
>>>>>> you can figure out some way to get it to work. If you want
>>>>>> more help, you're going to need to post some code.
>>>>>>
>>>>>> Robin S.
>
>

"RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
news:jYKdnfMnB4syRhDYnZ2dnUVZ_tunnZ2d@comcast.com...
>I don't understand why you can't fill both tables, either. Are you
> filling both tables *before* adding the constraint?
>
> One thing is, it's a *constraint*, so every entry in the child table
> *must* have a corresponding entry in the parent table. IS that true?
>
> Yes, I'll be around during the holidays.
> Robin S.
> ---------------------------------
>
>
> "Max" <nobody@devnull.spamcop.net> wrote in message
> news:458dc7e6$0$20814$5fc30a8@news.tiscali.it...
>> I'm still trying Robin, but seems I cannot fill the second tabel after
>> implementing your code in my form_load. I see it after the
>> console.writeline routine. Data are present only in 'tblPrest1' tabel,
>> while, moving mouse around the console window, I see 'nothing' when I go
>> to examinate tblPrest2' table. In effect, the program skips the section
>> regarding 'each rowTblPrest2...', because the value is 'nothing'. But I
>> can't understand why.
>> Anyway, I used the DataSet designer as the very first time to create
>> relationships, but it didn't work; I didn't get error messages, just no
>> change in 'tblPrest2'. Later, looking in newsgroup, I found somebody
>> talking about it's not possible to create it in this ** friendly ** way,
>> but only by writing code...
>> Thanks. I'll let you know.
>> PS: Are you looking at the newsgroup also in these 'holidays days'?
>>
>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>> news:9LmdnQkTo77VExHYnZ2dnUVZ_tyinZ2d@comcast.com...
>>>I forgot to tell you to add the constraint to the table definition.
>>> Oops. Here's some code, with the line(s) I missed (look for the
>>> asterisks).
>>>
>>> Here's a working example. I set up a dataset for Northwind
>>> with Customers and Orders and deleted the constraint that was
>>> generated so I could create one in code. Customers corresponds
>>> to your table1; Orders corresponds to your table2.
>>>
>>> I didn't try modifying any of my data because I don't want to muck
>>> up my data, but it should work. (famous last words)
>>>
>>> ----------
>>> Dim ds As NorthwindDataSet = New NorthwindDataSet()
>>> Dim custAdapter As CustomersTableAdapter = New CustomersTableAdapter()
>>> custAdapter.Fill(ds.Customers)
>>> Dim orderAdapter As OrdersTableAdapter = New OrdersTableAdapter()
>>> orderAdapter.Fill(ds.Orders)
>>>
>>> Dim fk As ForeignKeyConstraint
>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>
>>> '***THIS IS THE STEP I FORGOT TO INCLUDE -- Oops!
>>> ds.Tables("Orders").Constraints.Add(fk)
>>> ds.EnforceConstraints = True
>>>
>>> 'Note: You can use the data relation created by the fk
>>> '  constraint to read through your data:
>>>
>>> Dim rowCustomer As NorthwindDataSet.CustomersRow
>>> Dim rowOrder As NorthwindDataSet.OrdersRow
>>> Dim rel As DataRelation = ds.Relations("FK_Customers_Orders")
>>> For Each rowCustomer In ds.Customers
>>>    Console.WriteLine("Orders for {0}", rowCustomer.CompanyName)
>>>    For Each rowOrder In rowCustomer.GetChildRows(rel)
>>>        Console.WriteLine("    {0} - {1:d}", rowOrder.OrderID,
>>> rowOrder.OrderDate)
>>>    Next rowOrder
>>> Next rowCustomer
>>>
>>> And here's something else I figured out: You *can* add the constraint
>>> to your DataSet in the DataSet Designer.
>>>
>>> To add a DataRelation, right-click on any item in the DataSet designer
>>> (like in your table definition) and select Add and then Relation.
>>>
>>> You can select the parent and child tables using the drop-down lists
>>> at the top of the dialog box, and then the datacolumns just below
>>> that.
>>>
>>> The lower half of that dialog box lets you control whether the action
>>> will create a DataRelation, a ForeignKeyConstraint, or both. The
>>> default is just DataRelation, but you should choose ForeignKey or
>>> Both. You can also specify values for the UpdateRule, DeleteRule,
>>> and AcceptRejectChangesRule on the FK constraint. It defaults to
>>> Cascade, Cascade, and None, which is fine.
>>>
>>> If you have all of your tables in one DataSet that you created
>>> through the DAtaSet designer, you can try adding the constraints
>>> there. If you ever re-create the DataSet, you have to remember
>>> to add the constraints, too, though.
>>>
>>> Hope this helps.
>>> Robin S.
>>> -----------------------------------------------------------------
>>> "Max" <nobody@devnull.spamcop.net> wrote in message
>>> news:458c656a$0$20818$5fc30a8@news.tiscali.it...
>>>> Ok. I loaded in this form the secon table, too, and I fill it (I hope).
>>>> Then
>>>> nothing, the second table remians unchanged. I'm really getting crazy.
>>>> This is the code:
>>>>
>>>> Public Class Form_modPrest1
>>>>    Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>>>> System.Object, ByVal e As System.EventArgs) Handles
>>>> TblPrest1BindingNavigatorSaveItem.Click
>>>>        Me.Validate()
>>>>        Me.TblPrest1BindingSource.EndEdit()
>>>>
>>>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>>>>    End Sub
>>>>    Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal
>>>> e
>>>> As System.EventArgs) Handles MyBase.Load
>>>>        Me.TblPrest2TableAdapter.Fill(Me.GestPrestDataSet.tblPrest2)
>>>>        'TODO: questa riga di codice carica i dati nella tabella
>>>> 'GestPrestDataSet.tblPrest1'. È possibile spostarla o rimuoverla se
>>>> necessario.
>>>>        Me.TblPrest1TableAdapter.Fill(Me.GestPrestDataSet.tblPrest1)
>>>>        Dim fk As ForeignKeyConstraint
>>>>        fk = New ForeignKeyConstraint("FK_tbl1_tbl2", _
>>>>
>>>> GestPrestDataSet.Tables("tblPrest1").Columns("NomePrest"), _
>>>>
>>>> GestPrestDataSet.Tables("tblPrest2").Columns("NomePrest"))
>>>>    End Sub
>>>> End Class
>>>>
>>>> Thanks Robin
>>>>
>>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>>> news:ALWdnT5eXZlm6xbYnZ2dnUVZ_qSrnZ2d@comcast.com...
>>>>> Argh. Sorry; I didn't realize you were going to use my dataset
>>>>> code. You need to use the OleDBDataAdapter, not the SqlDataAdapter.
>>>>> But you don't need that stuff if you're using a DataSet that
>>>>> you set up in your DataSet designer; you only need the code defining
>>>>> the Foreign Key Restraint. I included the other just to show
>>>>> position.
>>>>>
>>>>> I'm assuming you have *two* datatables in your dataset, one
>>>>> being the parent and one being the child? Your code only
>>>>> seems to fill one table. Where is the other one?
>>>>>
>>>>> ds is my dataset name.
>>>>> Customers is my parent table.
>>>>> Orders is my child table.
>>>>> CustomerID is the field in both tables that relates them
>>>>> to each other.
>>>>>
>>>>>>> 'now add the foreign key constraint
>>>>>>> Dim fk as ForeignKeyConstraint
>>>>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>>>
>>>>> If you still can't figure it out, please re-post your code
>>>>> showing the filling of the tables, probably in your form_load
>>>>> routine.
>>>>>
>>>>> And if you post more code, if you paste it into notepad and
>>>>> then copy and paste it into your posting, it will be
>>>>> indented properly and be easier to read.
>>>>>
>>>>> Robin S.
>>>>> -----------------------
>>>>>
>>>>> "max" <m**@max.max> wrote in message
>>>>> news:9dyih.1750$AA.434@tornado.fastwebnet.it...
>>>>>>
>>>>>> I'm sorry, but I really can't solve the problem.
>>>>>> I don't have any relationships defined in Access, neither I have set
>>>>>> in Dataset Designer.
>>>>>> Trying to use your code, I'm in trouble about connection string, this
>>>>>> is mine (I put the code in a Form_Load environment):
>>>>>> Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\xxx\yyy.mdb"
>>>>>> I get an error in "Provider", it's not recognized by SqlDataAdapter.
>>>>>>
>>>>>> I have this code:
>>>>>>
>>>>>> Public Class Form_modPrest1
>>>>>> Private Sub TblPrest1BindingNavigatorSaveItem_Click(ByVal sender As
>>>>>> System.Object, ByVal e As System.EventArgs) Handles
>>>>>> TblPrest1BindingNavigatorSaveItem.Click
>>>>>> Me.Validate()
>>>>>> Me.TblPrest1BindingSource.EndEdit()
>>>>>> Me.TblPrest1TableAdapter.Update(Me.GestPrestDataSet.tblPrest1)
>>>>>> End Sub
>>>>>> Private Sub Form_modPrest1_Load(ByVal sender As System.Object, ByVal
>>>>>> e As System.EventArgs) Handles MyBase.Load
>>>>>> Me.TblPrest1TableAdapter.Fill(Me.myDataSet.tblPrest1)
>>>>>>
>>>>>> This cames automatically from dragging the tblPrest1 from dataset to
>>>>>> the form, in a datagrid format. I put your code under last line. And
>>>>>> it doesn't work.
>>>>>> Because I have other four tables identical to this, I get also five
>>>>>> forms identical, changing only the names of form (modPrest2...) and
>>>>>> names of tables (tblPrest2...)
>>>>>> Then I load these forms by a menu, and I see the contents of all
>>>>>> tables in datagrid format.
>>>>>> Then I make a change in the tblPrest1, and save the change by
>>>>>> clicking in the floppy disk icon. When I reload the table, changes
>>>>>> are effectively in use.
>>>>>> It seems this procedure already fill the table in dataset... or not?
>>>>>> I can't understand how automatically changing the other four tables
>>>>>> without calling each one by one.
>>>>>> I'm sorry for this but I'm really at the beginning about database
>>>>>> programming.
>>>>>> Please help and thanks for your patience.
>>>>>>
>>>>>> "RobinS" <RobinS@NoSpam.yah.none> ha scritto nel messaggio
>>>>>> news:p6Sdnc3or7CipxfYnZ2dnUVZ_q6vnZ2d@comcast.com...
>>>>>>>
>>>>>>>
>>>>>>> Do you have no relationships defined between the tables
>>>>>>> in Access? Do they not show up in the Dataset Designer?
>>>>>>>
>>>>>>> I'm not going to swear this will work. But you could
>>>>>>> try adding your own foreign key constraints. I'm also not
>>>>>>> sure where you would do this, but I would guess *after* the
>>>>>>> dataset is filled, and *before* you do the binding. (I'm
>>>>>>> assuming you're doing the binding in code, but maybe not?)
>>>>>>>
>>>>>>> Here's an example on setting up a foreign key constraint.
>>>>>>> This is from David Sceppa's book ADO.Net 2.0 The Core
>>>>>>> Reference.
>>>>>>>
>>>>>>> This uses the Customers and Orders tables from the Northwind
>>>>>>> database.
>>>>>>>
>>>>>>> 'how to define a foreign key constraint
>>>>>>> 'first, fill your dataset with the two tables involved
>>>>>>> 'this is mine; this is loading from SQLServer, but
>>>>>>> '  load it however you're loading it from Access
>>>>>>> Dim ss As String = "SELECT * FROM Customers; SELECT * FROM Orders"
>>>>>>> Dim da As New SqlDataAdapter(ss, cs)  'cs is my connectionstring
>>>>>>> da.TableMappings.Add("Table","Customers")
>>>>>>> da.TableMappings.Add("Table1","Orders")
>>>>>>> Dim ds as New DataSet()
>>>>>>> da.Fill(ds)
>>>>>>>
>>>>>>> 'now add the foreign key constraint
>>>>>>> Dim fk as ForeignKeyConstraint
>>>>>>> fk = New ForeignKeyConstraint("FK_Customers_Orders", _
>>>>>>>  ds.Tables("Customers").Columns("CustomerID"), _
>>>>>>>  ds.Tables("Orders").Columns("CustomerID"))
>>>>>>>
>>>>>>> Maybe if you play around with the code in this example,
>>>>>>> you can figure out some way to get it to work. If you want
>>>>>>> more help, you're going to need to post some code.
>>>>>>>
>>>>>>> Robin S.
>>
>>
>
>

"Branco Medeiros" <branco.medei***@gmail.com> ha scritto nel messaggio
news:1167095025.721726.294610@n51g2000cwc.googlegroups.com...
> max wrote:
>> 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 ForeignKeyConstraint object. I
>> have
>> created the tables using the DataSet Visual Tool and I know it doesn't
>> create any ForeignKeyConstraint obj. I have found many codes examples on
>> it,
>> but I don't know how to merge the code they in an existing dataset.
> <snip>
>
> I really didn't get what you're trying. And it seems you're mistaking
> what a foreign key really is.
>
>>From what I got you want five tables identical to each other that get
> updated when one of then is updated. This design seems very odd to me
> (why one would want five identical tables in the same database?), but I
> assume you have your reasons. It's this, or I completely misunderstood
> the layout of the tables -- for instance, they may be related somehow,
> in a way you didn't describe (or I failled to understand).
>
> Anyway, a foreign key constraint won't give exectly what you want.
>
> A foreign key (or FK) is a column in a table that holds *primary keys*
> of another table. This way you become assured that the foreign element
> really exists in the other table (also called foreign, or parent
> table).
>
> Consider for example a table B, with a column IDB (it's primary key, or
> PK) and other columns with data. I may have a table A, that references
> rows in table B. The columns in A that references the rows in B will be
> foreign keys in A to B.
>
> For instance, table A could have the columns IDA ( it's PK), IDB (an FK
> to table B, whose value would be, as you probalbly guessed, taken from
> column B.IDB), and other columns, that we won't care here.
>
> In this layout, B would be "a" parent table for A. Each row in A would
> have to have a valid value in the IDB column, that is, the value in
> A.IDB would have to be either NULL or an existing value in B.IDB.
>
> More over, if a row in B was to be deleted and there existed rows in A
> that referenced that row, an error would occur (well, not necessarily.
> Read on). In the same token, if a row in B had the value of IDB
> updated, an error would occur if there existed references to that row
> in A.
>
> The name of this game is "referencial integrity".
>
> Notice that you may configure your FK constraint to delete the items
> from table A that referenced items in table B when those are deleted.
> Also, you may configure it to automagically update the value in A.IDB
> whenever the corresponding B.IDB becomes updated.
>
> This may seem exactly what you want, but, as I said before, an FK can
> only reference the *primary key* of the foreign table, and this doesn't
> seem to be the case of your tables.
>
> Even if you managed to define FKs from your table to other tables in
> your setup, you'd still have a problem. Because you're defining the FK
> constraint in code (not in the database), you'd have to load the data
> from *all* the referenced tables into the application before the
> constraint could be applied. This may be feasible, but if you have
> large tables it may become prohibitive, performance-wise.
>
> It seems a more detailed description of what you really are up to is
> needed here...
>
> HTH.
>
> Regards,
>
> Branco.
>

> Thanks Branco,
> probably I'm talking about FK without knowing exactly what a FK is. But, if
> it is the case, it's a misunderstanding coming from a lot of confusion
> generated by reading too many manuals, newsgroups, online help and much
> more.
> I wish to explain why I need five identicals tables. I want write an
> application based on medical issues; in a single medical session, a single
> patient could have many different medical visits (I extimated a max number
> of five), because in the medical office there are many different doctors
> making different visits. So, for example, Mr. Smith, in the same day, could
> have three different visits (cardiologist, dentist and dermatologist). In a
> form, I have a box for the patients (coming from a 'patient' table), the
> date, and the five possible visits that the patient could have in that day.
> In these five tables (called tblPrest1... tblPrest5), there are three
> columns: a progressive ID of the medical visit, the kind of medical visit,
> and the price related. If the boss wants to change the price of most of the
> medical visits, for example in the new year, I want that the operator
> changes it just in a table and replicate the change in all other four
> tables, without going to change the price one table at a time.
> The five tables was already created in Access and filled with the actual
> medical visits and related price. I'm making an application in vb2005 that
> connects to the .mdb file (and it already works), and permits at the
> operators the updating, deleting and importing new records as needed.
> At this point, after reading your answer, I really don't know if using FK is
> what I really need. Effectively, using relations, I noted the pk issues you
> described and, because the operators may need to change either the price and
> the type of medical visit when needed, I can realize that it's probably not
> possible using relations.
> The question is: which different methods can I use to achieve this?
> I'm sorry with RobinS if I omitted this long explanation.
> Thanks,
> Max

"Branco Medeiros" <branco.medei***@gmail.com> wrote in message
news:1167170581.006289.49140@a3g2000cwd.googlegroups.com...
> Max wrote:
> <back posted/>
>
> It seems to me that there are many approaches to your problem. The
> separate tables, although looking practical, would have some serious
> issues. One of them is the inhability to automatically update all the
> other tables in response to changes in one of the them. Other issue is
> the possibility of a patient having more than five visists in a given
> day (who knows, sh*te happens...). Even if there are less than five
> visits, you'd have the issue of the ID not being synchronized between
> tables. A maintenance hell, IMHO.
>
> I didn't understand very well the issue of the visit pricing, but it
> seems you may keep everything in a single Visit table and decouple the
> items that would be common to all the visits of a given patient in a
> single day and put then in a separate table. Something in the likes
> of:
>
>  Visit table:
>  VisitID (PK)
>  PatientID (FK to the patients table)
>  VisitKind (probably an FK to a simple visit description table)
>  VisitInfoID (an FK to a VisitInfo table)
>  ... other fields unique to a visit, such as visit time, doctor name,
> duration, etc
>
>  VisitInfo table:
>  VisitInfoID (PK)
>  VisitDate
>  VisitPrice
>  ... other fields common to all visits of a patient in a given day
>
> HTH.
>
>
> Regards,
>
> Branco.
>
>> Thanks Branco,
>> probably I'm talking about FK without knowing exactly what a FK is.
>> But, if
>> it is the case, it's a misunderstanding coming from a lot of
>> confusion
>> generated by reading too many manuals, newsgroups, online help and
>> much
>> more.
>> I wish to explain why I need five identicals tables. I want write an
>> application based on medical issues; in a single medical session, a
>> single
>> patient could have many different medical visits (I extimated a max
>> number
>> of five), because in the medical office there are many different
>> doctors
>> making different visits. So, for example, Mr. Smith, in the same day,
>> could
>> have three different visits (cardiologist, dentist and
>> dermatologist). In a
>> form, I have a box for the patients (coming from a 'patient' table),
>> the
>> date, and the five possible visits that the patient could have in
>> that day.
>> In these five tables (called tblPrest1... tblPrest5), there are three
>> columns: a progressive ID of the medical visit, the kind of medical
>> visit,
>> and the price related. If the boss wants to change the price of most
>> of the
>> medical visits, for example in the new year, I want that the operator
>> changes it just in a table and replicate the change in all other four
>> tables, without going to change the price one table at a time.
>> The five tables was already created in Access and filled with the
>> actual
>> medical visits and related price. I'm making an application in vb2005
>> that
>> connects to the .mdb file (and it already works), and permits at the
>> operators the updating, deleting and importing new records as needed.
>> At this point, after reading your answer, I really don't know if
>> using FK is
>> what I really need. Effectively, using relations, I noted the pk
>> issues you
>> described and, because the operators may need to change either the
>> price and
>> the type of medical visit when needed, I can realize that it's
>> probably not
>> possible using relations.
>> The question is: which different methods can I use to achieve this?
>> I'm sorry with RobinS if I omitted this long explanation.
>> Thanks,
>> Max
> <snip>
>

> It seems to me that there are many approaches to your problem. The
> separate tables, although looking practical, would have some serious
> issues. One of them is the inhability to automatically update all the
> other tables in response to changes in one of the them. Other issue is
> the possibility of a patient having more than five visists in a given
> day (who knows, sh*te happens...). Even if there are less than five
> visits, you'd have the issue of the ID not being synchronized between
> tables. A maintenance hell, IMHO.
>
> I didn't understand very well the issue of the visit pricing, but it
> seems you may keep everything in a single Visit table and decouple the
> items that would be common to all the visits of a given patient in a
> single day and put then in a separate table. Something in the likes of:
>
>  Visit table:
>  VisitID (PK)
>  PatientID (FK to the patients table)
>  VisitKind (probably an FK to a simple visit description table)
>  VisitInfoID (an FK to a VisitInfo table)
>  ... other fields unique to a visit, such as visit time, doctor name,
> duration, etc
>
>  VisitInfo table:
>  VisitInfoID (PK)
>  VisitDate
>  VisitPrice
>  ... other fields common to all visits of a patient in a given day
>
> HTH.
>
>
> Regards,
>
> Branco.
>
>> Thanks Branco,
>> probably I'm talking about FK without knowing exactly what a FK is. But,
>> if
>> it is the case, it's a misunderstanding coming from a lot of confusion
>> generated by reading too many manuals, newsgroups, online help and much
>> more.
>> I wish to explain why I need five identicals tables. I want write an
>> application based on medical issues; in a single medical session, a
>> single
>> patient could have many different medical visits (I extimated a max
>> number
>> of five), because in the medical office there are many different doctors
>> making different visits. So, for example, Mr. Smith, in the same day,
>> could
>> have three different visits (cardiologist, dentist and dermatologist). In
>> a
>> form, I have a box for the patients (coming from a 'patient' table), the
>> date, and the five possible visits that the patient could have in that
>> day.
>> In these five tables (called tblPrest1... tblPrest5), there are three
>> columns: a progressive ID of the medical visit, the kind of medical
>> visit,
>> and the price related. If the boss wants to change the price of most of
>> the
>> medical visits, for example in the new year, I want that the operator
>> changes it just in a table and replicate the change in all other four
>> tables, without going to change the price one table at a time.
>> The five tables was already created in Access and filled with the actual
>> medical visits and related price. I'm making an application in vb2005
>> that
>> connects to the .mdb file (and it already works), and permits at the
>> operators the updating, deleting and importing new records as needed.
>> At this point, after reading your answer, I really don't know if using FK
>> is
>> what I really need. Effectively, using relations, I noted the pk issues
>> you
>> described and, because the operators may need to change either the price
>> and
>> the type of medical visit when needed, I can realize that it's probably
>> not
>> possible using relations.
>> The question is: which different methods can I use to achieve this?
>> I'm sorry with RobinS if I omitted this long explanation.
>> Thanks,
>> Max
> <snip>
>

>
> Branco Medeiros wrote:
> <back posted/>
>
> Well, the max number of visits (5) in a single day has decided by the boss
> of the office, so it would be ok to me, too. So, it's not a problem of
> mine. :) I don't understand why, if I have less than five visits, I don't
> have ID synchronized between tables. The five tables are identicals from
> the beginning, and the ID is always the same in the other tables, so every
> changes in a record in one table should replicate to the others...
> 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.
> 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.
> 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
> automatically the changes (update, delete or insert) in the other four
> tables.
> It sounds simple...
> Thanks,
> Max
>
>> It seems to me that there are many approaches to your problem. The
>> separate tables, although looking practical, would have some serious
>> issues. One of them is the inhability to automatically update all the
>> other tables in response to changes in one of the them. Other issue is
>> the possibility of a patient having more than five visists in a given
>> day (who knows, sh*te happens...). Even if there are less than five
>> visits, you'd have the issue of the ID not being synchronized between
>> tables. A maintenance hell, IMHO.
>>
>> I didn't understand very well the issue of the visit pricing, but it
>> seems you may keep everything in a single Visit table and decouple the
>> items that would be common to all the visits of a given patient in a
>> single day and put then in a separate table. Something in the likes of:
>>
>>  Visit table:
>>  VisitID (PK)
>>  PatientID (FK to the patients table)
>>  VisitKind (probably an FK to a simple visit description table)
>>  VisitInfoID (an FK to a VisitInfo table)
>>  ... other fields unique to a visit, such as visit time, doctor name,
>> duration, etc
>>
>>  VisitInfo table:
>>  VisitInfoID (PK)
>>  VisitDate
>>  VisitPrice
>>  ... other fields common to all visits of a patient in a given day
>>
>> HTH.
>>
>>
>> Regards,
>>
>> Branco.
>>
>>> Thanks Branco,
>>> probably I'm talking about FK without knowing exactly what a FK is. But,
>>> if
>>> it is the case, it's a misunderstanding coming from a lot of confusion
>>> generated by reading too many manuals, newsgroups, online help and much
>>> more.
>>> I wish to explain why I need five identicals tables. I want write an
>>> application based on medical issues; in a single medical session, a
>>> single
>>> patient could have many different medical visits (I extimated a max
>>> number
>>> of five), because in the medical office there are many different doctors
>>> making different visits. So, for example, Mr. Smith, in the same day,
>>> could
>>> have three different visits (cardiologist, dentist and dermatologist).
>>> In a
>>> form, I have a box for the patients (coming from a 'patient' table), the
>>> date, and the five possible visits that the patient could have in that
>>> day.
>>> In these five tables (called tblPrest1... tblPrest5), there are three
>>> columns: a progressive ID of the medical visit, the kind of medical
>>> visit,
>>> and the price related. If the boss wants to change the price of most of
>>> the
>>> medical visits, for example in the new year, I want that the operator
>>> changes it just in a table and replicate the change in all other four
>>> tables, without going to change the price one table at a time.
>>> The five tables was already created in Access and filled with the actual
>>> medical visits and related price. I'm making an application in vb2005
>>> that
>>> connects to the .mdb file (and it already works), and permits at the
>>> operators the updating, deleting and importing new records as needed.
>>> At this point, after reading your answer, I really don't know if using
>>> FK is
>>> what I really need. Effectively, using relations, I noted the pk issues
>>> you
>>> described and, because the operators may need to change either the price
>>> and
>>> the type of medical visit when needed, I can realize that it's probably
>>> not
>>> possible using relations.
>>> The question is: which different methods can I use to achieve this?
>>> I'm sorry with RobinS if I omitted this long explanation.
>>> Thanks,
>>> Max
>> <snip>
>>
>
>

> 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 automatically
> the changes (update, delete or insert) in the other four tables.
> It sounds simple...

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

>
> 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
>> automatically
>> 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" <branco.medei***@gmail.com> ha scritto nel messaggio
news:1167226611.085146.307970@h40g2000cwb.googlegroups.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...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.
>

>> 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" <nobody@devnull.spamcop.net> schreef in bericht
news:45925b93$0$7737$5fc30a8@news.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).
>>
>> 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
>>> automatically
>>> 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.
>>
>
>

"Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> ha scritto nel messaggio
news:Ow%2322qcKHHA.1280@TK2MSFTNGP04.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" <nobody@devnull.spamcop.net> schreef in bericht
> news:45925b93$0$7737$5fc30a8@news.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).
>>>
>>> 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
>>>> automatically
>>>> 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" <branco.medei***@gmail.com> ha scritto nel messaggio
news:1167261522.032650.165290@48g2000cwx.googlegroups.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.
>

"Branco Medeiros" <branco.medei***@gmail.com> schreef in bericht
news:1167170581.006289.49140@a3g2000cwd.googlegroups.com...
> Max wrote:
> <back posted/>
>
> It seems to me that there are many approaches to your problem. The
> separate tables, although looking practical, would have some serious
> issues. One of them is the inhability to automatically update all the
> other tables in response to changes in one of the them. Other issue is
> the possibility of a patient having more than five visists in a given
> day (who knows, sh*te happens...). Even if there are less than five
> visits, you'd have the issue of the ID not being synchronized between
> tables. A maintenance hell, IMHO.
>
> I didn't understand very well the issue of the visit pricing, but it
> seems you may keep everything in a single Visit table and decouple the
> items that would be common to all the visits of a given patient in a
> single day and put then in a separate table. Something in the likes of:
>
>  Visit table:
>  VisitID (PK)
>  PatientID (FK to the patients table)
>  VisitKind (probably an FK to a simple visit description table)
>  VisitInfoID (an FK to a VisitInfo table)
>  ... other fields unique to a visit, such as visit time, doctor name,
> duration, etc
>
>  VisitInfo table:
>  VisitInfoID (PK)
>  VisitDate
>  VisitPrice
>  ... other fields common to all visits of a patient in a given day
>
> HTH.
>
>
> Regards,
>
> Branco.
>
>> Thanks Branco,
>> probably I'm talking about FK without knowing exactly what a FK is. But,
>> if
>> it is the case, it's a misunderstanding coming from a lot of confusion
>> generated by reading too many manuals, newsgroups, online help and much
>> more.
>> I wish to explain why I need five identicals tables. I want write an
>> application based on medical issues; in a single medical session, a
>> single
>> patient could have many different medical visits (I extimated a max
>> number
>> of five), because in the medical office there are many different doctors
>> making different visits. So, for example, Mr. Smith, in the same day,
>> could
>> have three different visits (cardiologist, dentist and dermatologist). In
>> a
>> form, I have a box for the patients (coming from a 'patient' table), the
>> date, and the five possible visits that the patient could have in that
>> day.
>> In these five tables (called tblPrest1... tblPrest5), there are three
>> columns: a progressive ID of the medical visit, the kind of medical
>> visit,
>> and the price related. If the boss wants to change the price of most of
>> the
>> medical visits, for example in the new year, I want that the operator
>> changes it just in a table and replicate the change in all other four
>> tables, without going to change the price one table at a time.
>> The five tables was already created in Access and filled with the actual
>> medical visits and related price. I'm making an application in vb2005
>> that
>> connects to the .mdb file (and it already works), and permits at the
>> operators the updating, deleting and importing new records as needed.
>> At this point, after reading your answer, I really don't know if using FK
>> is
>> what I really need. Effectively, using relations, I noted the pk issues
>> you
>> described and, because the operators may need to change either the price
>> and
>> the type of medical visit when needed, I can realize that it's probably
>> not
>> possible using relations.
>> The question is: which different methods can I use to achieve this?
>> I'm sorry with RobinS if I omitted this long explanation.
>> Thanks,
>> Max
> <snip>
>