OLEDB using 32bit instead of 64bit

Question

I need to import some datafrom a Excel worksheet but the OleDbConnection is using the 32bit version, so it cant find the provider. I already imported using the 64bit wizzard and everything is working fine.

Already tried using the following connection string:

Provider=Microsoft.ACE.OLEDB.12.0;

  Public Shared Function ExcelToSqlServer() As Integer

        Dim ds As New DataSet
        Dim da As New OleDbDataAdapter

        Dim conn As New OleDbConnection



        Dim cnn As New SqlConnection
        Dim sqlBC As SqlBulkCopy

        Dim myFileDialog As New System.Windows.Forms.OpenFileDialog
        Dim xSheet As String = ""

        With myFileDialog
            .Filter = "Excel Files |*.xlsx"
            .Title = "Open File"
            .ShowDialog()
        End With

        If myFileDialog.FileName.ToString <> "" Then
            Dim ExcelFile As String = myFileDialog.FileName.ToString
            xSheet = "Incidentes"

            conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "data source=" & ExcelFile & "; " & "Extended Properties='Excel 12.0 Xml;HDR=Yes'")

            Try
                conn.Open()
                da = New OleDbDataAdapter("SELECT * FROM  [" & xSheet & "$]", conn)
                ds = New DataSet
                da.Fill(ds)

                sqlBC = New SqlBulkCopy(cnn)
                sqlBC.DestinationTableName = "Incidentes"
                sqlBC.WriteToServer(ds.Tables(0))
                conn.Close()
                Return 1
            Catch ex As Exception
                MsgBox("Error: " + ex.ToString, MsgBoxStyle.Information, "Informacion")
                conn.Close()
                Return -1
            End Try
        End If
        Return -1
    End Function

I get the following error when I run this function

Answer 1

Whether your app tries to use the 32-bit or 64-bit version of the ACE OLE DB provider is determined by whether your app is running in a 32-bit or 64-bit process. A 32-bit app can't use the 64-bit OLE DB provider and vice versa. Whether your app runs in a 32-bit or 64-bit process depends on the Target Platform in the project properties and the OS it runs on.

If the Target Platform is x86 then the app will only run in a 32-bit process, which means that it will not run on an OS that does not support 32-bit processes.

If the Target Platform is x64 then the app will only run in a 64-bit process, which means that it will not run on an OS that does not support 64-bit processes.

If the Target Platform is Any CPU and the Prefer 32-bit box is checked then the app will run in a 32-bit process on an OS that supports it and in a 64-bit process otherwise.

If the Target Platform is Any CPU and the Prefer 32-bit box is not checked then the app will run in a 64-bit process on an OS that supports it and in a 32-bit process otherwise.

Using ACE is slightly tricky because, if you want to support every possible scenario, you have to build your project at least two different ways. The problem is that the vast majority of people who have Office already installed will have installed the 32-bit version, which means that they will have 32-bit ACE installed. To support those users, you need to target x86 or else target Any CPU and check Prefer 32-bit. If you do that though, you won't be able to support users who have installed 64-bit Office or standalone 64-bit ACE, so you'd need a second build for them. You need to determine exactly what combinations your users might require and make sure that you have a build that will support them.

In the specific case you describe, a Target Platform of x64 or else a Target Platform of Any CPU and unchecking Prefer 32-bit should do the trick, but that will not work for the vast majority of Office users on 64-bit Windows.