I remember from long time ago, Access not liking the "." in any path of the table name, check that could be an issue. I also thought there is an option to import tables in Access right? did you try that?
Thank you for replying.
Those periods you see are coming from Teiid, I did not supply them. I was expecting to only see the table name (CANDYBAR_DATA) and not the Teiid data source followed by a period followed by the table name. I thought there might be an option in Teiid/Teiid Designer to prevent the data source from appearing, but I could not find one. I also thought there might be an option in the Postgres ODBC driver, but I could not find one there either.
Yes, I did try to import the tables as well as linking, but neither worked and both returned the same error message.
Any other thoughts?
> I thought there might be an option in Teiid/Teiid Designer to prevent the data source from appearing, but I could not find one
If you are using the Teiid import logic, then you need to set importer.useFullSchemaName to false. The runtime name for the imported tables will just be their source table name.
Designer should have a similar option to not use the source catalog/schema name in the Teiid table name.
Ramesh's clue as well as information found in this JBoss Developer Forum
Re: teiid 8.2 odbc interface linked tables fail from MSAccess 97-2010 helped me figure out the problem.
Microsoft Access does not like underscores in table names when pulling from an external data source via the Postgres ODBC driver. Funny enough, it doesn't seem to mind underscores in the names of Teiid data sources. For example, given SRC_TEIID_ORA_ORCL.CANDYBAR_FACT, Microsoft Access will barf because of the underscore in CANDYBAR_FACT, but not SRC_TEIID_ORA_ORCL. Once I renamed CANDYBAR_FACT to CANDYBARFACT, Access had no problem importing/linking the data.
And, yes, Access will take the period in SRC_TEIID_ORA_ORCL.CANDYBARFACT and change it to an underscore, so Access will present you with SRC_TEIID_ORA_ORCL_CANDYBARFACT.
Note that I have been helping a friend at work with Teiid (as well as various other pieces of software) and -- although I'm a complete newb when it comes to Teiid -- I have written up an installation document for my friend. This document contains installation instructions for Teiid 8.11.4, Teiid Designer 9.1.1, Eclipse 4.5 ("Mars"), JBoss Developer Studio 8.1.0 GA with EAP 6.4 (AS 7.5) , Apache Spark 1.5.1, Apache Maven 3.3.3, Scala 2.10.6 (as well as the Eclipse plug-in with the latest release of 2.11.7), the Oracle and SQL Server JDBC drivers, etc. I also include a Teiid/Teiid Designer example connecting to a flat file, an Oracle database, and a SQL Server database. Finally, I show how to rename a table in Teiid Designer (without changing the name of the incoming source table!) and then access the virtual database from with Microsoft Access. Since I am a newb to most of this software, please consider these instructions "alpha":
Thanks again for your help!
Apologies, for some reason I remembered as ".", it was underscore after all. Glad that you figured it out.
Wow, is only word I could find right now for the detailed documentation right now. It is so detailed with every step, I know Teiid documentation is no closer to that at all. I have never seen such detailed installation doc for Teiid before. Kudos!!! obviously you look like master at many things. Please do let us know how we can further help with your project. Also if you have any suggestions/comments for Teiid to enhance/innovate further, as we are always looking for contributions from community.
Can we share this document on our website as is?
Thank you for your very kind words!!! As the saying goes, I'm a jack of all trades, master of none.
Yes, please feel free to share this document.