Knowledgebase
Home » Item #934
Data tool error 'cannot find the input table or query' or 'invalid object name' when upsizing to v6
Question
I'm trying to use the v6 data tool software to load my products into the v6 database, either from the v5 database or from an Excel file. However, I keep getting this this error:
Testing source connection... done.
Detecting version...Set to Excel Spreadsheet
Testing destination connection... done.
Detecting version...
Error occurred!
The Microsoft Jet database engine cannot find the input table or query 'tblCactuShopProducts'. Make sure it exists and that its name is spelled correctly.
SELECT TOP 1 * FROM tblCactuShopProducts
...or I get this:
Invalid object name 'tblCactuShopProducts'
The table tblCactuShopProducts does not exist in the database - in v6 it looks like the table is called tblCactuShop1Products.
Is the data tool broken? What am I doing wrong?
Answer
When selecting data sources in the data tool, there are some extra boxes including one labelled 'table prefix'. When selecting a v6 database (i.e. the 'destination' database screen), you should enter '1' in this box. For all other data sources (Excel spreadsheet, older CactuShop databases) you should leave the box blank.
The table names in v6 were changed like this so that you can run two (or more) sets of CactuShop tables on the same single SQL database instance. For example, you can set up all the v6 tables on your existing SQL database without overwriting the v5 tables - very useful for upgrading. If you needed to run a second store from the same database instance, you can easily run a find/replace on the setup .sql script to replace 'tblCactuShop1' with 'tblCactuShop2'.
This new feature makes the CactuShop database more flexible, but obviously care must be taken to specify correct settings when upgrading.