I have a "method execute of object _ connection" failed after running the below code
for moving excel sheet into access table.
i think it fail with this statement
Code:
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
Code:
Public Sub DoTrans()
Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "\CPoad.accdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "provider='Microsoft.ACE.OLEDB.12.0';" & _
"Data Source = " + dbPath
dsh = "[" & Application.ActiveSheet.Name & "$]"
cn.Open scn
ssql = "INSERT INTO tblP_upload ([Count], [MT], [FPmount], [Fold]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
cn.Execute ssql
Set cn = Nothing
End Sub