I am using an ADODB recordset object to populate the fields in an Access
Table, from Excel. There are two Tables that need to be updated, a header
file and a details file which are linked with a one to many relationship. My
code successfull updates the Header file but errors on the details file. "The
connection cannot be used to perform this operation. It is either closed or
invalid in this context." I have check the table name to ensure that it is
ok. I have tried the update without referential integrity enforced and still
no luck... Here is my code...

Public Sub LogUpdateExpenseAccount(ByVal strComments As String)
   Dim cnt     As New ADODB.Connection
   Dim rst     As New ADODB.Recordset
   Dim dteNow  As Date
   On Error GoTo ErrorHandler
   
   dteNow = Now()
   If udtLoginInfo.User <> "" Then
       'Open connection to the database
       cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=" & m_cDBLocation & ";"
       
       'rst.CursorLocation = adUseServer
       rst.Open m_cUpdateHeaderFile, cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
       rst.AddNew
       rst.Fields("User ID") = frmAllAccessLogin.g_strActiveUser
       rst.Fields("Update ID") = udtLoginInfo.User
       rst.Fields("Update Date") = dteNow
       rst.Fields("Method") = shtUpdateExpense.Range("K2").Value
       rst.Fields("Allocation") = shtUpdateExpense.Range("K3").Value
       If strComments <> "" Then rst.Fields("Comments") = strComments
       
       On Error GoTo AddUser
       rst.Update
       On Error GoTo ErrorHandler
   
       ' Close ADO objects
       rst.Close
       cnt.Close
       Set rst = Nothing
       Set cnt = Nothing
       Call UpdateDetails(dteNow)
   End If
   Exit Sub

AddUser:
   MsgBox "Your user profile has not been set up in Access Databse. " & _
    "Please contact a member of Financial Planning and Analysis.",
vbCritical, _
    "Login Error"
   cnt.Close
   Set rst = Nothing
   Set cnt = Nothing
   Exit Sub
   
ErrorHandler:
   modErrors.HandleError m_cModule, "LogUpdatedUnit_FTEs"

End Sub

Private Sub UpdateDetails(ByVal dteUpdate As Date)
   Dim cnt As New ADODB.Connection
   Dim rst As New ADODB.Recordset
   Dim intCounter As Integer
   
   On Error GoTo ErrorHandler
   
   
   'Traverse Array
   For intCounter = LBound(g_aryUpdateDetails()) To
UBound(g_aryUpdateDetails())
       rst.Open m_cUpdateDetailsFile, cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
       rst.AddNew
       rst.Fields("User ID") = frmAllAccessLogin.g_strActiveUser
       rst.Fields("Update Date") = dteUpdate
       rst.Fields("Period") = g_aryUpdateDetails(intCounter).Period
       rst.Fields("Branch") = g_aryUpdateDetails(intCounter).BranchNumber
       rst.Fields("Account") = g_aryUpdateDetails(intCounter).Account
       rst.Fields("Original Total") =
g_aryUpdateDetails(intCounter).OriginalTotal
       rst.Fields("New Total") = g_aryUpdateDetails(intCounter).NewTotal
       On Error GoTo AddUser
       rst.Update
       On Error GoTo ErrorHandler
       
   Next intCounter
   'Open connection to the database
   cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & m_cDBLocation & ";"
   
   'rst.CursorLocation = adUseServer
   rst.Open m_cUpdateDetailsFile, cnt, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
   rst.AddNew
   rst.Fields("User ID") = frmAllAccessLogin.g_strActiveUser
   rst.Fields("Update Date") = Now()
   
   
   
   On Error GoTo AddUser
   rst.Update
   On Error GoTo ErrorHandler

   ' Close ADO objects
   rst.Close
   cnt.Close
   Set rst = Nothing
   Set cnt = Nothing
   Exit Sub

AddUser:
   MsgBox "Your user profile has not been set up in Access Databse. " & _
    "Please contact a member of Financial Planning and Analysis.",
vbCritical, _
    "Login Error"
   cnt.Close
   Set rst = Nothing
   Set cnt = Nothing
   Exit Sub
   
ErrorHandler:
   modErrors.HandleError m_cModule, "LogUpdatedUnit_FTEs"

End Sub