SQLServer 2000 and ADO Lock Types

[IronIke]

I am developing a Visual Basic program that accesses a SQL2000 database. One of my queries is as follows:

"Select * from table1 where id = " & n

where n is selected by the user.

When I open the recordset on my local system using ADO, the recorset.locktype comes back as adLockBatchOptomistic, which allows me to append records.

However, when I run the code on my production system (also SQL2000), the recorset.locktype comes back as adLockReadOnly, which does not allow me to append records.

I am assuming that there is some setting on my SQLServer that is different from the production SQLServer. Does anyone know what could cause this? As it is, I have to debug my application on the production system, which I would prefer to avoid.

Thanks.

 

[tlbroadbent]

Check these possibilities.

1) The user doesn't have update permissions on the table in the production database.

2) The table doesn't have a primary key, unique constraint, or timestamp column. If Access cannot uniquely identify a record from the SQL Server table, it will not be updatable. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[IronIke]

Terry,

1) Yes. The user does have update permissions.

2) I believe the answer is no to this one on both databases.

I forgot to note that if the query is changed to the following:

"Select * from table1", the recordset.locktype on the production system comes back as adLockPessimistic. On my system, it returns adLockBatchOptomistic. Why the difference?