Passthrough Oracle irritation ...

[AlanSearle]

Hi Everyone,

I have a number of ODBC passthrough queries which collect data from an Oracle database. This all works fine.

I also have some VBA code which can generate some of these queries 'on the fly' (e.g. passing filter parameters). This also works fine when I avoid using table synonyms.

However, when I programmatically generate a query and try to use a table synonym such as SELECT * FROM T_LOG LOG MSAccess adds the word "AS" and produces the following: SELECT ' FROM T_LOG AS LOG ... which falls over because Oracle does recognise the "AS". If I then manually remove the "AS", the query works fine.

This is a stupid problem which is driving me round the bend so my question is how to stop MS-Access poking its nose in and adding those "AS" entries?

I really hope somone can help me.

Regards and thanks,
Alan Searle
Cologne, Germany

PS: At first this was a minor irritation but it is turning into quite a fundamental problem because there are some (nested) queries which will not run without the use of synonym names for the tables. So this is starting to hold up my whole development. Many thanks for any tips that you can give.

 

[AlanSearle]

I managed to fix this problem by doing the following ...

I created the query a dummy select and then after the connection string had been set (and MS-Access knew it was a passthrough), only then did I set the SQL.

Thus ...

Set qdfNew = CurrentDb.CreateQueryDef(qFullName, "SELECT SYSDATE FROM DUAL")
With qdfNew
.Connect = "ODBC;DSN='mydatabase'; etc; etc"
.SQL = strSQL
End With
CurrentDb.QueryDefs.Refresh

Now it works fine.

Regards,
Alan Searle
Cologne, Germany