My code won't update the data - can anyone help?

[Cloonalt]

If anyone can look at this and tell me why it won't update the data I would greatly appreciate it.

Public Sub Main()
On Error GoTo Err_Execute

' connection, command, and recordset variables
Dim Cnxn As ADODB.Connection
Dim cmdChange As ADODB.Command
Dim rstDaily_Dat As ADODB.Recordset
Dim Err As ADODB.Error
Dim strSQLChange As String
Dim strSQLDaily_Dat
Dim strCnxn As String

' Define SQL statement to execute as command text
strSQLChange = "UPDATE daily_dat SET daily_dat.[85_86_89_99] = '-1' " & _
"WHERE daily_dat.form_type='85' Or daily_dat.form_type='86' Or daily_dat.form_type='89' Or daily_dat.form_type='99';"

strCnxn = "Provider=SQLOLEDB.1; Data Source='GWL-0032815512'; " & _
"Initial Catalog='Daily_Import';Integrated Security='SSPI';"

Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Create command object
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = Cnxn
cmdChange.CommandText = strSQLChange

' Open Daily_Dat table
Set rstDaily_Dat = New ADODB.Recordset
strSQLDaily_Dat = "daily_dat"
rstDaily_Dat.Open strSQLDaily_Dat, Cnxn, , , adCmdTable

ExecuteCommand cmdChange, rstDaily_Dat

' Retrieve the current data by requerying the recordset
rstDaily_Dat.Requery

' clean up
rstDaily_Dat.Close
Cnxn.Close
Set rstDaily_Dat = Nothing
Set Cnxn = Nothing
Exit Sub

Err_Execute:
' Notify user of any errors that result from

If rstDaily_Dat.ActiveConnection.Errors.Count >= 0 Then
For Each Err In rstDaily_Dat.ActiveConnection.Errors
MsgBox "Error number: " & Err.Number & vbCr & _
Err.Description
Next Err
End If

' clean up
If Not rstDaily_Dat Is Nothing Then
If rstDaily_Dat.State = adStateOpen Then rstDaily_Dat.Close
End If
Set rstDaily_Dat = Nothing

If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub

Public Sub ExecuteCommand(cmdTemp As ADODB.Command, rstTemp As ADODB.Recordset)

Dim Err As Error

' Run the specified Command object and trap for
' errors, checking the Errors collection
On Error GoTo Err_Execute
cmdTemp.Execute
On Error GoTo 0

' Retrieve the current data by requerying the recordset
rstTemp.Requery

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query
If rstTemp.ActiveConnection.Errors.Count > 0 Then
For Each Err In rstTemp.ActiveConnection.Errors
MsgBox "Error number: " & Err.Number & vbCr & _
Err.Description
Next Err
End If

Resume Next

End Sub

Public Sub PrintOutput(rstTemp As ADODB.Recordset)

' Enumerate Recordset
Do While Not rstTemp.EOF
Debug.Print " " & rstTemp!Title & _
", " & rstTemp!Type
rstTemp.MoveNext
Loop

End Sub
'EndExecuteVB


Private Sub Command2_Click()

Main

End Sub

 

[mp9]

What data type is daily_dat.[85_86_89_99] ?

Suggest removing the quotes around -1 if it is a boolean or numeric... a long shot, but you never know.

i.e.

strSQLChange = "UPDATE daily_dat SET daily_dat.[85_86_89_99] = -1 " &_

http://www22.brinkster.com/accessory/

 

[Cloonalt]

{85_86_89_99] is text. If I execute the SQL in Query Analyzer the update works. So I don't think the problem it with the SQL.

Thanks for your help.