Re: Informix CLI 2.0 : problem with ODBC driver using MS-ACCESS

Juergen, Scheid/ZFO34/11/DFS/DE@emory.mathcs.emory.edu wrote: > > The "invalid tablename"-error is described in documentation file of CLI - see > NOTECLI.200 : > ... > 39824 MICROSOFT ACCESS CAN'T ATTACH AN ONLINE TABLE WITH PRIMARY KEY > CONSTRAINT; ACCESS SAYS 'OWNER_TABLE IS AN INVALID NAME'. > > No workaround. Tables with primary key constraints cannot be > accessed from MS Access. > ... > > Isn't funny, is it ??? > > This error is also know by the Intersolv ODBC-driver. > I get a message from Informix that this error will be fixed in next version > (2.5) of CLI. But nobody can > tell me when this version will be released. > > Juergen Scheid, Germany > > _____ Reply Separator _____ > Author: Koen.VanderElst @ ping.be (Koen Van der Elst) @ INTERNET > Received: 25.04.96 22:12:19 EDT > Subject: Informix CLI 2.0 : problem with ODBC driver using MS-ACCESS and > MS-QUERY > > I have a weird problem using Informix CLI 2.0 in combination with > MS-ACCESS 2.0 and MS-QUERY. While trying to set up an ODBC connection > a ran into a strange problem : > > When I try to attach a table from the Informix stores 7 database I get > an MS-ACCESS error : "owner_tablename" is not a valid tablename (e.g. > "informix.customer" is an invalid tablename. When I try to access the > same table from MS-QUERY, I can perform selects but no updates, > inserts or deletes. > > The main difference between MS-ACCESS and MS-QUERY is that MS-ACCESS > uses its Jet-Engine, MS-QUERY doesn't. > > Public has insert, update, delete on all tables and views. > > Informix Belgium has advised me to put a view on every table : > "create view xxxx as select * from yyyy". This allows me to attach to > all the views except the ones on the stock table and on the stores > table. > > When I grant DBA to public I a can attach to all the views from > MS-ACCESS but not to the tables. I am unable to modify the views from > MS-ACCESS, modifying them from MS-QUERY works fine. > > Microsoft Belgium has advised me to drop all indexes on the tables > before attaching to them. This allows me to attach to the tables from > MS-ACCESS but not to modify them. > > I'm a bit puzzled here ... Can anybody shed some light on this > problem, please. > > My configurations used in testing : > > Informix 7.10 UC1 on SCO 3.2.4 > Informix 7.12 UC1 on SCO 3.2.5 (Openserver 5). > > MS-Windows 95, MS-ACCESS 2.0 > MS Windows 3.11, MS 32-bit TCP/IP, MS-ACCES 2.0 > > All combinations of Informix engines and client OS produce the same > errors. > > Please e-mail me if you know what is going wrong. > > Koen There is a way to have primary constraints and attach the table in access. The basic problem is that the underlying index for the primary key is created with a space as the first character followed by the name. The index is created for you by Informix. Access does not like the space. So the way around this problem is to create your tables without the primary key, but with a unique index, and give it a name. Create table <Stuff> Create unique index <Index Name> <More Stuff> Once this is done then alter the table and add the primary key Alter table <Table Name> add constraint primary key <stuff> constraint <Constraint Name> It is importian that you name the index and the primary key. If you do not the database will and guess what the space will be there, and access will not like it. Hope this helps.