MS SQL 2005 / MS Access 2007 .adp/ One to Many

[closerwalk]

Here is the problem. I created a new .adp pointed it to the SQL server and created the database and tables. The table structure below as a sample. My problem is that one-to-many relationships are not working correctly or I should say not updatable. What am I doing wrong?

TABLE ONE ------------------
id (pk) (numeric)
first name (text)
last name (text)
destination_id (numeric) (fk)

TABLE TWO ........................
id (pk) (numeric)
destination (text)

RELATIONSHIPS
one to many
table two ID --->> table one destination_id

QUERY
tableone first name lastname destination_id table two destination

When I try to add a record to the query its a no go. It will not allow me to insert new records. Does anyone know why?

Mind you this scenerio works in a acceess db file mdb perfectly.


Thanks in advance,

Ken

 

[pilsdumps]

You'd think that you could just have a query like this:

select
a.first,
a.lastname,
b.destination
from
table_one a,
table_two b
where
a.destination_id = b.ID

but as you've discovered, Access gets funny about this.

You need to include the primary key from both tables in your query (even if you're not using it) and set the unique table property to the table you want to insert into ie. table_one. I usually just include all primary and foreign keys in the select list, and it usually works.

Have a look here for further info:
http://allenbrowne.com/tips.html

you might find something here that will help.