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