odbc problem?

[joeythelips]

Hi,

i have an access database with several linked tables.

i am using a microsoft for oracle odbc link.

When i try to run an update query in access of a linked table i get the message 'Operation must use an updateable query'.
As i have oracle installed on my own pc, i also created an Oracle ODBC driver link to the same tables. When i ran the update query using this link, the updates worked fine.

My problem is that i have over 15 users of the access database and i do not want to install oracle on their pc's.
Therefore i cannot setup the Oracle ODBC driver link to their pc.

Does anyone have any ideas?

Thanks,

Joe

 

[cheerio]

Is it possible to craft your update query as an Access pass-through query?

This will use ODBC but will pass the query through to Oracle without validation by Access. The query would have to use ORACLE SQL syntax rather than Access SQL syntax.

Alternatively, the ORACLE ODBC driver should simply be a single DLL file. You should be able to install that on end user machines without the rest of ORACLE.

Ken

 

[joeythelips]

Hi Cheerio,

not sure what you mean by a pass through query?

Joe

 

[n2ckb]

When linking tables if Access cannot work out what the key field(s) is of a particular table it prompts for you to choose a key field. If a key field is not chosen the opportunity to add new records is removed. One thing to check is that you can add records (in datasheet view) to the table you are trying to update. If you cannot this will be why you cannot insert new records.

Might be worth a look.

N

 

[cheerio]

Further to the previous message, break your links to the Oracle database and recreate them. If Access asks you for the key field you must provide that information. Otherwise it does not have sufficient information to unambiguously tell Oracle which row of the table you are trying to update. If this is the cause of the problem then it is the easiest way to resolve it.

Pass through queries are created in Access in Query Design view by going to the Query, SQL Specific menu. The advantage is that they are executed at the database server by Oracle which is much faster. However, you cannot link forms directly to them - the form would have to be unbound which is technically more demanding. If you are going to work with Oracle you should certainly investigate, but they may not be the best answer to your current problem.

Ken

 

[JoeatStavka]

Cheerio, can you recommend a good and concise source for Oracle SQL and/or ANSI SQL. My "SQL background" is limited to viewing and tweaking Access97 SQL. My company uses Oracle and another RDBMS owned by Oracle with a slightly different syntax. Access does not translate well when trying to write pass through queries to the "non-Oracle, Oracle".

Thanks

DJ

 

[cheerio]

Sorry, I have been away a few days.

I usually connect to MSSQL databases so cannot advise on the ORACLE dialect or ORACLE books. However, my impression is that ORACLE will cope with most ANSI SQL.

Regretably Access SQL is non-standard and at best creates a first draft. This is especially true of UPDATE and DELETE queries. Also generally on join conditions and where clauses Access adds excessive brackets which make the code difficult to follow.