none
Insert excel data into existing MS Access table

    Question

  • Hello.

    I have this piece of code which picks the data in an excel file. At the moment i have the data into the dt table. Can someone please explain how do i write the data in dt to FECHO_UNICRE table at the UTLT.accdb database? I want to fill the table just after deleting the existing records.

    Thanks in advance for any kind help.

    Octavio

        Private Sub Unicre_Calculos_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            'Try
            Dim ConnStrg = "Provider=Microsoft.ACE.OLEDB.12.0; data source=C:\Unicre\Fecho.xlsx;Extended Properties=""Excel 12.0; HDR=Yes;"""
            Dim dt As New DataTable
            Using cn As New OleDbConnection With {.ConnectionString = ConnStrg}
                cn.Open()
                Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", cn)
                dt.Load(cmd.ExecuteReader)
            End Using
            If dt.Rows.Count > 0 Then
                Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Boxus\Geral\Dados\UTLT.accdb;Persist Security Info=False")
                Dim acCmd As New OleDbCommand("DELETE FROM FECHO_UNICRE", conn)
                acCmd.ExecuteNonQuery()
            Else
                MsgBox("O ficheiro não tem dados", vbOKOnly)
                Exit Sub
            End If
            'Catch ex As Exception
            '   MsgBox(ex.Message).ToString()
            'End Try
        End Sub

    Friday, March 23, 2012 12:29 PM

Answers

  • I can give you this way:

    ' Form Level Declaration
    Const ACCESS_FULLPATH As String = "D:\FOLDER\xx.accdb"
    Const EXCEL_FULLPATH  As String = "D:\FOLDER\excel.xls"
    Dim   objAccess = CreateObject("Access.Application")
    
    'btn_click()
    Dim connStrg = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ACCESS_FULLPATH
    
    ' Link excel sheet to access database
    objAccess.Application.OpenCurrentDatabase(ACCESS_FULLPATH)
    objAccess.docmd.TransferSpreadsheet  (2, 8,"Sheet1", EXCEL_FULLPATH, True, "")
    
    Dim conn As New OleDbConnection(connStrg)
    conn.Open
    ' Insert into access table from linked excel sheet
    Dim comm As New OleDbCommand("INSERT INTO FECHO_UNICRE SELECT [Sheet1].* FROM [Sheet1]",conn)
    comm.ExecuteNonQuery
    
    objAccess.DoCmd.DeleteObject(0,"Sheet1")
    objAccess.Quit


    • Marked as answer by octeixeira Friday, March 23, 2012 4:57 PM
    Friday, March 23, 2012 2:49 PM
  • Thanks a lot. It works perfectly.

    Octavio

    • Marked as answer by octeixeira Friday, March 23, 2012 4:56 PM
    Friday, March 23, 2012 4:55 PM

All replies

  • I can give you this way:

    ' Form Level Declaration
    Const ACCESS_FULLPATH As String = "D:\FOLDER\xx.accdb"
    Const EXCEL_FULLPATH  As String = "D:\FOLDER\excel.xls"
    Dim   objAccess = CreateObject("Access.Application")
    
    'btn_click()
    Dim connStrg = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ACCESS_FULLPATH
    
    ' Link excel sheet to access database
    objAccess.Application.OpenCurrentDatabase(ACCESS_FULLPATH)
    objAccess.docmd.TransferSpreadsheet  (2, 8,"Sheet1", EXCEL_FULLPATH, True, "")
    
    Dim conn As New OleDbConnection(connStrg)
    conn.Open
    ' Insert into access table from linked excel sheet
    Dim comm As New OleDbCommand("INSERT INTO FECHO_UNICRE SELECT [Sheet1].* FROM [Sheet1]",conn)
    comm.ExecuteNonQuery
    
    objAccess.DoCmd.DeleteObject(0,"Sheet1")
    objAccess.Quit


    • Marked as answer by octeixeira Friday, March 23, 2012 4:57 PM
    Friday, March 23, 2012 2:49 PM
  • Hi, have you considered using Transferspreedsheet:

    http://msdn.microsoft.com/en-us/library/bb214134(v=office.12).aspx

    You may have to automate the msaccess to get at DoCmd

    rupex

    Friday, March 23, 2012 2:52 PM
  • Thanks a lot. It works perfectly.

    Octavio

    • Marked as answer by octeixeira Friday, March 23, 2012 4:56 PM
    Friday, March 23, 2012 4:55 PM