[Thies Windu]

Good morning.

I am having a problem using ACE. I am on Windows 7 64 bit and I am using Visual Studio 2010. I have inherited a program that needs to be converted from using JET to ACE as more and more users could not use it on their new computers. All this little application does is sort data according to selected fields and then insert blank lines whenever the data changes. It worked fine with Jet built in VS 2005.

Line 32 is where I am getting trouble. I get one of two error when I get to this point "IErrorInfo.GetDescription failed with E_FAIL(0x80004005) when targeting any CPU. I found suggestions from people on the web to target X86 in the advanced compile options but then I get 'Microsoft.ACE.OLEDB.12.0' provider is not registered. This I think is due to the system looking for the 32 bit version of ACE but I am using the 64 bit.

When reading through the code the line da.Fill(dt) is underlined with the comment "Variable 'da' is used before it has been assigned a value. A null reference exception could result at runtime." This confuses me as I declare it at the top and it gets used in the if the statement. The only reason for the underline I can see is that there is no option in case the incoming data is not from csv,xls or xlsx but since the open file dialog doesn't allow for anything else that shouldn't matter.

I am lost and any help is appreciated.

   Private Sub btnProcessFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnProcessFile.Click

        Dim con As OleDbConnection
        Dim da As OleDbDataAdapter

        'make the connection to the excel file depending on the type of file selected they use different connection strings
        If txtFileName.Text = "" Then
            MessageBox.Show("Please select a file to process first.")
        Else
            Dim file As New FileInfo(txtFileName.Text)
            If file.Extension() = ".csv" Then
                con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file.DirectoryName & ";Extended Properties='text;HDR=Yes;FMT=Delimited(,);IMEX=1'")
                da = New OleDbDataAdapter("select * from " & file.Name, con)
            ElseIf file.Extension() = ".xls" Then
                con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file.Name & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'")
                da = New OleDbDataAdapter("select * from [Sheet1$]", con)
            ElseIf file.Extension() = ".xlsx" Then
                con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file.Name & ";Extended Properties='Excel 12.0 XML;HDR=Yes;IMEX=1'")
                da = New OleDbDataAdapter("select * from [Sheet1$]", con)
            End If

            Try
                'fill the datagrid with the data from the data table.  Should the connection to the file fail
                'the error messages will be displayed

                'should the user click the button twice the clears should remove the old data so data does not 
                'double up in the dg
                dt.Rows.Clear()
                dt.Columns.Clear()
                dt.Clear()

                da.Fill(dt) ' why does it fail here? unless data is not being retrieved?
                dgTest.DataSource = dt.DefaultView

                dgTest.ReadOnly = True
                dgTest.AllowSorting = False
                dgTest.Location = New Point(16, 56)
                dgTest.Size = New Size(272, 220)
                dgTest.PreferredColumnWidth = 100
                plMain.Controls.Add(dgTest)
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
                MessageBox.Show(ex.Message)

            End Try
        End If

    End Sub

[_HAWK_]

I don't think the single quotes(') are needed in the connection string.

[Thies Windu]

After taking out the single quote I get the following error. "Could not find installable ISAM".

From my understanding this is due to multiple extended properties. When using multiple extended properties the quotes are needed.

[Thies Windu]

I am still stuck. I have read online that people had to change properties in ACE. Could this be the problem? I am not at work right now so I can't test this theory out.

Thanks

Thies

[_HAWK_]

It has been awhile since I was playing with this, but I always had trouble with the Extended property part. Try leaving them out. Or just use 'text'.

[fixo]

Try to change on these lines, it's working on
my end

      If file.Extension() = ".csv" Then
            con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file.DirectoryName & ";Extended Properties='text;HDR=Yes;FMT=Delimited(,);IMEX=1'")
            da = New OleDbDataAdapter("select * from " & file.Name, con)
        ElseIf file.Extension() = ".xls" Then
            con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file.FullName & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'")
            Console.WriteLine(file.FullName)
            da = New OleDbDataAdapter("select * from [Sheet1$]", con)
        ElseIf file.Extension() = ".xlsx" Then
            con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file.FullName & ";Extended Properties='Excel 12.0 XML;HDR=Yes;IMEX=1'")
            da = New OleDbDataAdapter("select * from [Sheet1$]", con)
        End If

        Try
            dt = New DataTable("Sheet1")
            da.Fill(ds, "Sheet1")
            dt = ds.Tables("Sheet1")

[Thies Windu]

Thanks for the replies.

Fixo I have tried the code and noticed that you seem to be using a dataset that I am not using. So I just declared one and I am now getting a null error.

I am still thinking that somehow there is a permissions problem with ACE.

I am going to research the permissions problem in a bit more detail.

[fixo]

Thies Windu,
Hope you'll find a solution on your machine
If it would be intersting heere is a detailed
setting of my test:
Win7, VS 2010, MS Office 2007 (limited student realease)
- all legal

If you'll solve this issue, please, share your result
with community
Cheers

[Thies Windu]

I came across the following:

http://blogs.lesstha...MSSQLServer/ace

I followed the instructions there as his problems appeared to be the same as mine.

I still get the same error but now I can access the excel files through SQL Server.

Fixo, are you using 64 bit?

I have Windows 7 and Office 64 bit. Visual Studio 2010 (which I think might still be 32 bit).

This is very annoying but I am sure that in the end it will be something stupid like setting a flag or something along those lines. Jet definitely was a heck of a lot easier to use than ACE.

[fixo]

I'm still on 32 bit
Thanks for the link

This post has been edited by fixo: 25 July 2011 - 10:05 AM

[Thies Windu]

Still no solution.

Here is a screen grab of the exact error message I get.

Detailed:



Short Form:

[Thies Windu]

Uggh...didn't realize I forgot to trim the pictures. I apologize.

Small progress. I can use ACE to connect to the CSV files with the following connection string and command.

con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & file.DirectoryName & ";Extended Properties='text;HDR=Yes;FMT=Delimited(,);IMEX=1'")
da = New OleDbDataAdapter("select * from [" & file.Name & "]", con)

So I am pretty sure that ACE is working the way it should. This does not explain the errors I get for the XLS and XLSX files.

Edit: Typo fix.

This post has been edited by Thies Windu: 25 July 2011 - 01:06 PM

[Thies Windu]

Could somebody test out the connection string and select the way I am on an XLS and XLSX file just to ensure that that is not the problem?

Thanks

[CharlieMay]

Not sure if this will help you but this will open both a .xls and xlsx spreadsheet.
I'm running on Win7 64Bit but I have my compile options to X86 only.

       'Dim sFileName As String = "C:\Users\Charlie\Documents\!Cases Sold Reports\ImportFile.xls"
        Dim sFileName As String = "C:\Users\Charlie\Documents\!Cases Sold Reports\ImportFile.xlsx"
        Dim connectionStringTemplate As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                                 "Data Source={0};" & _
                                                 "Extended Properties=""Excel 12.0;" & _
                                                 "HDR=Yes;IMEX=1"""

        Dim connectionString As String = String.Format(connectionStringTemplate, sFileName)
        Dim sqlSelect As String = "SELECT * FROM [IMPORT$];"

        Dim workbook As DataSet = New DataSet()

        Dim excelAdapter As System.Data.OleDb.OleDbDataAdapter = _
           New System.Data.OleDb.OleDbDataAdapter(sqlSelect, connectionString)


        excelAdapter.Fill(workbook)
        Dim worksheet As DataTable = CType(workbook.Tables(0), DataTable)

        dgvImport.DataSource = worksheet

[Thies Windu]

Thanks Charlie. I am assuming that the first sheet in your workbook is called IMPORT?