ODBC call failed 1

[cathy68]

Hi Gurus
I couldn't fix the question for a couple of days. Please help:
When I run the following code I get an--error "ODBC call faileed"
1. This is an Access 2000 application connected to a SQL 2000 database.
2. tblFEMSAudi is linked table from SQL2000 databasse.
3. when debugging the error stops on --.Update--
4. A new record never get added to the SQL table because of this error
5.In tblFEMSAudi this is an AutoNum field called "ID" as Primary key.

Function AuditLog(State As Boolean)
' Exit the application
On Error GoTo Err_AuditLog
If State = True Then
' logging on
Dim db As Database
Dim rst As Recordset
'Dim tdf As TableDef
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblFEMSAudit", dbOpenDynaset, dbSeeChanges)
With rst
.AddNew
![Service Number] = GetDIXSName
![User] = LogOn.Audit
![Logged In] = Now()
.Update
rst.Close
End With

db.Close

'set the login in audit id
varAuditID = DMax("[ID]", "tblFEMSAudit", "[Service Number]='" & GetDIXSName & "' And IsNull([Logged Out])")

Else

'logging off
Dim sqlString As String
DoCmd.SetWarnings False
sqlString = "UPDATE tblFEMSAudit SET tblFEMSAudit.[Logged Out] = Now() " _
& "WHERE (((tblFEMSAudit.ID)=" & varAuditID & "));"

DoCmd.RunSQL sqlString

End If

Exit_AuditLog:
DoCmd.SetWarnings True

Exit Function

Err_AuditLog:

MsgBox ERR.Description
Resume Exit_AuditLog

End Function

Can anyone please tell me what is wrong and how to fix it?

Many thanks in advance.

cathy

 

[cathy68]

Hi guys
I solved this problem. The answer is:
in tblFEMSAudit there is an AutoNum field called"ID
" as Primary key. When I design this table at SQL server side, I set up it as "Primary Key", but I forgot to set up its indentity as "Yes", when I link it back to Access application, run the function that cause the error message"ODBC call failed".

hope this tip will help your guys in the future.

Cathy