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(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles Button1.Click
mstrInputXLSFil e = Application.Sta rtupPath + "\XLS_normalize d_format.xls"
TextBox1.Text = mstrInputXLSFil e
End Sub
Private Sub Button2_Click(B yVal sender As System.Object, ByVal e As System.EventArg s) Handles Button2.Click
Dim mExcelFile As String = mstrInputXLSFil e
Dim mAccessFile As String = Application.Sta rtupPath & "\access_schema .mdb"
Dim mWorkSheet As String
mWorkSheet = "Sheet1"
Dim mTableName As String
mTableName = "Terms"
ExcelToAccess(m strInputXLSFile , mWorkSheet, mAccessFile, mTableName)
'Dim mDataBase As DAO.Database
'mDataBase = DAODBEngine_def inst.OpenDataba se(mExcelFile, True, False, "Excel 5.0;HDR=Yes;IME X=1")
'mDataBase.Exec ute("Select Term, Description, Comments into [;database=" & mAccessFile & "]." & mTableName & " FROM [" & mWorkSheet & "$]")
'MsgBox("Done. Use Access to view " & mTableName)
End Sub
Private Sub ExcelToAccess(B yVal sourceFile As String, ByVal sourceSheet As String, ByVal targetFile As String, ByVal targetTable As String)
Dim mExcelFile As String = mstrInputXLSFil e
Dim mAccessFile As String = Application.Sta rtupPath & "\access_schema .mdb"
Dim mTableName As String
mTableName = "Terms"
Dim mWorkSheet As String
mWorkSheet = "Sheet1"
''''Sub transfers all records from .xls sourcefile.sour cesheet ...
''''... to .mdb targetfile.targ ettable
''''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.Ends With("$") Then
mWorkSheet &= "$"
End If
Dim sourceConStr As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & mExcelFile & ";Extended Properties=""Ex cel 8.0;HDR=Yes;IME X=1"""
Dim sourceSQL As String = "SELECT term, description, Comments FROM [" & mWorkSheet & "]"
Dim targetConStr As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & mAccessFile & ";User Id=admin;Passwo rd=;"
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 OleDbDataAdapte r(targetSQL, targetCon)
Dim cb As New OleDbCommandBui lder(targetDA)
Dim targetCommand As OleDbCommand = cb.GetInsertCom mand
'To Know the source and target values......... ....
MsgBox(targetCo mmand.CommandTe xt)
'Debug.WriteLin e(targetCommand .CommandText)
'now do the work
Dim sourceCon As New OleDbConnection (sourceConStr)
Dim sourceCommand As New OleDbCommand(so urceSQL, sourceCon)
targetCommand.C onnection.Open( )
sourceCon.Open( )
Dim sourceReader As OleDbDataReader
sourceReader = sourceCommand.E xecuteReader()
While sourceReader.Re ad() 'for each row from source
Try
For i As Integer = 0 To sourceReader.Fi eldCount - 1
'load values into parameters
''Only if .xls columns match exactly to .MDB Table Fields
'targetCommand. Parameters(i).V alue = sourceReader(i)
'' If there is no Exact Match than give "targetfieldnum ber" and respective Sourcefeildname or number like:
''targetCommand .Parameters(<Ac cessFieldNumber >).Value = sourceReader.It em(<ExcelFieldN ame>)
''targetCommand .Parameters(<Ac cessFieldNumber >).Value = sourceReader.It em(<ExcelFieldN umber>)
targetCommand.P arameters(1).Va lue = sourceReader.It em("Term")
targetCommand.P arameters(2).Va lue = sourceReader.It em("desc")
Next
'then write to target
targetCommand.E xecuteNonQuery( )
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 ", MessageBoxButto ns.OK, MessageBoxIcon. Error)
End Try
End While
sourceReader.Cl ose()
sourceCon.Close ()
targetCommand.C onnection.Close ()
End Sub
