ODBC Call error 3146

[Jimbo]

I have an access 97 database linked to sql server..I click update on a
form
and get an error message:
Run-time error 3146 ODBC call failed

The debugger points to this line:

MyTable.Update

In this segment of code:

Private Sub Update5_Click()

Dim MyDB As Database, MyTable As Recordset, MyTmpTable As
Recordset,
Criteria As String, NewID As Long
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("Dx5", DB_OPEN_DYNASET,
dbSeeChanges)
Set MyTmpTable = MyDB.OpenRecordset("tmp_RockDx5",
DB_OPEN_DYNASET)

MyTable.AddNew
MyTable.Update
MyTable.Move 0, MyTable.LastModified
NewID = MyTable![Dx5ID]

Me![HoldNewDxID] = NewID

If DCount("*", "Dx5Current") > 0 Then

Me![HoldOldDxID] = Me![Dx5Current].Form![Dx5ID]

'Put end date on the current
Axis 5
'record

Me![Dx5Current].Form![EndDate] = Date
DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD

'Copy in data from the current
Axis 5
'record to the new record,
since it is
'likely to be similar

Set MyTable = MyDB.OpenRecordset("Dx5", DB_OPEN_DYNASET)

holdnum = Forms![DxSummary]![HoldNewDxID]
Criteria = "[Dx5ID] = " & holdnum
MyTable.FindFirst Criteria
MyTable.Edit
MyTable![CurrentGAF] = Me![Dx5Current].Form![CurrentGAF]
MyTable![HighestGAFLastYear] =
Me![Dx5Current].Form![HighestGAFLastYear]
MyTable.Update

End If

Me.Visible = False
Me![Dx5Current].Requery 'This is necessary in order to
release the
'record, so that it can be edited
if the
'user uses the Cancel button on the

add-edit
'form
Criteria = "[Dx5ID] = Forms![DxSummary]![HoldNewDxID]"

DoCmd.OpenForm "AddDx5", , , Criteria, A_EDIT

MyTmpTable.Close
MyTable.Close

Forms![AddDx5]![ClientID] = Forms![LogIn]![ActiveClient]
Forms![AddDx5]![StaffID] = Forms![LogIn]![ActiveStaff]

End Sub

[Steve Jorgensen]

Since you are getting an ODBC error, I assume you're using linked tables. The
ODBC call failed error is the same for -any- server-side failure, and is not
terribly informative. To get the useful error information in these cases, you
must examine the Errors collection.

Looking at what you're doing, though, I'm guessing the table has one or more
required fields with no default values, so doing an Update immediately after
an AddNew is trying to create a new record without the minimum required field
data.

On 29 Mar 2005 07:00:05 -0800, "Jimbo" <ji********@motorola.com> wrote:

I have an access 97 database linked to sql server..I click update on a
form
and get an error message:
Run-time error 3146 ODBC call failed

The debugger points to this line:

MyTable.Update

In this segment of code:

Private Sub Update5_Click()

Dim MyDB As Database, MyTable As Recordset, MyTmpTable As
Recordset,
Criteria As String, NewID As Long
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("Dx5", DB_OPEN_DYNASET,
dbSeeChanges)
Set MyTmpTable = MyDB.OpenRecordset("tmp_RockDx5",
DB_OPEN_DYNASET)

MyTable.AddNew
MyTable.Update
MyTable.Move 0, MyTable.LastModified
NewID = MyTable![Dx5ID]

Me![HoldNewDxID] = NewID

If DCount("*", "Dx5Current") > 0 Then

Me![HoldOldDxID] = Me![Dx5Current].Form![Dx5ID]

'Put end date on the current
Axis 5
'record

Me![Dx5Current].Form![EndDate] = Date
DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD

'Copy in data from the current
Axis 5
'record to the new record,
since it is
'likely to be similar

Set MyTable = MyDB.OpenRecordset("Dx5", DB_OPEN_DYNASET)

holdnum = Forms![DxSummary]![HoldNewDxID]
Criteria = "[Dx5ID] = " & holdnum
MyTable.FindFirst Criteria
MyTable.Edit
MyTable![CurrentGAF] = Me![Dx5Current].Form![CurrentGAF]
MyTable![HighestGAFLastYear] =
Me![Dx5Current].Form![HighestGAFLastYear]
MyTable.Update

End If

Me.Visible = False
Me![Dx5Current].Requery 'This is necessary in order to
release the
'record, so that it can be edited
if the
'user uses the Cancel button on the

add-edit
'form
Criteria = "[Dx5ID] = Forms![DxSummary]![HoldNewDxID]"

DoCmd.OpenForm "AddDx5", , , Criteria, A_EDIT

MyTmpTable.Close
MyTable.Close

Forms![AddDx5]![ClientID] = Forms![LogIn]![ActiveClient]
Forms![AddDx5]![StaffID] = Forms![LogIn]![ActiveStaff]

End Sub

[Jimbo]

I found out the problem....it was because of an ntext field in the
table..I changed it to a varchar and it works fien now...but at least
your suggestion got me looking at the underlying table
structure...thanks

-Jim