Appending data to 2 tables in a 1:1 relationship

[ChrisW]

Hi,

I've got a MS Access database with a 1:1 relationship between 2 of the
tables, and I'm trying to append data to both tables.

Both tables have the same Primary Key, which is generated as an Auto-
Number in Access. If I delete the relationship in Access, then I can
append data to both tables at the same time. However, if I re-apply
the relationship, and rerun my code, then I get:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You
cannot add or change a record because a related record is required in
table <tablename>

At the moment, I'm just using a simple Connection and Statement:

try
{
System.out.println(sql1 + "\n" + sql2 + "\n" + sql3);
Statement results = conn.createStatement();
results.executeUpdate(sql1);

Statement r2 = conn.createStatement();

r2.executeUpdate(sql2);
}
catch(Exception ex)
{
System.out.println(ex.toString());
}

I have tried using PreparedStatements, but get the same error; I know
my SQL for each APPEND query is correct (I've tested them in Access),
and I also know that you have to use separate SQL queries to append
data into multiple tables (a single SQL query can't be used to append
data into more than 1 table), but I'm now completely stuck!

If anybody's got any good suggestions, I'd be most grateful,

Thanks,
Chris

 

[Sabine Dinis Blochberger]

Hi,

I've got a MS Access database with a 1:1 relationship between 2 of the
tables, and I'm trying to append data to both tables.

Both tables have the same Primary Key, which is generated as an Auto-
Number in Access. If I delete the relationship in Access, then I can
append data to both tables at the same time. However, if I re-apply
the relationship, and rerun my code, then I get:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You
cannot add or change a record because a related record is required in
table <tablename>

This is a case for transactions. I don't know if or how MS Access
supports this.

Or, a wild guess, the auto increment is at fault. Make the column just
an integer and increment it yourself.

 

[Arne Vajhøj]

I've got a MS Access database with a 1:1 relationship between 2 of the
tables, and I'm trying to append data to both tables.

Both tables have the same Primary Key, which is generated as an Auto-
Number in Access. If I delete the relationship in Access, then I can
append data to both tables at the same time. However, if I re-apply
the relationship, and rerun my code, then I get:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You
cannot add or change a record because a related record is required in
table <tablename>

Statement results = conn.createStatement();
results.executeUpdate(sql1);
Statement r2 = conn.createStatement();
r2.executeUpdate(sql2);

Are you sure that it is not just a matter of insert the
records in the correct order to fulfill the foreign
key constraint ?

Arne

 

[Arne Vajhøj]

I've got a MS Access database with a 1:1 relationship between 2 of the
tables, and I'm trying to append data to both tables.

Both tables have the same Primary Key, which is generated as an Auto-
Number in Access. If I delete the relationship in Access, then I can
append data to both tables at the same time. However, if I re-apply
the relationship, and rerun my code, then I get:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You
cannot add or change a record because a related record is required in
table <tablename>

This is a case for transactions. I don't know if or how MS Access
supports this.

I don't think the use of transactions can resolve a foreign key
constraint problem.

MS Access do support transactions. But only with a rather poor
transaction isolation level.

Arne