Appending data from excel to access using VBA

[Yusheel]

Hi all

Im trying to append data to send data from an excel spreadsheet to an access database using VBA, browsing the forums I found some old posts that illustrate how to do so, but I cant seem to get it to work.Please note I have never coded in Access before. Using excel and access 2007.

My code:

Public Sub DoTrans()


Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")
Set cn = CreateObject("ADODB.Connection")
dbpath = "C:\mytemp\DatabaseYush.accdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" & dbpath
dsh = "[" & Application.ActiveSheet.Name & "$]"
Call appAccess.OpenCurrentDatabase(dbpath)
appAccess.Visible = True
ssql = "INSERT INTO Mytable ([a], , [c])"
ssql = ssql & " SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh



'stuck here it says "operation is not allowed when object is closed". I tried to manually open the table and then run that portion but no luck
cn.Execute ssql




End Sub

 

[Yusheel]

solved:


Public Sub DoTrans2()




Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "\DatabaseYush.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 PRIDE ([a], , [c]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh




cn.Execute ssql




End Sub