473,832 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Converting Excel Data to MSAccess .MDB in VB.NET

2 New Member
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.


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
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")

'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
MessageBox.Show (strmsg, "OleDBException ", MessageBoxButto ns.OK, MessageBoxIcon. Error)
End Try
End While
sourceReader.Cl ose()
sourceCon.Close ()
targetCommand.C onnection.Close ()
End Sub
May 4 '06 #1
1 11381
64 New Member
dear friend,

I'm also stucked in the same problem and my problem is that........... .....it's related to my job.I guess,if I can't solve this problem,I'll loose my job.

I guess,you have solved the problem already cause your post is quite old.So, please please please give me your code and please advice me how to do it.

My e-mail address is: zerinzafrin[at]gmail[dot]com
Dec 4 '06 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Nagarajan .A | last post by:
I need to read an excel file, and add that data into msaccess database. Can anyone help me? Thanks in advance. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!
by: CLarkou | last post by:
In Office XP, I am sending data from MSAccess to Excel, via DDE commands and by using excel commands .CELLS(). Both ways are used in the same procedure. On all machines it works except on a machine with French Office XP. Commands of DDE for page setup works but not the ones for sending data (DDEPOKE). I got the international characters for Excel row and column. What could be the problem for DDEPOKE command to fail ? I get the error 285,...
by: Andi Plotsky | last post by:
I have an Access app (running under Access2000) where I want to export the data to an Excel spreadsheet. This has worked fine in the past, but after taking it to my client site (where I ran it under Access2002 - but left it as an Access2000 database), I get the following error message. "Class does not support automation or does not support expected interface". THE LINE IT FAILS ON IS: Wks.range ("A2").CopyFromRecordset rs
by: Ramakrishnan Nagarajan | last post by:
Hi, I am converting Excel data into a Dataset in C#. There are around 24 columns in the Excel Sheet. First I tried to insert one row with correct values in the Excel sheet. i.e. for text columns I entered text values and for numeric columns I entered numeric values. It works fine and pass through all the validation checks and gets inserted into the database successfully. But when I gave some junk values in the excel sheet and tried to...
by: David | last post by:
It looks like this is a very popular subject, but all the previous messages have been deleated from the server. In my application, I need to export a table in my MSAccess database to an Excel 2003 spreadsheet. I have no trouble with individual ranges, but am having trouble finding a good example where the data from an entire table is written to the worksheet. Thanks in advance for any suggestions. David
by: UKuser | last post by:
Hi Guys, I have a program which converts Excel spreadsheets to Javascript and allows interactivity. However it can't convert it to PHP, which is obviously better for users to view (in case J/S is turned off). How would I go about converting some of this created code (example JS below), or is there an easier way to get PHP to do the calculations itself? I am aware of an excel-server product but this is too expensive and doesnt actually...
by: garry.oxnard | last post by:
Can anyone help me to solve a problem which involves switching from Access to Excel (then back to Access) programatically please? I have an Excel template which, on open, also opens an Access database - containing a list of addresses. Sequence of events is = (1) Excel template opens in its default XXX.xls filename. (2) Code runs to save the spreadsheet as XXX.xls. (3) User clicks a button to open an Access database containing an
by: stronghold888 | last post by:
Hello everyone, I created a database in Access and on my form, I inserted a button that exports data into a ready Excel template. The export works fine, but I have one problem and it's from my code. First of all, the data from the Access database is inserted in an Excel calendar. The calendar is numbered with days 36 to -18. By exemple, if a calendar statement is for day 36, then it will be inserted in the specific column. For now, statements...
by: beulajo | last post by:
Hai. I have student marks in excel sheet. I need to export this data in to msaccess. I have no problem in exporting the data. My excel sheet is in this format I have 40 students. Each student had attended 9 tests which has 5 criteria, under which marks had been given(10). one sample data is B0841 Albert Raja A. 0 0 0 0 0 0 0 0 0 0.5 2 2 0 0 1
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.