AutoNummering in Access DataBase

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I don't know if there is a solution to this question, but I am hoping there
is.

What I would like to know is the follow.
I made thru Access a database with 1 tablel having a primary key (column is
called mainID) and another table having a foreigner key (using OleDB
statements to read and write). I gave the primary key column autonummering.
This works great because I dont have to worry about the nummering of the
primary key table, its all done automatic, when using the
OleDbAdapter.Update() statement. The only problem is that when I create a 2
new rows 1 in each table. I can't get the foreigner key tablel to
automaticaly take the same nummer as the primary key.

Does anyone know if this is possible or will I have to use something else
than autonummering and take care of the linking myself with something like
this

int counter = 0;
DataRow drNewRow1 = myDataSet.Tables["Table1"].NewRow();
DataRow drNewRow2 = myDataSet.Tables["Table2"].NewRow();
drNewRow1["mainID"] = counter++; { primary key column}
drNewRow2["mainID"] = drNewRow1; { foreign key column}

Bardo
 
Oops,

When posting this post I got a error message , so I rewrote the post. Now I
see there 3 posts. Sorry.

Bardo
 
bardo,

You will have to get the id of the row that you inserted and manually insert
it into the foreign key table. You can get this by using the statement SELECT
@@IDENTITY immediately after the insert statement. Then you can insert this
row into the foreign key to maintain the referential integrity.

For Updates and Deletes you do have the ability to cascade to maintain the
referential integrity. In Access you can specify relationships between your
tables. Go to the Tools menu and select Relationships... Now select which
tables you want to have a relationship. Drag the primary key from one table
to the foreign key to the other table. A dialog box will appear. Select
Enforce Referential Integrity and then select
the Cascade checkboxes (one for Update and one for Delete)

I hope this helps.
-----------------------
 
Back
Top