Solved

MS Access To SQL Server

Posted on 2010-11-15
24
289 Views
Last Modified: 2012-05-10
I split my 2007 Access DB to Frontend Backend with SQL Server 2005.

Now Im getting an error on a select list: Cannot add records primary key for table not in recordset.
0
Comment
Question by:webdork
  • 13
  • 5
  • 4
  • +1
24 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34141377
I think your question is not sufficient to determine what the issue is.

However, do you have a subform?
0
 

Author Comment

by:webdork
ID: 34141384
yes subform
0
 

Author Comment

by:webdork
ID: 34141389
Its an order form with a product subform. The subform has a select list linked to the product table.
0
 

Author Comment

by:webdork
ID: 34141395
Before splitting the DB to Front/Back end you could select a product from the subform select list and a line item with all product details would form and you could select another product line item. and so on.
0
 

Author Comment

by:webdork
ID: 34141423
Subform select list:

Record Source: SELECT DISTINCT Products.ProductName, ProductId FROM Products;
0
 
LVL 84
ID: 34141549
Is ProductID the Primary Key of the Products table?

Remove the Distinct keyword and see what happens.

0
 

Author Comment

by:webdork
ID: 34141562
Yes ProductID is the primary key. Im trying that now.
0
 

Author Comment

by:webdork
ID: 34141588
Still error after removing DISTINCT keyword as recommended.
0
 
LVL 84
ID: 34141606
Can you send the code snippet that produces the error?
0
 

Author Comment

by:webdork
ID: 34141632
Im not sure how to extract the code??
0
 

Author Comment

by:webdork
ID: 34141644
here is screenshot
screenshot.png
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34141792
You need to add productID to the subform select you just showed
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34141796
Then use [ProductID] as the field in the LinkChildFields and LinkMasterFields
properties of the subform control (the control on main form that holds the
subform object). The subform then will track with the ProductIDvalue that you
have showing in the main form, and the ProductIDvalue in the subform will be
filled in by ACCESS with the value from the main form.

Try it
0
 

Author Comment

by:webdork
ID: 34141811
the link field is OrderID. It needs to be to get the correct order details.  There is no ProductID reference in the main form
0
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 34142216
Ok, I thought you said productID is the primary key??
0
 

Author Comment

by:webdork
ID: 34142546
Not sure we are approaching this the right way. It worked before I split the DB.
0
 
LVL 84
ID: 34144434
Your screenshow shows the Recordsource of the combo to be this:

SELECT DISTINCT Products.ProductsID, Products.ProductName FROM Products;

Your BoundColumn is set to 2. Try setting the BoundColumn of the Combo to 1 (to store the value from the First column, which is ProductID).
0
 

Author Comment

by:webdork
ID: 34178025
Still no remedy. i've tried all recommendations.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34179304
Let's back up.

Exactly what are you doing when you receive this message? Are you loading the form, or are you trying to select something from a Combo, etc etc?

I assume these are linked tables? If so, can you confirm that your tables all have Primary Keys defined on them, and that those PKs are properly represented in the Access links? To do that, open the link in Design view (Access will complain, but open it anyway) and insure that a PK has been defined on the table.

Are you storing the VALUE of ProductID in your parent table (the table/query your form is based on), or are you storing the ProductName? Your settings would store the ProductName, which is not proper design.

What code is in your combo's BeforeUpdate and AfterUpdate events?
0
 

Author Comment

by:webdork
ID: 34179943
This DB model is based on an early MS Access Northwest DB.  Its been in use since 2002. Last week I split Access to use SQL Server 2005 as a Backend.  After the split, the ComboBox on the sub form stopped working.
Here are the details:

I've got an OrderForm and a OrderSubForm linked by OrderID

The OrderSubForm underlying query is OrderDetailsExtended linking the Products table with the OrderDetails table.

The Products table PK is ProductID.

The OrdersDetails table has an OrderDetailsID column that is the identity column and does not allow nulls. This column is not tagged as a PK. The OrdersDetails table has two columns tagged as PK. OrderID and ProductID, neither allow nulls.

In the OrderSubForm there is a ComboBox. When I invoke this Combobox, I see the list of ProductNames as I should, but when I try to select one I get an error:

"Cannot add record(s); Primary key for table 'Products' not in recordset"

This is the Control Source for the ComboBox:
ProductName

This is the RowSource for the ComboBox:
SELECT DISTINCT Products.ProductName FROM Products;


Here are the Before and After events:
Private Sub ProductID_BeforeUpdate(Cancel As Integer)
' If Orders Subform is opened as a standalone form, display a message
' and undo changes made to the ProductName text box. (The IsLoaded function
' finds a form in the Forms collection when it's opened as a standalone
' form, but not when it's opened as a subform on a main form.)

    'Dim strMsg As String, strTitle As String
   ' Dim intStyle As Integer
   
   ' If IsLoaded("Orders Subform") Then
      '  strMsg = "You can't add or edit a Product Name when you open Orders Subform as a standalone form."
        'intStyle = vbOKOnly
       ' strTitle = "Can't Add or Change Product Name"
       ' MsgBox strMsg, intStyle, strTitle
       ' Me!ProductID.Undo
       ' Me.Undo
    'End If

Private Sub Product_AfterUpdate()

End Sub

End Sub
0
 

Author Closing Comment

by:webdork
ID: 34185404
cant wait anymore
0
 
LVL 84
ID: 34186960
Your UI design is flawed, regardless of whether it's been working in the past or not. The error you're reporting:

"Cannot add record(s); Primary key for table 'Products' not in recordset"

Is self-explanatory. Given these statements:

(a) "The Products table PK is ProductID. "
(b) This is the Control Source for the ComboBox: ProductName
(c) This is the RowSource for the ComboBox:SELECT DISTINCT Products.ProductName FROM Products;  

Your form design is incorrect. You should NEVER store anything other than the Primary Key value of a lookup table in the host table. In this case, you're storing the value of Products.ProductName, when you should be storing the value of Products.ProductID. As the error said, you are NOT including the Primary Key value of the Products table (i.e. ProductID) in the recordset. You MUST do this with SQL Server.

Access allows you to get away with things of this nature. SQL Server does not.

In other words: Change your UI design to work with accepted normalization methods and it will work. If not, you'll have nothing but trouble moving forward with SQL Server.




0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 34190610
Just a thought as I don't see it here, was the PK an autonumber in Access? Is som check on SQL Server that the PK (which will be an interger) has the identity seed set yto yes (is one of the properties of that field near the bottom of the list).

Kelvin
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 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

15 Experts available now in Live!

Get 1:1 Help Now