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.

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
                [B]targetCommand.ExecuteNonQuery()[/B]

            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

Edited 7 Years Ago by __avd: Corrected code tags; Use [code] tags to wrap your source code.

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.

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
                [B]targetCommand.ExecuteNonQuery()[/B]

            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

Hey man this is kinda old so I dont know if you figured it out or even still check this, but the answer you are looking for is:

No matter where it is, access, VB, or Excel, chande the word desc to something like descrip or some thing, and it should work

hi,

have u got the solution for converting excel to access.
I am also working on such a matter . Can u help me to solve this problem.
i dont know hw the process should start??

thanxs in advance..

This article has been dead for over six months. Start a new discussion instead.