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

Question

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.

Have you tried the query in MS Access by pasting into sql view? Your Excel connect string does not look right to me. — Fionnuala
– Fionnuala, Commented Jan 7, 2013 at 16:52
The module is working fine for another excel document which I import to a different table. What I find odd, is that if I add the data this way, then it works. <Dim MyExcelInsertSQL As String = "INSERT INTO [REPORTCONFIG] VALUES (10,1,1,'Algemene gegevens','','S','White','Blue','Arial',14,'Bold',0,15,1000,21)> So, yes you would say that the problem then lies with the excel connection string, but as I just mentioned, it is working fine for a different Excel document. — Rico Strydom
– Rico Strydom, Commented Jan 7, 2013 at 17:35

HansUp · Accepted Answer · 2013-01-07 17:57:13Z

1

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$];

answered Jan 7, 2013 at 17:57

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rico Strydom Over a year ago

HansUp, the $ did the trick for my document with REPORTCONFIG as tab name but now the other documents with tab name that contains underscores (rpt_bank_krediet_mg_comm_cns) are not importing anymore. Has it to do with the underscores?

Rico Strydom Over a year ago

HansUp, I found the problem. Problem with variable MyTableName. Thanks again for your help.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related