Access 2002 Project with SQL Server 2000

Access 2002 Project with SQL Server 2000

Post by Mithu Sing » Sat, 12 Oct 2002 15:00:17

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]




RETURN ( SELECT TOP 100 PERCENT ProductID, ProductTitle, ProductCategory,

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.


1. Connecting to a Sql Server 2000 SP3 from a Sql Server 2000 SP2

I have attempted to register a Sql Server 2000 SP3 on my SQL Server 2000 SP2
Server and each time I receive the server does not exist or cannot connect
error.  I am able to ping the system in which the sp3 server is located so
is not a connectivity issue.  My connection is over a VPN using PPTP and my
intention is to conduct replication between the two servers.  Replication
have worked so far with most of my users but not with this one.  The only
common denominator is the fact that this is a sp3 server.   Are there any
suggestions on how to solve this problem?  Short of reinstalling the Sp3
server to bring it back to sp2, I don't see a solution to the issue.
Upgrading the sp2 server to sp3 is not an option.  Thanks in advance for
your help.

2. Child Table Calculations?!?

3. APIs for accessing SQL Server 7.x and SQL Server 2000 transaction log

4. Your Help Is Needed! (Receive a Cash Payment for Helping)

5. Access 2000 calling SQL Server 2000 store procedures

6. Full-text: how much memory is enough?

7. Access 2000, Sql Server 2000

8. Trapping SQL Server 2000 Error from within Access 2000

9. how to make upsizing MS Access 2000 db to SQL Server 2000 using

10. Upsizing Access 2000 -> SQL Server 2000

11. Access 2000 to SQL Server 2000

12. Access 2000 to SQL Server 2000 Problem