Click here to Skip to main content
14,421,405 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi Friends,

Here i m have some problem were export the data which in excel to the format of .mdb. i m trying the code were showing below, but it shows the messageBox "{Import Failed, correct Column name in the sheet!, Error Message: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine}" . is that anyone can help me.

Best Regards,
Thanes

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    ' delete the file with the same and create a new access file
    If File.Exists("C:\Users\Admin\Desktop\test\CA\book.mdb") Then
        File.Delete("C:\Users\Admin\Desktop\test\CA\book.mdb")
    End If

    Dim _accessData As Access.Application
    _accessData = New Access.Application()
    _accessData.Visible = False
    _accessData.NewCurrentDatabase("C:\Users\Admin\Desktop\test\CA\book.mdb", Access.AcNewDatabaseFormat.acNewDatabaseFormatAccess2000, , , )

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

    ' initialize the connect string
    Dim _filename As String = "C:\Users\Admin\Desktop\test\CA\test.xls"
    Dim _conn As String
    _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Users\Admin\Desktop\test\CA\test.xls" & _filename & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;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 Book.mdb.
    Dim _command As OleDbCommand = New OleDbCommand()
    _command.Connection = _connection

    Try
        _command.CommandText = "SELECT * INTO [MS Access;Database=C:\Users\Admin\Desktop\test\CA\book.mdb].[Sheet1] FROM [Sheet1$A4:D]"
        _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!" & Environment.NewLine & "Error Message:" & Environment.NewLine & e1.Message)
    End Try

End Sub,


*Note: the program is can create .mdb file, but cannot import the excel data into it.
Posted
Updated 24-Aug-15 23:16pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Before proceeding further make sure column names in the excel should be same as in the .mdb file. You are getting this error because your system don't have Access Database Engine. Install Access Database Engine and restart your system.(if it's asked for).
   
Comments
cgthanes 25-Aug-15 5:10am
   
Hi Sarath.
Yes i m ask for it. But it still have the same error. is that any other suggestion.

Regards,
Thanes
Sarath kumar.N 25-Aug-15 5:13am
   
https://www.microsoft.com/en-in/download/details.aspx?id=23734 try to install this..
Sarath kumar.N 25-Aug-15 5:11am
   
Did u restarted the system?
cgthanes 25-Aug-15 5:33am
   
yes i m already restart the PC. Same error also. Also install ready the link were you gives. Any other suggestion. Please help me....
Sarath kumar.N 25-Aug-15 5:42am
   
This error is only because of the above I mentioned. I will share another link.I'm this will help u.
Sarath kumar.N 25-Aug-15 5:43am
   
your system 32/64 bit?
cgthanes 25-Aug-15 5:44am
   
64 bits...
Sarath kumar.N 25-Aug-15 5:46am
   
https://onedrive.live.com/redir?resid=EB5A2B7F9D52B1C5!2487&authkey=!AFKpWtqB5tdK-Uk&ithint=folder%2c download this and install.
cgthanes 25-Aug-15 6:25am
   
i cannot install the 64 bits access database, only can install 32 bits access database, but my pc is 64 bits...
Sarath kumar.N 25-Aug-15 6:28am
   
Anyway in that i included both versions. Did u tried
cgthanes 25-Aug-15 20:38pm
   
yes i m try it ready, but still cannot, shows the same error.
Rate this:
Please Sign up or sign in to vote.

Solution 2

SELECT…INTO Statement (Microsoft Access SQL)[^] is wrong!

SELECT * INTO [MS Access;Database=C:\Users\Admin\Desktop\test\CA\book.mdb].[Sheet1] FROM [Sheet1$A4:D <missed final row> ]"


I do strongly recommend to define set of fields to be imported.

SELECT <Field_names> INTO ... 
FROM ...


It might help you to undestand how to fetch data from MS Excel files: How to: Get Data from Multiple Workbooks using One OleDbConnection?[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100