ODBC Access Link to SS2K5-Cannot update new records.

Access 2003 connected via ODBC using
ODBC;Driver={SQL Native
Client};Server=ServerName;Database=DbName;UID=xx;P WD=xxx
Originally the data was imported from Access tables. All imported records
can be edited or updated without a problem but new records created from
access using code or access table views cannot be edited.
The returned error:
The records has been changed by another since you started editing it. ...
If a record is copied from an existing record by cutting and pasting the
complete record in an access table view then it is ok but if the record is
added by going to the new record (*) at the bottom of the table and adding
the one field that does not allow nulls then the autonumber gets updated
properly but none of the other fields can be edited.
It appears the SS2K5 server will not allow the access application to delete
one of the records that cannot be edited either.
The error:
The Microsoft Jet dtabase engine stopped the process because you and another
user are attempting to change the same data at the same time.
If you log into the Server using Management Studio with the same credentials
you can do anything you want.
Looked a differnet connection strings but am not aware of an ODBC
configuration with record locking directives.
Thanks in advance for any help you might offer.
RobGMiller
It's hard to diagnose a problem like this without being able to see
the database schema and the application/form code. One thing that
often helps is to add a timestamp column to the table. This lets
Access know if a row has been altered by another process since it was
fetched.
-Mary
On Fri, 14 Dec 2007 12:43:02 -0800, RobGMiller
<RobGMiller@.discussions.microsoft.com> wrote:

>Access 2003 connected via ODBC using
>ODBC;Driver={SQL Native
>Client};Server=ServerName;Database=DbName;UID=xx; PWD=xxx
>Originally the data was imported from Access tables. All imported records
>can be edited or updated without a problem but new records created from
>access using code or access table views cannot be edited.
>The returned error:
>The records has been changed by another since you started editing it. ...
>If a record is copied from an existing record by cutting and pasting the
>complete record in an access table view then it is ok but if the record is
>added by going to the new record (*) at the bottom of the table and adding
>the one field that does not allow nulls then the autonumber gets updated
>properly but none of the other fields can be edited.
>It appears the SS2K5 server will not allow the access application to delete
>one of the records that cannot be edited either.
>The error:
>The Microsoft Jet dtabase engine stopped the process because you and another
>user are attempting to change the same data at the same time.
>If you log into the Server using Management Studio with the same credentials
>you can do anything you want.
>Looked a differnet connection strings but am not aware of an ODBC
>configuration with record locking directives.
>Thanks in advance for any help you might offer.

ODBC Access Link to SS2K5-Cannot update new records.

Access 2003 connected via ODBC using
ODBC;Driver={SQL Native
Client};Server=ServerName;Database=DbNam
e;UID=xx;PWD=xxx
Originally the data was imported from Access tables. All imported records
can be edited or updated without a problem but new records created from
access using code or access table views cannot be edited.
The returned error:
The records has been changed by another since you started editing it. ...
If a record is copied from an existing record by cutting and pasting the
complete record in an access table view then it is ok but if the record is
added by going to the new record (*) at the bottom of the table and adding
the one field that does not allow nulls then the autonumber gets updated
properly but none of the other fields can be edited.
It appears the SS2K5 server will not allow the access application to delete
one of the records that cannot be edited either.
The error:
The Microsoft Jet dtabase engine stopped the process because you and another
user are attempting to change the same data at the same time.
If you log into the Server using Management Studio with the same credentials
you can do anything you want.
Looked a differnet connection strings but am not aware of an ODBC
configuration with record locking directives.
Thanks in advance for any help you might offer.
RobGMillerIt's hard to diagnose a problem like this without being able to see
the database schema and the application/form code. One thing that
often helps is to add a timestamp column to the table. This lets
Access know if a row has been altered by another process since it was
fetched.
-Mary
On Fri, 14 Dec 2007 12:43:02 -0800, RobGMiller
<RobGMiller@.discussions.microsoft.com> wrote:

>Access 2003 connected via ODBC using
>ODBC;Driver={SQL Native
> Client};Server=ServerName;Database=DbNam
e;UID=xx;PWD=xxx
>Originally the data was imported from Access tables. All imported records
>can be edited or updated without a problem but new records created from
>access using code or access table views cannot be edited.
>The returned error:
>The records has been changed by another since you started editing it. ...
>If a record is copied from an existing record by cutting and pasting the
>complete record in an access table view then it is ok but if the record is
>added by going to the new record (*) at the bottom of the table and adding
>the one field that does not allow nulls then the autonumber gets updated
>properly but none of the other fields can be edited.
>It appears the SS2K5 server will not allow the access application to delete
>one of the records that cannot be edited either.
>The error:
>The Microsoft Jet dtabase engine stopped the process because you and anothe
r
>user are attempting to change the same data at the same time.
>If you log into the Server using Management Studio with the same credential
s
>you can do anything you want.
>Looked a differnet connection strings but am not aware of an ODBC
>configuration with record locking directives.
>Thanks in advance for any help you might offer.

Obtaining the number of records in a sqlDataReader

Can anyone tell me how I can obtain the number of records returned in a sqlDataReader recordset?You will need to read through the records to know how many rows, or alternately, return the rowcount as a column:

SELECT *,(SELECT COUNT(*) FROM Customer WHERE ID=7)
FROM Customer WHERE ID=7

If using stored procedures, perhaps you can do this more elegantly.

Why do you need the count?|||You can also check the @.@.rowcount system variable after the select

select ...
set @.Count = @.@.rowcount|||I need the count because the project I am working on needs to do specific tasks dependent on the no of records returned.

How can I access the @.@.rowcount from inside my code?|||Well, then I think the reality is you are going to have to (either in a seperate query or in a subquery) run the COUNT(*) query and send that as a column in the select statement. You could pass an OUTPUT parameter, but I do not believe the OUTPUT parameter will be readable until the DataReader has been read through.

Perhaps you should just use a DataSet, which has the count available.|||Perhaps a dataset is the better tool for the job then. Will it have any significant overhead compared to a datareader?|||Yes. Rather than reading records one at a time in firehose mode, it will read all rows into memory, and store them in memory (the dataSet is an in-memory representation). If you are processing a huge resultset, it could be a problem vs. the DataReader, but generally it is OK as well.|||You can also try using a stored procedure and you can return the count as either a seperate resultset or using an output paramater.