Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Appending data from excel to access using VBA

  1. #1
    New Member
    Join Date
    Mar 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Appending data from excel to access using VBA

    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], [b], [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

  2. #2
    New Member
    Join Date
    Mar 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Appending data from excel to access using VBA

    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], [b], [c]) "
    ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh




    cn.Execute ssql




    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •