ADO Update Fails w/o Error

[Jim Jawn]

Okay, here's the problem, I have a function that transfers data from one
table to another using ADO. When I step through the code, everything looks
hunky dory. I have no problems with .Update method and I even get the ID
number of the record that was just inserted. The data, however does not
show up in the table after this function executes. What's even weirder is
that the other tables that I use in this function insert fine using the same
exact objects! I'm pretty perplexed as to why this might be happening.
Sorry for the long code, but does anyone with a flashlight around?
Pre-emptive thanks, Jim Jawn.

Public Function TransferProspects(ByVal lPID As Long) As Long

'Locals
Dim lCEID As Long 'CEID
Dim sSQLp As String 'Prospect
Dim sSQLc As String 'CE
Dim sSQLla As String 'Language

'ADO
Dim oRsp As ADODB.Recordset
Dim oRsc As ADODB.Recordset
Dim oRsla As ADODB.Recordset
Dim oConn As ADODB.Connection

'Set SQL statements to Get
sSQLp = "SELECT * FROM tblProspects p WHERE p.PID = " & lPID
sSQLc = "tblCE"
sSQLla = "SELECT * FROM tblProspectJoinLanguage la WHERE la.PID = " &
lPID

'Now Set & Use the ADO objects
Set oConn = New ADODB.Connection
Set oRsp = New ADODB.Recordset
Set oRsla = New ADODB.Recordset

'Now set the current project
Set oConn = CurrentProject.Connection

'Now Open the recordsets
oRsp.Open sSQLp, oConn, adOpenForwardOnly, adLockOptimistic, adCmdText
oRsc.Open sSQLc, oConn, adOpenDynamic, adLockBatchOptimistic, adCmdTable
oRsla.Open sSQLla, oConn, adOpenForwardOnly, adLockOptimistic, adCmdText

'Now Begin A Transaction
oConn.BeginTrans

'Now insert the CEID
With oRsc
.AddNew
.Fields("CEFirstName") = oRsp.Fields("FirstName")
...... 'Lots and Lots of Fields (50 total)
.Fields("UpdatedOn") = Now()
.Update
'Get New CEID
lCEID = .Fields("CEID")
.Close
End With

'ABOVE - No Message
'lCEID returns the correct number
'Nothing from Update at all --- hmmm....

' -- LANGUAGES
' -----------------------------------------------------------------
'Now reset the SQL statements
sSQLc = "tblCEJoinLanguages"

'Now Reopen orsc with language data
oRsc.Open sSQLc, oConn, adOpenDynamic, adLockOptimistic, adCmdTable

'Got the CEID, now Insert languages
With oRsla
Do While Not .EOF
oRsc.AddNew
oRsc.Fields("CEID") = lCEID
oRsc.Fields("LanguageID") = .Fields("LanguageID")
oRsc.Update
.MoveNext
Loop
End With
oRsc.Close

If oConn.Errors.Count > 0 Then
oConn.RollbackTrans
TransferProspects = 0
Call MsgBox(Err.Number & vbCrLf & Err.Description)
GoTo CleanUp
Else
oConn.CommitTrans
TransferProspects = lCEID
Call MsgBox("Prospect Successfully Transferred." & vbCrLf & _
"Click OK to view Prospect As Contractor Employee.", _
vbOKOnly, "Success!")
GoTo CleanUp
End If

CleanUp:
oRsp.Close
oRsla.Close
oConn.Close

Set oRsp = Nothing
Set oRsla = Nothing
Set oConn = Nothing
End Function

 

[joe]

Have you tried using CurrentProject.Connection instead of creating a new
adodb.connection object?