I encountered a “cannot find table” error on a custom table and I was especially confused because I had just used the SQL Maintenance window to create that table in the Fabrikam database and I then manually populated some test data in it via SQL Management Studio.
I proceeded to review the table definition in Dexterity and confirmed Database Type is Default and its Series is 3rd Party. The Series got me wondering as I did not create this table originally and I do not often use the 3rd Party series. Searching online led me to this support article which describes controlling the location of Dexterity tables.
Table locations are determined by their Series but the 3rd Party Series does not have a default entry in the Pathnames table (SY02100). There are a several options if you find yourself at this decision point.
- You may check whether your ODBC connection has a default database defined.
- If a default is set, then GP looks to access your tables there.
- If a default is not set, then GP looks to the master
- You can create an entry for the 3rd Party Series in the Pathnames window.
As I used the SQL Maintenance window to create the table, it did create it in Fabrikam (the TWO database) but when the application tried to access the table it sought to communicate with it in the master database – I did not have a default defined on my ODBC connection.
I tested setting a default database on my ODBC connection; that did resolve the issue and the application communicated with my table in the Fabrikam company. However, I would rather resolve it using a defined Pathname. So I undid the setting of a default on my ODBC connection. I checked and of course the error came back so let’s proceed with the Pathnames option.
To access the Pathnames window in Dynamics GP, right-click the navigation pane and select Add > Add Window… Next, expand the Microsoft Dynamics GP and then the Company entries. Scroll down and select Pathnames, then click the Add button and finally the Close button. Select the new option in your Home navigation pane for Pathnames.
To add the desired entry, select your Product – for me this is the name of my Dexterity project I’ve installed in Dynamics which I have called RSM Test. Next, select 3rd Party in the Table Series drop-list. This final step you must ensure you type correctly. The Pathname is in the format <database>\<schema>\ and so you want the database name and ‘dbo’. For this example, it is TWO\dbo\. Click Apply to save the entry and the application will now seek to communicate with your 3rd Party tables in the correct database.
I hope this helps. If you would like more information for getting the most out of your Microsoft Dynamics GP solution, contact our experts at RMS 855-437-7202
by Chad Bruels for RSM