May i ask how to let users convert to mdb files by excel according to the worksheet name of the excel file

[christing]

I wrote the code about converting exel files into mdb files. below is my code.

' delete the file with the same and create a new access file
If File.Exists("D:\mydata.mdb") Then
File.Delete("D:\mydata.mdb")
End If

Dim _accessData As Access.Application
_accessData = New Access.Application()
_accessData.Visible = False
_accessData.NewCurrentDatabase("D:\mydata.mdb", Access.AcNewDatabaseFormat.acNewDatabaseFormatAccess2000, , , )

_accessData.CloseCurrentDatabase()
_accessData.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll)
_accessData = Nothing

' initialize the connect string
Dim Path As String
Path = OpenFile()
Dim ComboBox As String
ComboBox = ComboBox1.Text
Dim _conn As String
_conn = "provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;';"
Dim _connection As OleDbConnection = New OleDbConnection(_conn)

'Use OledbCommand object to select all the data from sheet1 and execute an ExecuteNonQuery to import data into .mdb.
Dim _command As OleDbCommand = New OleDbCommand()
_command.Connection = _connection

Try

_command.CommandText = "SELECT * INTO [MS Access;Database=D:\mydata.mdb]" + ComboBox + ";From" + ComboBox + "[$A1:S]"
_connection.Open()
_command.ExecuteNonQuery()
_connection.Close()
MessageBox.Show("Convert File Successful!")

Catch e1 As Exception
MessageBox.Show("Import Failed, correct Column name in the sheet!" & Environment.NewLine & "Error Message:" & Environment.NewLine & e1.Message)
End Try
End Sub


i get an error message on this code



can someone help me take a look and guide me thanks a lot

Continue reading...