Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010

    Unanswered: Best way to create new row with FK dependency

    Please pardon yet another noobie question. I have a standard many to many relationship set up in my SQL server-hosted database. The relationship is implemented with three tables, A, B, and AB, where AB only has three columns, a PK, and two FKs pointing to A and B.

    For now I have chosen to use Access as my front end since it is widely deployed in our company.

    I have designed a Form to fill in information for table A. As part of that form, I would like to have a dataview subform that allows me to add new rows to table B (and link them to the current A row using AB, of course).

    What is the best way to implement this?
    1. Can it be done simply by properly setting up relationships/constraints/cascades on the SQL server side and/or Access?
    2. Should I do it with some sort of server trigger?
    3. Or is it best to do it with Access VBA code?
    4. Or perhaps another method I haven't thought of yet?

    I appreciate any inputs from the community!

  2. #2
    Join Date
    Feb 2004
    My first reaction would be that the solution would depend on how long the "for now" will exist (forever is not that uncommon) and what meaning table B has in your application. Personally I'm not inclined to use triggers except for journals or audit-tables and such, I'd go for code, possibly in a procedure to make a migration easier?

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I don't regularly use Microsoft Access, but I'm pretty sure that there is a "Parent-Child DataView" that does exactly what you've requested.

    I'm moving this discussion to the Microsoft Access Forum to get you some better suggestions from the MS-Access perspective.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Aug 2010
    I'm guessing "for now" likely will be a few years due to lack of resources to implement anything other than Access

    Table B has multiple meanings - since I have a similar setup in a various places within the design.

    Based on both responses I've gotten, it appears I need some sort of code (whether stored procedure, Access VBA, or an Access "smart" view). This cannot simply be done via the FK cascades, is that correct?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts