Originally Posted by ShailShin

Hi All,

I have to convert Excel data to MSAcess .MDB file using VB.NET.
VB.NET Code read the Excel fi;le and write ti to .MDB file. For the same I have below code, but I am stuck at the writing it to .MDB file which I have already created with Table as Terms which contains 10 Fields and my Excel contains 10 Fields, Here I want to Extract 3 columns data from Excel to Table.

For More Information about Excel Columns are:

term, description, image, comments

Also for .MDB Table Contains:

term_id, term, desc, comments

Here I have to Extract Data from Excel for term description and comments and stored it in .MDB Terms table.

Help with Code Tags

(Toggle Plain Text)

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        mstrInputXLSFile = Application.StartupPath + "\XLS_normalized_format.xls"
        TextBox1.Text = mstrInputXLSFile
    End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim mExcelFile As String = mstrInputXLSFile
        Dim mAccessFile As String = Application.StartupPath & "\access_schema.mdb"

        Dim mWorkSheet As String
        mWorkSheet = "Sheet1"

        Dim mTableName As String
        mTableName = "Terms"

        ExcelToAccess(mstrInputXLSFile, mWorkSheet, mAccessFile, mTableName)

        'Dim mDataBase As DAO.Database
        'mDataBase = DAODBEngine_definst.OpenDatabase(mExcelFile, True, False, "Excel 5.0;HDR=Yes;IMEX=1")
        'mDataBase.Execute("Select Term, Description, Comments into [;database=" & mAccessFile & "]." & mTableName & " FROM [" & mWorkSheet & "$]")
        'MsgBox("Done.  Use Access to view " & mTableName)
    End Sub
 Private Sub ExcelToAccess(ByVal sourceFile As String, ByVal sourceSheet As String, ByVal targetFile As String, ByVal targetTable As String)

        Dim mExcelFile As String = mstrInputXLSFile
        Dim mAccessFile As String = Application.StartupPath & "\access_schema.mdb"

        Dim mTableName As String
        mTableName = "Terms"

        Dim mWorkSheet As String
        mWorkSheet = "Sheet1"

        ''''Sub transfers all records from .xls sourcefile.sourcesheet ...
        ''''... to .mdb targetfile.targettable
        ''''It is assumed that the .mdb targettable definition already ...
        ''''... exists, with the same number and types of fields, ...
        ''''... in the same order, as the .xls worksheet columns.
        ''''It does not depend on the .mdb field names being the same ...
        ''''... as the .xls column headings: although it does assume ...
        ''''... that the .xls columns are named.

        If Not mWorkSheet.EndsWith("$") Then
            mWorkSheet &= "$"
        End If

        Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mExcelFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
        Dim sourceSQL As String = "SELECT term, description, Comments FROM [" & mWorkSheet & "]"
        Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mAccessFile & ";User Id=admin;Password=;"
        Dim targetSQL As String = "SELECT term, desc, Comments FROM " & mTableName

        'use dataadapter for target and command builder to ...
        '... create insert command, including parameter collection
        Dim targetCon As New OleDbConnection(targetConStr)
        Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
        Dim cb As New OleDbCommandBuilder(targetDA)
        Dim targetCommand As OleDbCommand = cb.GetInsertCommand
        'To Know the source and target values.............
        MsgBox(targetCommand.CommandText)
        'Debug.WriteLine(targetCommand.CommandText)

        'now do the work
        Dim sourceCon As New OleDbConnection(sourceConStr)
        Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
        targetCommand.Connection.Open()
        sourceCon.Open()
        Dim sourceReader As OleDbDataReader
        sourceReader = sourceCommand.ExecuteReader()
        While sourceReader.Read() 'for each row from source
            Try
                For i As Integer = 0 To sourceReader.FieldCount - 1
                    'load values into parameters

                    ''Only if .xls columns match exactly to .MDB Table Fields
                    'targetCommand.Parameters(i).Value = sourceReader(i)

                    '' If there is no Exact Match than give "targetfieldnumber" and respective Sourcefeildname or number like:

                    ''targetCommand.Parameters(<AccessFieldNumber>).Value = sourceReader.Item(<ExcelFieldName>)
                    ''targetCommand.Parameters(<AccessFieldNumber>).Value = sourceReader.Item(<ExcelFieldNumber>)

                    targetCommand.Parameters(1).Value = sourceReader.Item("Term")
                    targetCommand.Parameters(2).Value = sourceReader.Item("desc")
                Next

                'then write to target
                targetCommand.ExecuteNonQuery()

            Catch ex As OleDbException
                Dim dbe As OleDbError
                Dim strmsg As String
                For Each dbe In ex.Errors
                    strmsg &= "SQL Error: " & dbe.Message & vbCrLf
                Next
                MessageBox.Show(strmsg, "OleDBException", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End While
        sourceReader.Close()
        sourceCon.Close()
        targetCommand.Connection.Close()
    End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click mstrInputXLSFile = Application.StartupPath + "\XLS_normalized_format.xls" TextBox1.Text = mstrInputXLSFile End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim mExcelFile As String = mstrInputXLSFile Dim mAccessFile As String = Application.StartupPath & "\access_schema.mdb" Dim mWorkSheet As String mWorkSheet = "Sheet1" Dim mTableName As String mTableName = "Terms" ExcelToAccess(mstrInputXLSFile, mWorkSheet, mAccessFile, mTableName) 'Dim mDataBase As DAO.Database 'mDataBase = DAODBEngine_definst.OpenDatabase(mExcelFile, True, False, "Excel 5.0;HDR=Yes;IMEX=1") 'mDataBase.Execute("Select Term, Description, Comments into [;database=" & mAccessFile & "]." & mTableName & " FROM [" & mWorkSheet & "$]") 'MsgBox("Done. Use Access to view " & mTableName) End Sub Private Sub ExcelToAccess(ByVal sourceFile As String, ByVal sourceSheet As String, ByVal targetFile As String, ByVal targetTable As String) Dim mExcelFile As String = mstrInputXLSFile Dim mAccessFile As String = Application.StartupPath & "\access_schema.mdb" Dim mTableName As String mTableName = "Terms" Dim mWorkSheet As String mWorkSheet = "Sheet1" ''''Sub transfers all records from .xls sourcefile.sourcesheet ... ''''... to .mdb targetfile.targettable ''''It is assumed that the .mdb targettable definition already ... ''''... exists, with the same number and types of fields, ... ''''... in the same order, as the .xls worksheet columns. ''''It does not depend on the .mdb field names being the same ... ''''... as the .xls column headings: although it does assume ... ''''... that the .xls columns are named. If Not mWorkSheet.EndsWith("$") Then mWorkSheet &= "$" End If Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mExcelFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" Dim sourceSQL As String = "SELECT term, description, Comments FROM [" & mWorkSheet & "]" Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mAccessFile & ";User Id=admin;Password=;" Dim targetSQL As String = "SELECT term, desc, Comments FROM " & mTableName 'use dataadapter for target and command builder to ... '... create insert command, including parameter collection Dim targetCon As New OleDbConnection(targetConStr) Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon) Dim cb As New OleDbCommandBuilder(targetDA) Dim targetCommand As OleDbCommand = cb.GetInsertCommand 'To Know the source and target values............. MsgBox(targetCommand.CommandText) 'Debug.WriteLine(targetCommand.CommandText) 'now do the work Dim sourceCon As New OleDbConnection(sourceConStr) Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon) targetCommand.Connection.Open() sourceCon.Open() Dim sourceReader As OleDbDataReader sourceReader = sourceCommand.ExecuteReader() While sourceReader.Read() 'for each row from source Try For i As Integer = 0 To sourceReader.FieldCount - 1 'load values into parameters ''Only if .xls columns match exactly to .MDB Table Fields 'targetCommand.Parameters(i).Value = sourceReader(i) '' If there is no Exact Match than give "targetfieldnumber" and respective Sourcefeildname or number like: ''targetCommand.Parameters(<AccessFieldNumber>).Value = sourceReader.Item(<ExcelFieldName>) ''targetCommand.Parameters(<AccessFieldNumber>).Value = sourceReader.Item(<ExcelFieldNumber>) targetCommand.Parameters(1).Value = sourceReader.Item("Term") targetCommand.Parameters(2).Value = sourceReader.Item("desc") Next 'then write to target targetCommand.ExecuteNonQuery() Catch ex As OleDbException Dim dbe As OleDbError Dim strmsg As String For Each dbe In ex.Errors strmsg &= "SQL Error: " & dbe.Message & vbCrLf Next MessageBox.Show(strmsg, "OleDBException", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End While sourceReader.Close() sourceCon.Close() targetCommand.Connection.Close() End Sub