by I'm using an Access 2002 Database Project to work with my SQL Server DB. I
have problems with the "Identity" auto increment setup. Here are the
problems I am running into:
- Have a products table with the following fields: productID, price,
category, and title. When you run a query based on this table and select a
certain product category as a criteria, you are run into problems adding new
records using this query view. i.e. here is an example query:
ALTER FUNCTION dbo.[QueryProducts Criteria 10]
()
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT ProductID, ProductTitle, ProductCategory,
ProductPrice
FROM dbo.tblProducts
WHERE (ProductCategory = '10') )
You get all the records that have the product category of 10 with this query
when you run this query in the Access Project Query datasheet view - but,
you run into problems going into the last blank row in the query row and
trying to add new records - the Auto Number Increment starts with the last
(highest) ProductID number displayed in your query view - not the highest
valid ProductID in the Products table.
What is my solution to this? I'm new to SQL Server 2000 so I don't know the
in and outs to it. The solution needs to work so that when I have a filtered
form and I'm trying to add new records, it correctly increments the highest
ProductID in the products table as well.