6 Replies Latest reply on Oct 26, 2015 11:22 AM by ilhaydn

    Problem Linking Tables in Microsoft Access to Teiid Virtual Database

    ilhaydn

      Gang:

       

      I have successfully deployed a virtual database and have executed it without having a problem.  Queries run against the tables perform beautifully!

       

      The next thing I want to do is pull in the tables from the VDB into Microsoft Access (my version is 32-bit).

       

      I installed the Postgres ODBC driver (the 32-bit version) and created a DSN with a successful connection test. 

       

      I started Microsoft Access, created a blank database, clicked on External Sources and found my DSN. So far, so good!  Access then shows me my tables in the VDB!  Excellent...or so I thought!  When I select the tables, Microsoft Access displays a popup message stating the following:

       

      The Microsoft Access database engine could not find the object 'SRC_TEIID_ORA_CANDYBARDATA.CANDYBAR_DATA'.  Make sure the object exists and that you spell its name and the path name correctly.  If 'SRC_TEIID_ORA_CANDYBARDATA.CANDYBAR_DATA' is not a local object, check your network connection or contact the server administrator.

       

      I have gone into the Teiid Instance tab for the jboss-eap server and have checked that both the administrator and JDBC connection links return "OK". 

       

      I'm confused that Access cannot find the "data" when it clearly shows me the "tables" in the selection dialog box.

       

      Any help would be greatly appreciated!  I'm tearing what hair I have left out!

       

      Thanks!
      Scott

        • 1. Re: Problem Linking Tables in Microsoft Access to Teiid Virtual Database
          rareddy

          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?

          • 2. Re: Problem Linking Tables in Microsoft Access to Teiid Virtual Database
            ilhaydn

            Ramesh:

             

            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?

             

            Thanks,

            Scott

            • 3. Re: Problem Linking Tables in Microsoft Access to Teiid Virtual Database
              shawkins

              > 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.

              • 4. Re: Problem Linking Tables in Microsoft Access to Teiid Virtual Database
                ilhaydn

                Ramesh/Steven:

                 

                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":

                 

                http://www.sheepsqueezers.com/media/TeiidAndRelatedComponentsInstallationInstructions.pdf

                 

                Thanks again for your help!

                Scott

                • 5. Re: Problem Linking Tables in Microsoft Access to Teiid Virtual Database
                  rareddy

                  Scott,

                   

                  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.


                  Ramesh ..

                  • 6. Re: Problem Linking Tables in Microsoft Access to Teiid Virtual Database
                    ilhaydn

                    Ramesh,

                     

                    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.

                     

                    Thanks again,

                    Scott