Ace.OLEDB.12.0 vs Jet.OLEDB.4.0

Technical support for Spire.DataExport

Moderators: iceblue support, DataExport, Manager

Ace.OLEDB.12.0 vs Jet.OLEDB.4.0

Postby aschadek » Thu Jun 06, 2013 10:22 pm

I'm receiving the following error when attempting to write data to an Access database using Spire.DataExport (Free) in VB.NET.

Code: Select all
The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.


I need to export the data to a Excel 2007+ database file and Jet.4.0 simply can't do it. Is there any way to change the provider used by Spire to Ace.OLEDB.12.0? Or are there any other options that you can suggest trying?

Below is the code relative to Spire.DataExport. The sqlConn element is correctly connecting to a SQL server and getting results.

Code: Select all
            Dim accessExport1 As Spire.DataExport.Access.AccessExport = New Access.AccessExport
            accessExport1.CreateTable = True
            accessExport1.DatabaseName = MdbPath
            accessExport1.TableName = "docinfo"
            accessExport1.SQLCommand = sqlCmd
            Using sqlConn
                sqlConn.Open()
                accessExport1.SaveToFile()
                sqlConn.Close()
            End Using
aschadek
 
Posts: 2
Joined: Thu Jun 06, 2013 8:06 pm

Re: Ace.OLEDB.12.0 vs Jet.OLEDB.4.0

Postby Gary.zhang » Fri Jun 07, 2013 8:23 am

Hello,

Thanks for your inquiry.

I did the test in our side and didn't reproduce the error. I attatched my test code as below. And you could change the provider as
Ace.OLEDB.12.0 like this,"Provider=Microsoft.ACE.OLEDB.12.0".
Code: Select all
 
Dim oleDbConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Test.mdb"
Dim oleDbConnection1 As New OleDbConnection(oleDbConn)
oleDbConnection1.Open()
Dim oleDbCommand1 As New OleDbCommand()
oleDbCommand1.CommandText = "select * from country"
oleDbCommand1.Connection = oleDbConnection1
Dim ae As New AccessExport()
ae.DatabaseName = "s3.mdb"
ae.TableName = "docinfo"
ae.SQLCommand = oleDbCommand1
ae.SaveToFile()
.Close()


If there is still this issue, please provide us your complete project and tell us your system environment.

Sincerely,
Gary
E-iceblue support team
User avatar
Gary.zhang
 
Posts: 1346
Joined: Thu Apr 04, 2013 1:30 am

Re: Ace.OLEDB.12.0 vs Jet.OLEDB.4.0

Postby aschadek » Fri Jun 07, 2013 3:39 pm

So perhaps I wasn't clear enough. The issue is not that I cannot read from a ACE.OLEDB.12.0 database because that is actually not a part of your tool at all but rather that using your tool I cannot write to one. The method "SaveToFile()" likely has connection strings embedded and that is where the Jet.OLEDB.4.0 provider is called from. This is a guess but it seems logical. Below is my complete code snippet for the area I am using your tool.

Code: Select all
    Private Sub ExportDocinfoToMDB_SPIRE(MdbPath As String)
        Try
            Dim sqlConn As New SqlConnection()
            sqlConn.ConnectionString = _AppSettings("LocalSQLProvider")
            Dim sqlCmd As SqlCommand = sqlConn.CreateCommand
            Dim command As New SqlCommand()
            sqlCmd.CommandText = "select * from docinfo"
            sqlCmd.Connection = sqlConn
            Dim accessExport1 As Spire.DataExport.Access.AccessExport = New Access.AccessExport
            accessExport1.CreateTable = True
            accessExport1.DatabaseName = MdbPath
            accessExport1.TableName = "docinfo"
            accessExport1.SQLCommand = sqlCmd
            Using sqlConn
                sqlConn.Open()
                accessExport1.SaveToFile()
                sqlConn.Close()
            End Using
        Catch ex As Exception
            Throw New Exception("An error occurred while exporting to Access:" + vbCrLf + ex.Message)
        End Try
    End Sub


As you can see I am grabbing data from a SQL server and then pushing it to a Access MDB, however, I receive the Jet.OLEDB.4.0 error for which I have no control because there is no where to set the connection string that the AccessExport sub uses.
aschadek
 
Posts: 2
Joined: Thu Jun 06, 2013 8:06 pm

Re: Ace.OLEDB.12.0 vs Jet.OLEDB.4.0

Postby Gary.zhang » Mon Jun 10, 2013 7:35 am

Hello,

Sorry for the delay replay as weekend.

Thanks for your more detail information to make us understand the issue clearly, we will do some research on the issue, once there are any progress, we will get it back to you ASAP. Sorry for inconvenience.

If there are any questions, welcome to get it back to us.

Thanks,
Gary
E-iceblue support team
User avatar
Gary.zhang
 
Posts: 1346
Joined: Thu Apr 04, 2013 1:30 am

Re: Ace.OLEDB.12.0 vs Jet.OLEDB.4.0

Postby amy.zhao » Tue Jun 11, 2013 3:52 am

Hello,

Thanks for your waiting.
I talked with our dev team about this issue. They told me that must ensure that Microsoft.Jet.OLEDB.4.0 has been installed in your PC when writing data to a Access MDB. We are sorry for that we gave you previous error replies.

Regards,
Amy
E-iceblue support team
User avatar
amy.zhao
 
Posts: 2196
Joined: Wed Jun 27, 2012 8:50 am


Return to Spire.DataExport

Who is online

Users browsing this forum: No registered users and 0 guests