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

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 _____

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



Wed, 18 Jun 1902 08:00:00 GMT
 Informix CLI 2.0 : problem with ODBC driver using MS-ACCESS


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

It shows an attitude that is incredible. This is a show stopper bug for many
users. If the suppliers in question here aren't aware of that it's even worse.
Luckily there is OpenLink that doesn't have this bug, and has several other
advantages.


NM Data AS, Postbox 9090, Gronland, 0133 Oslo, Norway
My opinions are those of my company



Wed, 18 Jun 1902 08:00:00 GMT
 Informix CLI 2.0 : problem with ODBC driver using MS-ACCESS

Quote:

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

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



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Informix CLI 2.0 : problem with ODBC driver using MS-ACCESS and MS-QUERY

2. Informix CLI 2.0 : problem with ODBC driver using MS-ACCESS and MS-QUERY

3. Informix CLI drivers and MS Access 97

4. connect to the MS Access database without using the ODBC driver for MS Access

5. Troubles with informix ODBC drivers when using ms-access

6. Informix CLI driver ODBC vs Native Access Help

7. Accessing MS-Access 2.0 DB using ODBC

8. ISAM or ODBC driver for Attaching Paradox 5 tables to MS Access 2.0

9. Installing MS Access 2.0 ODBC drivers

10. ODBC-driver for Delph1 1.0 and MS Access 2.0

11. MS Access 2.0 ODBC driver does not support transactions


 
Powered by phpBB® Forum Software