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

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

Post by Irwin Goldstei » Sun, 31 Dec 1899 09:00:00




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

> <snip!>

ODBC is a strange animal and it gets stranger yet under MS-Access.
I can shed some light on some of what you described, and I can heartily
recommend OpenLink ODBC drivers for use with MS-Access 2.0 and Informix 5.0
or later.

Re: Dropping all the indexes.  This will causes MS-Access to attach to the table
as a "snapshot" which is not updateable.  In order to get Access to attach to the
table as a "dyanset" (updateable) you need to have at least one unique index on the
table.  (Therefore views, which "seem" to have no indexes -- though the underlying
table(s) might -- always get attached as snapshots.)  Access will use the first
unique index it finds as a "bookmark" for accessing/updating records.  You can
fool Access in to thinking there is a unique index by first attachin the table
and then executing a "data definition" query which creates a unique index on the
attached table (or view).  This will not actually create the index on the server,
but will make Access think there is one there.  This information (and a lot more
than you will probably ever want to know) about the "Jet" engine can be found in
a White Paper which I know is available on the MS forum on CompuServe and probably
somewhere on the MS internet site as well.

MS Access is pretty picky about ODBC drivers.  I've found that ODBC drivers that work
fine with other apps can't handle Access. I have had good results with OpenLink, however.
I believe you can find them on the net at http://www.openlink.uk.co.

HTH

Irwin

 
 
 

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

2. 2nd CFP: ASIAN98

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

4. Unspecified error connection to SQL Server after about 3 hours..?

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

6. Help usinf .PrintForm

7. Informix CLI drivers and MS Access 97

8. Access2000 and VFP data

9. Need help with MS-ACCESS 2.0 queries using ODBC

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

11. Accessing MS-Access 2.0 DB using ODBC

12. Installing MS Access 2.0 ODBC drivers

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