CODEWITHSUNDEEP
vb.netexcel-2010ms-access-2010
Rico Strydom
Rico Strydom

Reputation: 571

VB.Net: MS Access database engine could not find the object

I have a module in that imports Excel files into a MS Access database table. I get: MS Access database engine could not find the object 'REPORTCONFIG'. The Table REPORTCONFIG does exist and it is 'picked-up' by the code because just before I import the excel I first empty the table out (which works fine). I have made sure that the tab in my excel is that same name as the Table. (REPORTCONFIG)

This is my module:

Dim MyExcelFileDialogBox As New OpenFileDialog()
        If MyExcelFileDialogBox.ShowDialog = DialogResult.OK Then
            MyExcelFullFileName = MyExcelFileDialogBox.FileName
            MyExcelFile = Dir(MyExcelFileDialogBox.FileName)
            MyExcelFilePath = Path.GetDirectoryName(MyExcelFileDialogBox.FileName)
            ModuleConnection.AccessConnect()
            ModuleTables.DeleteTableContent(MyTableName)
            Dim MyExcelInsertSQL As String = "INSERT INTO [" & MyTableName & "] SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & MyExcelFullFileName & "].[" & MyTableName & "];"
            Dim MyCommand As OleDbCommand = New OleDbCommand(MyExcelInsertSQL, MyAccessConnection)
            Try
                MyCommand.ExecuteNonQuery()
                MyCommand.Dispose()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            MyAccessConnection.Close()
        End If

Any help is appreciated.

Upvotes: 1

Views: 1702

Answers (1)

HansUp
HansUp

Reputation: 97131

When using a worksheet as the data source for a query, add a dollar sign ($) after the sheet name so it will be found.

With this simple SELECT query, the db engine complains it can't find REPORTCONFIG:

SELECT *
FROM [Excel 12.0;DATABASE=C:\share\Access\MyWorkBook.xlsx;HDR=YES].[REPORTCONFIG];

However, when using REPORTCONFIG$ in the FROM clause, the following query returns the data without error.

SELECT *
FROM [Excel 12.0;DATABASE=C:\share\Access\MyWorkBook.xlsx;HDR=YES].[REPORTCONFIG$];

Upvotes: 1

Related Questions