Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    4

    Question Unanswered: Subforms and linked tables

    Hello.

    I'm upsizing an Access 2000 application to Access 2003 + SQL Server 2005, and I've found something I cannot get through.

    The original application was a file 'app.mdb' with all tables, and another file 'app_exec.mdb' with all forms (tables in 'app_exec.mdb' were linked to 'app.mdb').

    There was a table called T_Order (I will call it A) and another table called T_Order_Line (I will call it B).
    * Table A fields: Id (Primary Key) and Customer_Id
    * Table B fields: Id, Order_Id (Both are Primary Key) and Article_Id.

    There was one form (called frm_A) that includes a subform (called frm_B). The form frm_A allows navigation, adding, deleting and updating 'A' table. The subform frm_B shows data related to current 'A' record.

    The subform frm_B allows you to add/delete/update records to the table 'B'.

    Now I only have one file 'app.mdb' with forms and tables are linked to a SQL Server 2005 database (I use the Access export wizard).

    frm_A and frm_B are related using "Link master field" and "Link child field".

    The problem is that I cannot add new data to the frm_B. Navigation in the form works. In the subform frm_B I see data related to the current record. I can delete/edit data from frm_B. But I cannot insert new data on it.

    When I click on "Add New Order" button, I execute the code "DoCmd.GoToRecord , , acNewRec". In the old application, frm_B was cleared and ready for appending new data. But with linked tables to SQL Server, I cannot add new data!

    I have checked that if table 'B' is stored locally in Access, everything works fine. But if I use table 'B' linked in the SQL Server, I cannot add data.

    So, I am sure that the problem is only with table 'B' and the subform, but I cannot understand what happens.

    Any idea?

    Thanks !!!!

  2. #2
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Check out if the Allow Addition in form B is not changed to NO in your upsized db.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  3. #3
    Join Date
    May 2008
    Posts
    4
    Quote Originally Posted by Aran1
    Check out if the Allow Addition in form B is not changed to NO in your upsized db.
    Hi,

    Thanks for your reply.

    Yes, AllowAdditions is set to 'Yes' in both frm_A and frm_B

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How does form B get its data? What's the SQL?

    Can you make a query from that SQL and get query results that let you add data? You'll find out if it's the SQL or the Form by seeing if you can.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2008
    Posts
    4
    Quote Originally Posted by StarTrekker
    How does form B get its data? What's the SQL?

    Can you make a query from that SQL and get query results that let you add data? You'll find out if it's the SQL or the Form by seeing if you can.
    Hi StarTrekker,

    frm_B gets data directly from the table. It's linked with frm_A using the form properties "Link master field" and "Link child field". Setting these properties with both primary keys (Table 'A' and Table 'B') I get filtered data on it.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    First thing I would try is to add data into the table using SQL Server directly, bypassing the front end application. There might be an issue with the upsizing of that table... if you can't add data directly on the server, your front end app is going to have a problem!

    Next, try to change the record source of the form to an SQL statement. From my experience with SQL Server, it doesn't like just table names much. Something like SELECT * FROM T_Order_Line rather than just T_Order_Line.

    The settings for your link fields should be ok since it runs with native tables, but just for clarity, they should be ID for LinkMasterField and Order_Id for LinkChildField.

    Hope that helps
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2008
    Posts
    4

    Smile

    Quote Originally Posted by StarTrekker
    First thing I would try is to add data into the table using SQL Server directly, bypassing the front end application. There might be an issue with the upsizing of that table... if you can't add data directly on the server, your front end app is going to have a problem!

    Next, try to change the record source of the form to an SQL statement. From my experience with SQL Server, it doesn't like just table names much. Something like SELECT * FROM T_Order_Line rather than just T_Order_Line.

    The settings for your link fields should be ok since it runs with native tables, but just for clarity, they should be ID for LinkMasterField and Order_Id for LinkChildField.

    Hope that helps
    Yeah!

    Thank you very much for your help. Finally it works!!

    I have changed "T_Order_Line" to "SELECT * FROM T_Order_Line" and it works!!

    As you said, simply with "T_Order_Line" was fine for local tables, but doesn't work with linked.

    Thank you!!!

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Great

    And you're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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