Modify problem from Access VB to SQL 2000

Last Post 17 Nov 2006 08:39 AM by coulombe. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

14 Nov 2006 06:56 AM
I have a big problem:

I've created a database and imported some data on SQL Server 2000 SP4. Then I wrote a Access XP Application which updates data in the database. When I try to update data in the database the following error appears:
Row cannot be located for updating. Some calues may have have been changed since it was last read.
Sometimes I receive a E_FAIL error

Here the code:
Set objConn = CurrentProject.AccessConnection

Set objServerRec = New ADODB.Recordset
Set objContactAss = New ADODB.Recordset

strQuery = "SELECT * FROM Servers WHERE ServerName = '" & strServerName & "';"
objServerRec.Open strQuery, objConn, adOpenDynamic, adLockOptimistic

If objServerRec.Fields("ServerName") = strServerName Then
' Start the Transaction

With objServerRec
.Fields("Location") = strLocation
.Fields("SrvRole") = strSrvRole
.Fields("OSID") = iOS
.Fields("ActiveSrv") = bActive
.Fields("Prod") = bProd
.Fields("ForPatchDay") = bForPD
.Fields("UpdateType") = iUpdateType
.Fields("InstallType") = iInstType
End With

' Commit the Transaction
MsgBox "Error while getting Server Record for " & strServerName & "!", vbCritical, "Error"
End If

I tried with and without the BeginTrans & CommitTrans.
The MsgBox doesn't appear so the reading is OK.
As you can see the code tries to write immediately after the reading. Because it's in developing stage I'm the only one who access the database.

After enabling ODBC Logging I found these lines in SQL.LOG:
ServerUpdateTra dc8-1330 ENTER SQLExecDirectW
HSTMT 09944008
WCHAR * 0x01FF6D80 [ -3] "UPDATE "dbo"."Servers" SET Prod= ,ForPatchDay= ,InstallType= ,UpdateType= WHERE ((((("ServerID" = ? ) AND ("Prod" = ? ) ) AND ("ForPatchDay" = ? ) ) AND ("InstallType" = ? ) ) AND ("UpdateType" = ? ) ) \ 0"

ServerUpdateTra dc8-1330 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 09944008
WCHAR * 0x01FF6D80 [ -3] "UPDATE "dbo"."Servers" SET Prod= ,ForPatchDay= ,InstallType= ,UpdateType= WHERE ((((("ServerID" = ? ) AND ("Prod" = ? ) ) AND ("ForPatchDay" = ? ) ) AND ("InstallType" = ? ) ) AND ("UpdateType" = ? ) ) \ 0"

DIAG [07001] [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error (0)

Very strange.

Does anybody have an idea about the reason of this error(s)? Is there a coding error?

Let my know if you need more information.
I would appreciate any help.

New Member
New Member

16 Nov 2006 02:40 AM
Can you put in some debug PRINTs to make sure that your queries are OK and results as expected. Do a SELECT instead of the UPDATE, to make sure, everything is working.

Kalman Toth DB, DW & BI Architect, SQL Server Training
The Best SQL Server 2005 Training in the World
New Member
New Member

17 Nov 2006 08:39 AM
This can happen if your Access application is linking to SQL Server tables using ODBC.

The solution is to add a timestamp field to each of the tables that your Access app is connecting to. Here's what to do:

1) Open the table in design view in SQL Server. Add a new column (I call mine 'ts') and specify timestamp as data type. Save and close the table.

2) In MS Access, delete the link to the table and then reattach it.

You should now able to update the records in the table.

Hope this helps,

Marc Coulombe
Integrated DataTools Inc.

New Member
New Member

18 Nov 2006 03:52 PM
Make sure your table has a primary key field defined in SQL Server. Although you can create a table without a primary key field updating is not reliable without one and a variety of error messages can be returned from SQL

New Member
New Member

04 Dec 2006 11:25 PM
Dear all

Many thanks for the tips and sorry for the long delay.
I was away for Education short after I posted this topic and now I'm sick (Problems with my theets).

Kalman: I'm able to read from the record which I SELECT without problems. So I can assure that I'm on the correct record.

Marc: I will try your tip as soon as possible and return to here. I don't know when I can try it. But I will post feedback here for sure.

Thanks again.

Have a nice day
New Member
New Member

30 Jan 2007 04:26 AM
Dear all

I'm happy to be back.

I've added a timestamp column to all tables and refreshed the links in access, but without success. Still the same problem.

The strange thing is, that when I run the same code against access tables it works. So the code is ok for sure.

Does anybody have some tip's for me?

Thanks for any help

Acceptable Use Policy