[RESOLVED] One to One Relationship in VB2005

[2005]

Hi,
I have three tables that have a relationship one-one (using MSAccess). However, in VB2005 when I add a record in the first table the other tables does not generate a record as it do when the relationship is one-many. I'm not using datagrids. I'm using table1 in top of a form and the table2/table3 are in the same form as table1 but in tabs control.
Should i do some coding to manually relate table1 with the others....I'm really lost with this one-one relationship using Access and VB2005.

I have setup the database like this sample:

Table1:
table1ID - PK - Autonumber
Names - Text
LastName - Text

Table2:
table2ID - PK - Autonumber
table1ID - LongInteger and related one-one with table1(table1ID), Also Yes/No Duplicates.
Address - Text
State - Text

Thanks,
2005

[techgnome]

if you tell it to insert into table 1, why would you expect it to insert into table 2? you insert into table1, get the id, then use that to insert into table2....

-tg

[2005]

So, once i get the ID from the table1 should i get that ID and either by code or manually put it in the table2(table1ID)? Does the One to One relationship setup from the first post is correct?

Best Regards,
2005

[jmcilhinney]

You DB relationship is fine. After you insert the parent record you need to perform a query to get the last ID generated, put it into the child record and then insert that too. This sort of stuff is easier with a real database, e.g. SQL Server, as the Jet OLEDB provider doesn't support multiple SQL statements in a single command. With multiple statements you can combine the first INSERT and the SELECT into a single command, which makes things much cleaner.

[2005]

ok, then i will create the DB using SQLExpress. I have no problem either on creating the db using SQL or conecting to it. But i might have some problems on combining the statements into a single command as you mentioned above. Any sample to read about this will be helpfull, however i will do some research.

Thank you very much,
2005

[jmcilhinney]

You can combine multiple inline SQL statements for SQL Server simply by separating them with a colon, e.g.

myCommand.CommandText = "SELECT * FROM Table1; SELECT * FROM Table2"

In your case you'd have an INSERT statement followed by a SELECT statement. The query is going to get the value of SCOPE_IDENTITY() and either populate a row field or an output parameter. If you create a typed DataSet the wizard will offer to generate such SQL for you, so you could either do that for real or maybe just to see what the SQL looks like.

Of course, you can also use stored procedures with multiple statements in them.

[2005]

Thank you for the information. Now i have good info to start again.

Best Regards,
2005