Hi,
Can someone help me on the error when I run my macro in exporting data from excel to access. I am getting a run-time error. some of the cells are empty/Null and won't allow me to transfer all the values in access. Would appreciate your help on this.
Run Time Error.PNG
Sub PushTableToAccess()
Dim cnn As ADODB.Connection
Dim MyConn
Dim rst As ADODB.Recordset
Dim i As Long, j As Long
Dim Rw As Long
Sheets("EMP").Activate
Rw = Range("A60000").End(xlUp).Row
Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:="tblEmployee", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdTable
'Load all records from Excel to Access.
For i = 2 To Rw
rst.AddNew
For j = 1 To 15
rst(Cells(1, j).Value) = Cells(i, j).Value
Next j
rst.Update
Next i
' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Bookmarks