Actualizar datos en Access 2010 desde Excel 2010

Estimad@s expert@s, estoy realizando una aplicación en excel 2010 de control de calificaciones estudiantiles y mediante una macro deseo actualizar la información en una tabla de access a manera de respaldo. La idea es que cada fila de la hoja de NOTAS sea validada en access 2010. Si existe el registro actualizar la información en access, caso contrario se debería crear un registro en access con todos los datos. La macro programada le adjunto a esta pregunta, me anticipo agradeciendo:

Sub ActualizarExcelAccess()
Dim Conn As ADODB.Connection, RecSet As ADODB.Recordset
Dim fila As Long, primeraFila As Integer, ultimaFila As Long, iX As Long
Dim dataSource As String, Tabla As String
Dim wsName As String
Dim SentenciaSQL As String
'definimos los parámetros que serán usados por el código
dataSource = Sheets("Parámetros").[Z2]
Tabla = "Notas"
wsName = "Notas"
primeraFila = 2
Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
dataSource & ";"
Conn.BeginTrans
'Set RecSet = New ADODB.Recordset
'RecSet.Open Tabla, Conn, adOpenKeyset, adLockOptimistic, adCmdTable
ultimaFila = WorksheetFunction.CountA(Sheets(wsName).Range("A:A"))
For iX = primeraFila To ultimaFila
SentenciaSQL = "Select * from " & Tabla & " where " _
& "Grado_Paralelo ='" & Sheets(wsName).Cells(iX, 4).Value & "' and " _
& "Materia ='" & Sheets(wsName).Cells(iX, 6).Value & "' and " _
& "Nombre_y_Apellidos ='" & Sheets(wsName).Cells(iX, 10).Value & "'"
Set RecSet = Conn.Execute(SentenciaSQL)
' Si el registro existe se actualizaría la base de datos del access si no existe
' se debe crear el registro nuevo
If Not RecSet.EOF Then
'Si el registro existe se actualiza UPDATE
Else
'Si el registro no existe se crea INSERT INTO
Set RecSet = New ADODB.Recordset
RecSet.Open Tabla, Conn, adOpenKeyset, adLockOptimistic, adCmdTable
With RecSet
.AddNew
'----------------------------------------------------------------
.Fields("Docente") = Sheets(wsName).Cells(iX, 1).Value
.Fields("Grado") = Sheets(wsName).Cells(iX, 2).Value
.Fields("Paralelo") = Sheets(wsName).Cells(iX, 3).Value
.Fields("Grado_Paralelo") = Sheets(wsName).Cells(iX, 4).Value
.Fields("Tipo") = Sheets(wsName).Cells(iX, 5).Value
.Fields("Materia") = Sheets(wsName).Cells(iX, 6).Value
.Fields("Nro") = Sheets(wsName).Cells(iX, 7).Value
.Fields("Apellidos") = Sheets(wsName).Cells(iX, 8).Value
.Fields("Nombres") = Sheets(wsName).Cells(iX, 9).Value
.Fields("Nombre_y_Apellidos") = Sheets(wsName).Cells(iX, 10).Value
.Fields("Q1P1_Deberes") = Sheets(wsName).Cells(iX, 11).Value
.Fields("Q1P1_TClase") = Sheets(wsName).Cells(iX, 12).Value
.Fields("Q1P1_TGrupal") = Sheets(wsName).Cells(iX, 13).Value
.Fields("Q1P1_Leccion") = Sheets(wsName).Cells(iX, 14).Value
.Fields("Q1P1_Prueba") = Sheets(wsName).Cells(iX, 15).Value
.Fields("Q1P1_Promedio") = Sheets(wsName).Cells(iX, 16).Value
.Fields("Q1P2_Deberes") = Sheets(wsName).Cells(iX, 17).Value
.Fields("Q1P2_TClase") = Sheets(wsName).Cells(iX, 18).Value
.Fields("Q1P2_TGrupal") = Sheets(wsName).Cells(iX, 19).Value
.Fields("Q1P2_Leccion") = Sheets(wsName).Cells(iX, 20).Value
.Fields("Q1P2_Prueba") = Sheets(wsName).Cells(iX, 21).Value
.Fields("Q1P2_Promedio") = Sheets(wsName).Cells(iX, 22).Value
.Fields("Q1P3_Deberes") = Sheets(wsName).Cells(iX, 23).Value
.Fields("Q1P3_TClase") = Sheets(wsName).Cells(iX, 24).Value
.Fields("Q1P3_TGrupal") = Sheets(wsName).Cells(iX, 25).Value
.Fields("Q1P3_Leccion") = Sheets(wsName).Cells(iX, 26).Value
.Fields("Q1P3_Prueba") = Sheets(wsName).Cells(iX, 27).Value
.Fields("Q1P3_Promedio") = Sheets(wsName).Cells(iX, 28).Value
.Fields("Q1_Promedio") = Sheets(wsName).Cells(iX, 29).Value
.Fields("Q1_80") = Sheets(wsName).Cells(iX, 30).Value
.Fields("Q1_ExQuimestral") = Sheets(wsName).Cells(iX, 31).Value
.Fields("Q1_20") = Sheets(wsName).Cells(iX, 32).Value
.Fields("Q1_NotaQuimestral") = Sheets(wsName).Cells(iX, 33).Value
.Fields("Q1_Equivalencia") = Sheets(wsName).Cells(iX, 34).Value
.Fields("Q2P1_Deberes") = Sheets(wsName).Cells(iX, 35).Value
.Fields("Q2P1_TClase") = Sheets(wsName).Cells(iX, 36).Value
.Fields("Q2P1_TGrupal") = Sheets(wsName).Cells(iX, 37).Value
.Fields("Q2P1_Leccion") = Sheets(wsName).Cells(iX, 38).Value
.Fields("Q2P1_Prueba") = Sheets(wsName).Cells(iX, 39).Value
.Fields("Q2P1_Promedio") = Sheets(wsName).Cells(iX, 40).Value
.Fields("Q2P2_Deberes") = Sheets(wsName).Cells(iX, 41).Value
.Fields("Q2P2_TClase") = Sheets(wsName).Cells(iX, 42).Value
.Fields("Q2P2_TGrupal") = Sheets(wsName).Cells(iX, 43).Value
.Fields("Q2P2_Leccion") = Sheets(wsName).Cells(iX, 44).Value
.Fields("Q2P2_Prueba") = Sheets(wsName).Cells(iX, 45).Value
.Fields("Q2P2_Promedio") = Sheets(wsName).Cells(iX, 46).Value
.Fields("Q2P3_Deberes") = Sheets(wsName).Cells(iX, 47).Value
.Fields("Q2P3_TClase") = Sheets(wsName).Cells(iX, 48).Value
.Fields("Q2P3_TGrupal") = Sheets(wsName).Cells(iX, 49).Value
.Fields("Q2P3_Leccion") = Sheets(wsName).Cells(iX, 50).Value
.Fields("Q2P3_Prueba") = Sheets(wsName).Cells(iX, 51).Value
.Fields("Q2P3_Promedio") = Sheets(wsName).Cells(iX, 52).Value
.Fields("Q2_Promedio") = Sheets(wsName).Cells(iX, 53).Value
.Fields("Q2_80") = Sheets(wsName).Cells(iX, 54).Value
.Fields("Q2_ExQuimestral") = Sheets(wsName).Cells(iX, 55).Value
.Fields("Q2_20") = Sheets(wsName).Cells(iX, 56).Value
.Fields("Q2_NotaQuimestral") = Sheets(wsName).Cells(iX, 57).Value
.Fields("Q2_Equivalencia") = Sheets(wsName).Cells(iX, 58).Value
.Fields("PromedioAnual") = Sheets(wsName).Cells(iX, 59).Value
.Fields("Supl_Rem") = Sheets(wsName).Cells(iX, 60).Value
.Fields("Promedio_Final") = Sheets(wsName).Cells(iX, 60).Value
.Fields("Estado") = Sheets(wsName).Cells(iX, 60).Value
'----------------------------------------------------------------
.Update
End With
RecSet.Close
Set RecSet = Nothing
End If
Next iX
Conn.Close

'AQUÍ ME DA UN ERROR: No se puede cerrar un objeto Connection mientras se realiza

'una transacción.

Set Conn = Nothing
End Sub

Añade tu respuesta

Haz clic para o