I am using MS Excel 2010 to store data in MS Access 2010 table WorkingData. This routine worked fine previously but now it doesn't. I have tried with other table but the error seem to be table specific. Can anyone tell me what may be causing the error. I have dropped and created the table but that does not help.
Dim cnn As ADODB.Connection
Dim MyConn
Dim rst2 As ADODB.Recordset
Dim i As Long, j As Long, r As Long
Dim Rw As Long
Dim cmd As ADODB.Command
Dim dtmDate As Date
Dim nPeriod As Integer
....
'Give your Excel Sheet data Range
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 rst2 = New ADODB.Recordset
rst2.CursorLocation = adUseServer
rst2.Open Source:="WorkingData", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable