Question convertion of ms excel 2007 into ms access db

[sai87]

hi all,

i am new to vb.net programming i am trying to export an excel worksheet into access database with vb.net i didn't have any code if u have any resources of code or any idea how should i implement this please help me out.

 

[Satal Keto]

Is this something that you're going to be doing frequently or are you only doing this once?

If you're only doing this once or a couple times then maybe this link could be of use to you.
http://office.microsoft.com/en-us/excel/HP052008521033.aspx

Although if you want to do it more than a few times then we'll come up with some code for you

UPDATE:
Well I thought that I would provide a link to where you can find an example of importing data from Excel into Access through VB.Net.
Import data from Excel to Access using ADO.Net

 

[sai87]

export data from ms excel to ms access through vb.net

hi all,

i am new to vb.net i am trying to export data from ms excel to ms access through vb.net and i am using code below its not working and saying output as msgbox "Import Failed, correct Column name in the sheet!" i didnt get this please try to help me out.it is creating ms access file but with out any data.
my code is:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim _accessData As Access.Application
_accessData = New Access.ApplicationClass()
_accessData.Visible = False
_accessData.NewCurrentDatabase("C:\Book.accdb")
_accessData.CloseCurrentDatabase()
_accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll)
_accessData = Nothing
Dim _filename As String = "C:\Book.xlsx"
Dim _conn As String
_conn = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;"
Dim _connection As OleDbConnection = New OleDbConnection(_conn)
Dim _command As OleDbCommand = New OleDbCommand()
_command.Connection = _connection
Try
_command.CommandText = "SELECT * INTO [MS Access;Database=C:\Book.accdb].[Sheet1] FROM [Sheet1$]"
_connection.Open()
_command.ExecuteNonQuery()
_connection.Close()
MessageBox.Show("The import is complete!")
Catch e1 As Exception
MessageBox.Show("Import Failed, correct Column name in the sheet!")
End Try
End Sub

 

[sai87]

Export data from ms excel to ms access using vb.net

hi all,
i am new to vb.net programming and i am trying to export data from excel to ms access through vb.net. can u please help me out reg: any resources or any ideas.

 

[Satal Keto]

This is the third time you have asked this question.
The first time you asked the question I answered, please look at that thread. If there is something you don't understand in that thread then post your questions about my answer in that thread.

Your original question

UPDATE:
Please note when this was posted the OP had three separate threads, which JohnH has now merged into one

 

[sai87]

error: 'FillAccessDatabase' is not declared.

hi all,
thanks i am using the same code u guided me i am writing code in vb windows form i am getting error as :
'FillAccessDatabase' is not declared.
please try to help me out.
i have added all references also and my code is :
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim _accessData As Access.Application
_accessData = New Access.ApplicationClass()
_accessData.Visible = False
_accessData.NewCurrentDatabase("C:\Book.mdb")
_accessData.CloseCurrentDatabase()
_accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll)
_accessData = Nothing
Dim filename As String = "C:\Book.xls"
Dim _connection As OleDbConnection = MakeExcelConnection(filename)

FillAccessDatabase(_connection)
Dim _command As OleDbCommand = New OleDbCommand()
_command.Connection = _connection
Try
_command.CommandText = "SELECT * INTO [MS Access;Database=C:\Book.mdb].[Sheet1] FROM [Sheet1$]"
_connection.Open()
_command.ExecuteNonQuery()
_connection.Close()
MessageBox.Show("The import is complete!")
Catch e1 As Exception
MessageBox.Show("Import Failed, correct Column name in the sheet!")
End Try
End Sub

Private Shared Function MakeExcelConnection(ByVal fileName As String) As OleDbConnection
Dim _conn As String
_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fileName & ";" & "Extended Properties=Excel 8.0;"
Dim _connection As OleDbConnection = New OleDbConnection(_conn)
Return _connection
End Function

 

[Satal Keto]

Please when you post code use the code tags as its painful to try and read your code otherwise.

Well I can see that you call "FillAccessDatabase" but I don't see that function declared anywhere, can you provide the code (in code tags) that you have for that function?

 

[sai87]

Please when you post code use the code tags as its painful to try and read your code otherwise.

Well I can see that you call "FillAccessDatabase" but I don't see that function declared anywhere, can you provide the code (in code tags) that you have for that function?

how should i declare this "FillAccessDatabase" function in my code please try to help i was bit confused,

 

[Satal Keto]

Ow dear the code I linked you to seems to have the problem that it doesn't contain all the code it references.
To be honest I can't see what that function would do, try commenting it out and see if it works, if it doesn't let me know and I'll see if I can find you another example piece of code.

 

[sai87]

reply: function FillAccessDatabase

Ow dear the code I linked you to seems to have the problem that it doesn't contain all the code it references.
To be honest I can't see what that function would do, try commenting it out and see if it works, if it doesn't let me know and I'll see if I can find you another example piece of code.

thanks
i tried commenting it out and it does not work there is function call but function was not declared in the code. some code might be missing in this i am trying it to declare on my own but didn't work. so please try to help me out with some other code.