I am trying to connect to the dbo.Customer view via ODBC and Access and it is not giving all the fields available from the view. If I try to connect to the regular table and not the view I get the to many indexes error.
I can do a pass-through query to manually query the view and select just the columns I need and it works fine. I was also able to open the view from MS Power BI Desktop and it showed me all the fields.
It seems like there is something with Access that is preventing me from seeing all the columns in the view. The to many indexes error I understand is caused because the customer table actually has a lot of views and this was a previous issue from progress.
So for now we only see two options.
Rework all of our Access queries to use the Pass-Through Queries.
Create new views for and tables / UD tables that are having this issue. I assume I would want to create a separate DB for these so they are left untouched by anything Epicor does with the DB during upgrades or data model regeneration.
Its an MS Access limit on the number of columns it can display. We had to create a special database view with the required columns on to get round this limitation.
That is exactly what I ended up doing. I created a separate schema and created new views under that schema in order to keep it separate from the Epicor stuff.
I’ll add another one to that, whilst we don’t use access we have a separate database which just is full of views that access our epicor database. Along the lines of
CREATE VIEW Project
AS
SELECT * FROM Epicor905.dbo.Project WITH (NOLOCK)
A good example is if we want to build a SSRS report which is run from outside Epicor or a Crystal one for that matter the datasets look at our EpicorViews database and not the production database. It also can help with security. We also refernece this database when we want to create and external BAQ. Get SQL to do the work and just present a nice flat dataset to the designer (E9 for us though).
Another thing we do is run a snapshot replication on a nightly basis for reporting that is ok to be a day behind. We have a series of reports that run against that data.
I would not be keen on having a separate schema in the Epicor database though. Having it is a totally separate database ensures there are never any support quibbles when they request a copy of your database and they see you have a million and one items in the database that are not Epicor.
This hasn’t been an issue in the past with added custom views in our progress db. I don’t see us doing much more than views at this point and creating an entire database to hold 5 views seems a bit overkill. If we were doing more that a couple views used for ODBC it may be worth it.
I’d agree on that. Not really worth the effort for 5.
There there are some caveats to the separate approach, for example when you go to perform a live to test or train for example, you need a copy of the database, and adjust any external BAQ’s if you don’t have the ODBC connection similarly named in your test environment, but it certainly keeps everything nice and separate, which is good for upgrading.