Hi

still struggling to make the most out of Teiid!

i have a "Testmix" dynamic VDB, like

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="Testmix" version="1">
    <description>Test Mix VDB</description>
    <property name="UseConnectorMetadata" value="true" />
     <model name="TestmixMS">
        <property name="importer.useFullSchemaName" value="true"/>
        <source name="sqlserver-connector" translator-name="sqlserver" connection-jndi-name="java:/magoDS"/>
    </model>
     <model name="TestmixMY">
        <property name="importer.useFullSchemaName" value="false"/>
        <source name="mysql5-connector" translator-name="mysql5" connection-jndi-name="java:/testmysql"/>
    </model>
</vdb>

which is working wonderfully from

> Squirrel SQL with teiid client jdbc driver

> Squirrel SQL with jdbc/odbc(postgresql) bridge

> http://www.etl-tools.com/database-editors/database-browser/overview.html with ODBC (postgresql)

so i think the Teiid odbc interface is fine.

but i have found that MSaccess (tried both 97 and 2010 versions) has a problem linking or importing tables from my VDB ID they have an underscore "_" in their name... and 99.9% of my tables (in both models HAVE underscores i ntheir names, which is perfectly legal, i think.). I cannot try MSExcel (same versions) because it does not gives me the option to change the default port from 5432 to 35432, unfortunately

for MSAccess part i also tried using VBA command docmd.Transferdatabase command, which gives complete control over the iport/link feature but i have the same result, always:

i know this is not MSAccess support forum, but i wonder if anyone else has come into this issue and did find a workaround

if i try to link the  'TestmixMY.conti_contratti' table from the VDB, Access complains that (google translated from italian) "The Microsoft Access database engine can not find the object 'TestmixMY.conti_contratti'. Make sure the object exists and that its name and location entered are correct. If 'TestmixMY.conti_contratti' is not a local object, check your network connection or contact the server administrator."

if i try to link the  'TestmixMY.conticontratti' table from the VDB, Access wil not complain and result in an imported "TestmixMY_conticontratti" (ironically, note that it substiutes the "dot" with an "underscore" <grin>)

the same goes if i link a table from my MS SQL model/source in the VDB, which is like "TestmixMS.ApiMn.dbo.MA_ChartOfAccounts" (this DB is from a commercial app, and has underscores in all its over 400 tables, plus i have to set  <property name="importer.useFullSchemaName" value="true"/> in the VBD so it prefixes with DBname and schema the original table name) which fails and i get:

"The Microsoft Access database engine can not find the object 'TestmixMS.ApiMn.dbo.MA_ChartOfAccounts'. Make sure the object exists and that its name and location entered are correct. If 'TestmixMS.ApiMn.dbo.MA_ChartOfAccounts' is not a local object, check your network connection or contact the server administrator."

if i try to link the "TestmixMS.ApiMn.sys.columns" table it works wonderfully and it links the table as "TestmixMS_ApiMn_sys_columns"

i tried both with ANSI and UNICODE postgres ODBC drivers version 8.04 and the behaviour is identical...

i thought i could

- rename all tables in the source (of course not possible)

- create an updatable view in teiid for all tables with underscored-names having corresponding non-underscored names. ithink now this is possible even with dynamic VDBs, or at least with Teiid Designer but frankly i feel it is a pain...

is there any "substitution" machanism in the teiid engine, of which i am not aware, that could help in this situation, or any other trick/workaround i could use?

Thanks, community...

Marco