2
Hi All,
I have to convert Excel data to MSAcess .MDB file using VB.NET.
VB.NET Code read the Excel file and write it 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.
Also it shows error of "syntax error in INSERT INTO Command.
Coding:
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>).Va lue = sourceReader.Item(<ExcelFieldName>)
''targetCommand.Parameters(<AccessFieldNumber>).Va lue = 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
