Cannot add rows to query on SQL Server

[lorirobn]

Hi,

I recently put an Access database onto SQL Server. One of my forms, which has a query as its source, is not allowing any rows to be added on the SQL Server. It does allow row addition on my local server, and the table itself allows row addition on SQL Server.

I seem to remember reading that the Inner Join statement does not work on SQL Server, but can't find any info now.

In case it helps, my SQL is:

SELECT tblInventory.Item, tblInventory.ItemNumber, tblInventory.ItemLongDesc, tblInventory.VendorNumber, tblInventory.VendorItemNumber, tblInventory.VendorColor, tblInventory.Source, tblInventory.NetPrice, tblVendor.VendorName
FROM tblVendor INNER JOIN tblInventory ON tblVendor.VendorNumber = tblInventory.VendorNumber
ORDER BY tblInventory.Item, tblInventory.ItemNumber, tblInventory.VendorNumber;

When I remove any reference to the 2nd table (tblVendor), the query allows rows to be added.

Is there anything I am doing that does not work on SQL Server?

Thanks,
Lori

 

[jbenson001]

Inner joins do exist in SQL Server. When you run your query above, do you get results?

Jim

 

[lorirobn]

Yes, I get results, and it looks fine. The only problem I see is on the Navigation Bar. It's got the record number, and an icon for next record and last record. However, icon for 'add new record' is dimmed (and, on my local server, it's not dimmed and works fine).

Thanks,
Lori

 

[jbenson001]

If you are using access I am not sure how you would use it,. I think you need to use a linked table.. but I am no access guru.. You would need to write an Insert statment as far as I know

 

[lorirobn]

Fixed it. Thanks for your help. Turns out I didn't have the Primary Key set up in the tblVendor on the SQL Server side.

 

[DrNektar]

Yeap....I was ready to answer, but lorirobn found it himself.

I was also working with Access-SQL Server 2000, and the
'wiz-kid' that had written the SQL tables, and was supposed to be experienced, hadn't had primary keys in a lot of cases.

In that case you cannot add nor edit records from an outside source, i.e. a linked table such as in access.