none
transfer excel data to access 2007 using vb.net RRS feed

  • Question

  • I have data in excel that I would like to automatically transfer to a new table in an access 2007 database.
    Sunday, June 28, 2009 7:43 PM

Answers

  • You could use SQL to do this:

            Dim AccessConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test Files\Database1.accdb;")
    
            Try
                AccessConnection.Open()
    
                'New table
                Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Data] FROM [Excel 8.0;DATABASE=C:\Test Files\Book10.xls;HDR=NO;IMEX=1].[Sheet1$]", AccessConnection)
                'Existing table
                'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [Data] (Col1, Col2, Col3, Col4) SELECT F1, F2, F3, F4 from [Excel 8.0;DATABASE=C:\Test Files\Book10.xls;HDR=No;IMEX=1].[Sheet1$];", AccessConnection)
                AccessCommand.ExecuteNonQuery()
                AccessConnection.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                AccessConnection.Close()
            End Try

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, June 29, 2009 3:19 PM

All replies

  • You could try to open Access and import data from Excel. Another way is to connect to Excel file from the code using ACE OLEDB provider, load data into DataSet and then insert records into Access database one-by-one using same ACE provider.
    Val Mazur (MVP) http://www.xporttools.net
    Monday, June 29, 2009 10:20 AM
    Moderator
  • You could use SQL to do this:

            Dim AccessConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test Files\Database1.accdb;")
    
            Try
                AccessConnection.Open()
    
                'New table
                Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Data] FROM [Excel 8.0;DATABASE=C:\Test Files\Book10.xls;HDR=NO;IMEX=1].[Sheet1$]", AccessConnection)
                'Existing table
                'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [Data] (Col1, Col2, Col3, Col4) SELECT F1, F2, F3, F4 from [Excel 8.0;DATABASE=C:\Test Files\Book10.xls;HDR=No;IMEX=1].[Sheet1$];", AccessConnection)
                AccessCommand.ExecuteNonQuery()
                AccessConnection.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                AccessConnection.Close()
            End Try

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, June 29, 2009 3:19 PM
  • Thanks Paul you solution worked.  I had similar code, but for some reason it kept failing.  I am creating a solution where VB does all the importing and then runs comparision report based on 3 tables 2 from excel and 1 from access.
    Thursday, July 2, 2009 3:38 PM