Access 2007 subform not linking to parent with SQL Server Backend

Posted on 2011-10-05
Last Modified: 2012-05-12
Access 2007
SQL Server 2008

I have just finished linking all my Access 2007 front-ends to SQL Server 2008 databases.  I used DNS-less connections and the SQL Server driver.  Everything seems fine except in several front-ends I have a forms with a datasheet subforms ... and they are not linkling to the parent!?

The table for the parent form has a unique primary key "ORDERID" (int, Identity = yes, no nulls, clustered index) and the subform query has the same field ORDERID (int, Identity = no, nulls allowed, non-clustered index).  On the subform properties I have the Master/Child fields set to ORDERID.

If the record on the parent that is displayed already has records that match in both tables, then the subform is accessable, but if I am entering a new record, I can only enter data into the parent form and the subform remains disabled.

Can anyone tell me why?  With an Access backend, it works fine, but not with SQL Server backend.

Very important I find a solution ASAP! ... help!

Question by:MaxwellTurner
    LVL 84

    Accepted Solution

    What are you using as the Subform's Recordsource? Is it a table, or a query/view?

    Views in SQL Server are (for the most part) not updateable.

    LVL 1

    Author Comment

    Thanks LSM!  Wow this whole migration to SQL backend has been full of headaches, but one issue at a time!  Yes, I was using a query for the subforms ... works great now that I have changed it to the tables.

    LVL 84
    I feel your pain :)

    Here's a very good writeup on some of "best practices" when moving to SQL Server from Access:

    Also review some of the stuff from FMS:
    LVL 1

    Author Comment

    Thanks LSM!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now