dminx13
asked on
Multiple-step OLE DB Operation generated errors.
Getting a Run-time error: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. On 2 rows below. Not sure what the issue is? Or how to fix. But if I drag past or comment out the offending rows it works fine. Anyone have any suggestions?
Public Sub ExportToTimeTrac(ID As String, ImportDate As Date, BadgeNumber As String, _
FirstName As String, LastName As String, MiddleInitial As String, _
Supervisor As String, Status As String, EmployeeGroup As String, _
PeriodCycle As String, ShiftSchedule As String, Source As String, _
ScheduleImport As String, ScheduleType As String, Vacation As Single, _
Sick As Single, _
JCC1 As String, WR1 As String, WageType1 As String, CC1 As String, _
JCC2 As String, WR2 As String, WageType2 As String, CC2 As String, _
JCC3 As String, WR3 As String, WageType3 As String, CC3 As String, _
JCC4 As String, WR4 As String, WageType4 As String, CC4 As String, _
JCC5 As String, WR5 As String, WageType5 As String, CC5 As String, _
ImportSource As String, ImportStatus As String, LastUpdated As Date)
' Procedure is used to export records to the time trac database for import. If testing mode
' data will go to test database - SAMC135, else production SAMC 136
''''Dim wsp As Workspace
''''Dim con As Connection
''''Dim rs As Recordset
''''Dim fTest As Boolean
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
' Are we in a Test mode?
''''fTest = TestDatabase
' Create the ODBC Connection
''''Call RegisterTTDatabase(fTest)
''''Set wsp = CreateWorkspace("", "timetrac", "atks*atks", dbUseODBC)
''''If fTest = True Then
' Point to test
'Set con = wsp.OpenConnection("TT", , , "ODBC;Database=TimeTracTes t;UID=time trac,PWD=a tks*atks;D SN=HRISTim eTracTest" )
''''Set con = wsp.OpenConnection("TT", , , "ODBC;Database=TimeTracTes t;UID=time trac;PWD=a tks*atks;D SN=HRISTim eTracTest" )
''''Else
' Point to production
'Set con = wsp.OpenConnection("TT", , , "ODBC;Database=TimeTrac;UI D=timetrac ;PWD=atks* atks;DSN=H RISTimeTra c")
''''Set con = wsp.OpenConnection("TT", , , "ODBC;Database=TimeTrac;UI D=timetrac ;PWD=atks* atks;DSN=T imeTrac")
''''End If
''''Set rs = con.OpenRecordset("Employe esImport", dbOpenDynaset, 0, dbPessimistic)
con.Open _
"Provider = sqloledb;" & _
"Data Source=FR01APTATK01;" & _
"Initial Catalog=TimeTrac;" & _
"User ID=timetrac;" & _
"Password=atks*atks;"
rs.Open "EmployeesImport", con, adOpenDynamic, adLockOptimistic
With rs
.AddNew
.Fields("Employee") = ID
.Fields("ImportDate") = ImportDate
.Fields("Badge") = BadgeNumber
.Fields("Company") = "1" ' Default 1 -Saint Agnes Medical Center
.Fields("FirstName") = FirstName
.Fields("LastName") = LastName
.Fields("MiddleInitial") = MiddleInitial
.Fields("Supervisor") = Supervisor
-->On this row .Fields("Status") = Status
.Fields("EmployeeGroup") = EmployeeGroup
.Fields("SubGroup") = " " ' Sub group - pass a blank
.Fields("Class") = "A1-A1" ' Default A1-A1 does not mean anything
.Fields("HomeCostCenter") = CC1
.Fields("PeriodCycle") = PeriodCycle
.Fields("ShiftSchedule") = ShiftSchedule
.Fields("Source") = Source
.Fields("ScheduleImport") = ScheduleImport
.Fields("ScheduleType") = ScheduleType
.Fields("VacationAccrued") = Vacation
.Fields("SickAccrued") = Sick
.Fields("JobCode1") = JCC1
.Fields("WorkRule1") = WR1
.Fields("WageType1") = WageType1
.Fields("CostCenter1") = CC1
.Fields("JobCode2") = JCC2
.Fields("WorkRule2") = WR2
.Fields("WageType2") = WageType2
.Fields("CostCenter2") = CC2
.Fields("JobCode3") = JCC3
.Fields("WorkRule3") = WR3
.Fields("WageType3") = WageType3
.Fields("CostCenter3") = CC3
.Fields("JobCode4") = JCC4
.Fields("WorkRule4") = WR4
.Fields("WageType4") = WageType4
.Fields("CostCenter4") = CC4
.Fields("JobCode5") = JCC5
.Fields("WorkRule5") = WR5
.Fields("WageType5") = WageType5
.Fields("CostCenter5") = CC5
.Fields("ImportSource") = ImportSource
--> On this row .Fields("ImportStatus") = ImportStatus
.Fields("LastUpdated") = LastUpdated
.Update
End With
Set rs = Nothing
Set con = Nothing
''''Set wsp = Nothing
End Sub
Public Sub ExportToTimeTrac(ID As String, ImportDate As Date, BadgeNumber As String, _
FirstName As String, LastName As String, MiddleInitial As String, _
Supervisor As String, Status As String, EmployeeGroup As String, _
PeriodCycle As String, ShiftSchedule As String, Source As String, _
ScheduleImport As String, ScheduleType As String, Vacation As Single, _
Sick As Single, _
JCC1 As String, WR1 As String, WageType1 As String, CC1 As String, _
JCC2 As String, WR2 As String, WageType2 As String, CC2 As String, _
JCC3 As String, WR3 As String, WageType3 As String, CC3 As String, _
JCC4 As String, WR4 As String, WageType4 As String, CC4 As String, _
JCC5 As String, WR5 As String, WageType5 As String, CC5 As String, _
ImportSource As String, ImportStatus As String, LastUpdated As Date)
' Procedure is used to export records to the time trac database for import. If testing mode
' data will go to test database - SAMC135, else production SAMC 136
''''Dim wsp As Workspace
''''Dim con As Connection
''''Dim rs As Recordset
''''Dim fTest As Boolean
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
' Are we in a Test mode?
''''fTest = TestDatabase
' Create the ODBC Connection
''''Call RegisterTTDatabase(fTest)
''''Set wsp = CreateWorkspace("", "timetrac", "atks*atks", dbUseODBC)
''''If fTest = True Then
' Point to test
'Set con = wsp.OpenConnection("TT", , , "ODBC;Database=TimeTracTes
''''Set con = wsp.OpenConnection("TT", , , "ODBC;Database=TimeTracTes
''''Else
' Point to production
'Set con = wsp.OpenConnection("TT", , , "ODBC;Database=TimeTrac;UI
''''Set con = wsp.OpenConnection("TT", , , "ODBC;Database=TimeTrac;UI
''''End If
''''Set rs = con.OpenRecordset("Employe
con.Open _
"Provider = sqloledb;" & _
"Data Source=FR01APTATK01;" & _
"Initial Catalog=TimeTrac;" & _
"User ID=timetrac;" & _
"Password=atks*atks;"
rs.Open "EmployeesImport", con, adOpenDynamic, adLockOptimistic
With rs
.AddNew
.Fields("Employee") = ID
.Fields("ImportDate") = ImportDate
.Fields("Badge") = BadgeNumber
.Fields("Company") = "1" ' Default 1 -Saint Agnes Medical Center
.Fields("FirstName") = FirstName
.Fields("LastName") = LastName
.Fields("MiddleInitial") = MiddleInitial
.Fields("Supervisor") = Supervisor
-->On this row .Fields("Status") = Status
.Fields("EmployeeGroup") = EmployeeGroup
.Fields("SubGroup") = " " ' Sub group - pass a blank
.Fields("Class") = "A1-A1" ' Default A1-A1 does not mean anything
.Fields("HomeCostCenter") = CC1
.Fields("PeriodCycle") = PeriodCycle
.Fields("ShiftSchedule") = ShiftSchedule
.Fields("Source") = Source
.Fields("ScheduleImport") = ScheduleImport
.Fields("ScheduleType") = ScheduleType
.Fields("VacationAccrued")
.Fields("SickAccrued") = Sick
.Fields("JobCode1") = JCC1
.Fields("WorkRule1") = WR1
.Fields("WageType1") = WageType1
.Fields("CostCenter1") = CC1
.Fields("JobCode2") = JCC2
.Fields("WorkRule2") = WR2
.Fields("WageType2") = WageType2
.Fields("CostCenter2") = CC2
.Fields("JobCode3") = JCC3
.Fields("WorkRule3") = WR3
.Fields("WageType3") = WageType3
.Fields("CostCenter3") = CC3
.Fields("JobCode4") = JCC4
.Fields("WorkRule4") = WR4
.Fields("WageType4") = WageType4
.Fields("CostCenter4") = CC4
.Fields("JobCode5") = JCC5
.Fields("WorkRule5") = WR5
.Fields("WageType5") = WageType5
.Fields("CostCenter5") = CC5
.Fields("ImportSource") = ImportSource
--> On this row .Fields("ImportStatus") = ImportStatus
.Fields("LastUpdated") = LastUpdated
.Update
End With
Set rs = Nothing
Set con = Nothing
''''Set wsp = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
By the way, you can find out quickly enough by adding some error handling.
Then place a stop at the top of the procedure. Execute it. When you hit the stop, use F8 to step through the code slowly until you jump to the error handler. The last line executed caused the error.
Jim.
Then place a stop at the top of the procedure. Execute it. When you hit the stop, use F8 to step through the code slowly until you jump to the error handler. The last line executed caused the error.
Jim.
Sorry, just caught this:
--> On this row .Fields("ImportStatus") = ImportStatus
Check the field length/type.
Jim.
--> On this row .Fields("ImportStatus") = ImportStatus
Check the field length/type.
Jim.
ASKER
Status is passed as "Active" all the time. And ImportStatus is being passed as "New" all the time. So they are both strings. I've got other fields that are set up the same way: PeriodCycle - "B", ShiftSchedule - "01", Source - "Recruit", and ImportSource - "Recruit" So why are these 2 strings giving me issues?
Call ExportToTimeTrac(strID, Now(), BadgeNumber, Hire.Fields("FirstName"), _
Hire.Fields("LastName"), " ", strSupervisor, "Active", strEmployeeGroup, _
"B", "01", "Recruit", strScheduleImport, strScheduleType, 0, 0, _
strJCC(0), strWR(0), strWageType(0), strCC(0), _
strJCC(1), strWR(1), strWageType(1), strCC(1), _
strJCC(2), strWR(2), strWageType(2), strCC(2), _
strJCC(3), strWR(3), strWageType(3), strCC(3), _
strJCC(4), strWR(4), strWageType(4), strCC(4), _
"Recruit", "New", Now())
Public Sub ExportToTimeTrac(ID As String, ImportDate As Date, BadgeNumber As String, _
FirstName As String, LastName As String, MiddleInitial As String, _
Supervisor As String, Status As String, EmployeeGroup As String, _
PeriodCycle As String, ShiftSchedule As String, Source As String, _
ScheduleImport As String, ScheduleType As String, Vacation As Single, _
Sick As Single, _
JCC1 As String, WR1 As String, WageType1 As String, CC1 As String, _
JCC2 As String, WR2 As String, WageType2 As String, CC2 As String, _
JCC3 As String, WR3 As String, WageType3 As String, CC3 As String, _
JCC4 As String, WR4 As String, WageType4 As String, CC4 As String, _
JCC5 As String, WR5 As String, WageType5 As String, CC5 As String, _
ImportSource As String, ImportStatus As String, LastUpdated As Date)
Call ExportToTimeTrac(strID, Now(), BadgeNumber, Hire.Fields("FirstName"), _
Hire.Fields("LastName"), " ", strSupervisor, "Active", strEmployeeGroup, _
"B", "01", "Recruit", strScheduleImport, strScheduleType, 0, 0, _
strJCC(0), strWR(0), strWageType(0), strCC(0), _
strJCC(1), strWR(1), strWageType(1), strCC(1), _
strJCC(2), strWR(2), strWageType(2), strCC(2), _
strJCC(3), strWR(3), strWageType(3), strCC(3), _
strJCC(4), strWR(4), strWageType(4), strCC(4), _
"Recruit", "New", Now())
Public Sub ExportToTimeTrac(ID As String, ImportDate As Date, BadgeNumber As String, _
FirstName As String, LastName As String, MiddleInitial As String, _
Supervisor As String, Status As String, EmployeeGroup As String, _
PeriodCycle As String, ShiftSchedule As String, Source As String, _
ScheduleImport As String, ScheduleType As String, Vacation As Single, _
Sick As Single, _
JCC1 As String, WR1 As String, WageType1 As String, CC1 As String, _
JCC2 As String, WR2 As String, WageType2 As String, CC2 As String, _
JCC3 As String, WR3 As String, WageType3 As String, CC3 As String, _
JCC4 As String, WR4 As String, WageType4 As String, CC4 As String, _
JCC5 As String, WR5 As String, WageType5 As String, CC5 As String, _
ImportSource As String, ImportStatus As String, LastUpdated As Date)
ASKER
As a side note, this worked when we were using ODBC calls. We had to update to ADO and now it is throwing errors on those 2 fields. You can see that commented out on the code.
ASKER
UG!!!!! Didn't know where the data was going but this was right. The table was 1 character long and was getting data that was too long. I updated to be 1 character and it works perfectly now! Not sure why it was working before though. Maybe it was just trimming it??
<<Status is passed as "Active" all the time. And ImportStatus is being passed as "New" all the time. >>
You sure those values are trimmed? As a test, try hardcoding the letter "A" and "N" for the fields and see if it works.
Jim.
You sure those values are trimmed? As a test, try hardcoding the letter "A" and "N" for the fields and see if it works.
Jim.
if numbers, change the way you declare the variables
Status As String, change to Status As integer
ImportStatus As String change to ImportStatus As integer
if it is a Yes/No field change the variables accordingly to Boolean