I posted this in the "Microsoft SQL" forum, but it occurs ot me that may not have been the correct place to post it, so I am repeating it here..... sorry for any confusion........


I'm having a problem updating to a recordset. I am using the code listed below, which worked fine in my test environment. Unfortunately now that I am trying to run it on the live SQL 2000 Server I keep getting an error "3219", which seems to be associated with the .update at the bottom of my code.

Interestingly enough, I use similar code in other parts of my module, and it works fine???? Maybe I've just been looking ast this too long..... Can ANYONE help me out here??

MY PROBLEM CODE;

Function AddRegShiftRecord()
Dim CanUpd As Boolean
On Error GoTo ErrorHandler

'recordset and connection variables
Dim Cnxn As ADODB.Connection
Dim rstTs As ADODB.Recordset
Dim strCnxn As String
Dim strSQL As String
'record variables
Dim strID As String
Dim strFirstName As String
Dim strLastName As String
Dim blnRecordAdded As Boolean

' Open a connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='HRIS';" & _
"Initial Catalog='TIMS';Integrated Security='SSPI';"
Cnxn.Open strCnxn

' Open the table 'tbl_ProcTimesheet' with a cursor that allows updates
Set rstTs = New ADODB.Recordset
strSQL = "tbl_ProcTimesheet"
rstTs.Open strSQL, strCnxn, adOpenKeyset, adLockOptimistic, adCmdTable

CanUpd = rstTs.Supports(adAddNew)

rstTs.AddNew
rstTs!idCalendar.Value = 712
rstTs!Week.Value = CurrWeek
rstTs!erNum.Value = EmployerNum
txtDay = Format(DatePart("d", rst![PayDate].Value), "00")
txtMonth = Format(DatePart("m", rst![PayDate].Value), "00")
txtYear = DatePart("yyyy", rst![PayDate].Value)
'rstTs!PayDate.Value = txtDay & "/" & txtMonth & "/" & txtYear
'rstTs!StartTime.Value = RegStart
'rstTs!EndTime.Value = RegFinish
rstTs!Break.Value = RegBreak
rstTs!Sequence.Value = "01"
rstTs!ImportTypeID.Value = "COSTING"
rstTs!EmployeeNumber.Value = eeNum
rstTs!EmployeeName.Value = strEmployeeName
rstTs!ChequeType.Value = "REG"
rstTs!ReasonCode.Value = "91"
rstTs!Code.Value = RegCode
rstTs!TransAmt.Value = DailyRegularHours
rstTs!idPositionDefault.Value = DefPosnNo
rstTs!idJobCodeDefault.Value = DefJobNo
rstTs!idPosition.Value = PaidPosnNo
rstTs!idJobCode.Value = PaidJobNo
rstTs!Rate.Value = PayRate
rstTs!AnalysisID.Value = Analysis
rstTs!T1.Value = ac1
rstTs!T3.Value = ac3
rstTs!T4.Value = ac4
rstTs!T5.Value = ac5
rstTs!T6.Value = ac6
rstTs!Distribution.Value = Distr
rstTs!Premium.Value = Prem '
rstTs!Day.Value = DayNo
rstTs!PPOverride.Value = PPOver
rstTs!eeLink.Value = EmployeeLink

If EmployerNum = "5648 " Then
txtGratDay = Format(DatePart("d", rst![GratDate].Value), "00")
txtGratMonth = Format(DatePart("m", rst![GratDate].Value), "00")
txtGratYear = DatePart("yyyy", rst![GratDate].Value)
rstTs!GratuityOffSet.Value = GratOffSet
'rstTs!GratDate.Value = txtGratDay & "/" & txtGratMonth & "/" & txtGratYear
End If

rstTs!CreatedDate.Value = Now()
rstTs!idUser.Value = 1
rstTs!idBatch.Value = InsyncBatch
rstTs!ProductionID.Value = ProdID
rstTs!XCode.Value = DefX
rstTs!YCode.Value = DefY
rstTs!ZCode.Value = DefZ
rstTs!ExtendedEarnings.Value = DailyRegularHours * PayRate
rstTs!ByPassRRSP.Value = BPRRSP
rstTs!ByPassVac.Value = BPVacnPay
rstTs!ByPassDues.Value = BPDues
rstTs!ByPassBenefitsInLieu.Value = BPLieuBen
rstTs!ByPassGratuities.Value = BPGrats
rstTs.Update
blnRecordAdded = True

' clean up
rstTs.Close
Cnxn.Close
'Set rstTs = Nothing
'Set Cnxn = Nothing
Exit Function

ErrorHandler:
' clean up
If Not rstTs Is Nothing Then
If rstTs.State = adStateOpen Then rstTs.Close
End If
Set rstTs = 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 Function


CODE THAT WORKS FINE;

Function SetLastShiftRunRecord()
Dim ShiftCalccn As ADODB.Connection
Dim ShiftCalcrst As ADODB.Recordset
Dim strShiftCalccn As String
Dim strSQL As String


' Open a connection
Set ShiftCalccn = New ADODB.Connection
strShiftCalccn = "Provider='sqloledb';Data Source='HRIS';" & _
"Initial Catalog='TIMS';Integrated Security='SSPI';"
ShiftCalccn.Open strShiftCalccn

' Open table 'tbl_CurrPR' with a cursor that allows updates
Set ShiftCalcrst = New ADODB.Recordset
strSQL = "tbl_CurrPR"
ShiftCalcrst.Open strSQL, strShiftCalccn, adOpenKeyset, adLockOptimistic, adCmdTable


ShiftCalcrst.Filter = "ID = '" & CurrTSEmployer & "'"
ShiftCalcrst!LastShiftcalcRun.Value = Now()
ShiftCalcrst.Update


Set ShiftCalccn = Nothing
Set ShiftCalcrst = Nothing