locked
MS SQL 2005 / MS Access 2007 .adp / One to Many Relationship --- Problem! RRS feed

  • Question

  • 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
    Tuesday, March 2, 2010 8:13 PM

Answers

  • Are you getting any error messages? And do you have profiler that you could watch the output?

    One thing that seems confusing is your table design, and it might just be that I can't follow your description, but table one should reference table two, not the other way around.

    Also, I know in SQL Server you can only add data to one table at a time, but it seems like you might be trying to do two at a time.


    Louis

    Wednesday, March 3, 2010 2:17 AM
  • Access 2007 does not automatically create relationships between related tables at the end of an import operation. You must manually create the relationships between the various new and existing tables by using the options on the Relationships tab.

    Wednesday, March 3, 2010 4:52 PM

All replies

  • Can you add data to the linked TABLEs manually?
    Tuesday, March 2, 2010 8:24 PM
    Answerer
  • Are you getting any error messages? And do you have profiler that you could watch the output?

    One thing that seems confusing is your table design, and it might just be that I can't follow your description, but table one should reference table two, not the other way around.

    Also, I know in SQL Server you can only add data to one table at a time, but it seems like you might be trying to do two at a time.


    Louis

    Wednesday, March 3, 2010 2:17 AM
  • Access 2007 does not automatically create relationships between related tables at the end of an import operation. You must manually create the relationships between the various new and existing tables by using the options on the Relationships tab.

    Wednesday, March 3, 2010 4:52 PM