I have a sql server table with a column definition that I cannot
change (third party application)

when I link this table into access97
the linked table has a field called t_ccur with these properties
required = yes
allow zero length = yes


running this vba,
rs.addnew
rs!t_ccur = ""
rs.update

gives me this error
Cannot insert the value NULL into column 't_ccur',
table 'xyz'; column does not allow nulls. INSERT fails.

but I'm not set it to Null, I'm trying to set it to a zero-length
string

since it is required, how do I set this field to a zero-length string
in vba ?
do I need to build an INSERT statement ?

It looks like you are using DAO against an ODBC table. As you are
encountering -- ODBC tables present various challenges for operations
other than just reading the data (like when you need to
update/Insert/Delete data against an ODBC table).

Rather than wrestling with ODBC with DAO, I would go with ADO and write
a standard update (or insert - whatever the case is) statement. You
will need to make a reference (in a code module goto Tools/References)
to

Microsoft ActiveX Data Objects 2.5 (or higher) Library

Here is some sample ADO code (could use Windows Authentication or Sql
Server Authentication, and a stored procedure or plain text - I use
plane text in this sample)

Private Sub UpdateSqlRecord()
Dim cmd As New ADODB.Command
On Error GoTo errMsg

'--windows Authenticate method
'cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=YourServer;Database=YourDB;Trusted_Connecti on=Yes"

'--sql Server authentication method
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=YourServer;Database=YourDB;UID=rich123;pass word=test123;"

cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600

'--for stored proc - just add name of proc to commandtext
'cmd.CommandType = adCmdStoredProc

'--or here is how to use plain text
cmd.CommandType = adCmdText

cmd.CommandText = "Update yourTbl set fldx = '" & sometextvalue & "'
Where rowID = something"

'--the j here returns the # of affected records --
'--need to use cmd.ActiveConnection.CursorLocation = adUseClient
'--to get a value from j in this mode

cmd.Execute j, , adExecuteNoRecords

Debug.Print j

cmd.ActiveConnection.Close

Exit Sub

errMsg:
MsgBox Err.Description
End Sub





Rich

*** Sent via Developersdex http://www.developersdex.com ***

On Tue, 18 May 2010 10:16:18 -0700 (PDT), Roger
<lesperancer (AT) natpro (DOT) com> wrote:

ADO may not be the answer. Can you use a passthrough query?

-Tom.
Microsoft Access MVP

On May 20, 11:03*pm, Tom van Stiphout <tom7744.no.s... (AT) cox (DOT) net> wrote:
yes, I'm using a passthrough query, works fine