General - 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 !!!!

When you link the tables into access you have to define the primary key. The reason you can't add records is that when you define the link you're not establishing the Primary key on the ACCESS side. Now that you've got the SQL tables set up I would go through the linking process manually, one of the steps in that process should pop up with a window that shows all the fields for the table you're trying to link and ask you to establish a primary key. If you do that you should be all set.

Thanks for your help.

Finally I found the solution:

In the frm_B recorsource, I have changed "T_Order_Line" to "SELECT * FROM T_Order_Line" and it works!!