I am using MS Access 2007 and have an issue with writing queries
against ODBC connected tables. After providing the appropriate ODBC
connection string in the properties page, the Show Tables dialog will
list the tables in the source database. When selecting a table to add
to the design window the requested table will appear on the page but
then disappear in the blink of an eye.
I am unable to get the ODBC tables to "stick" to the page in design
view. Under SQL view an appropriate SQL statement can be written and
executed but I won't be able to switch to design view - just SQL and
datasheet. The query is not a passthrough, just a select query. This
works just fine under Access 2003.
While I can write the query in SQL view it can be very cumbersome
since the ODBC references have to be written as well. It is much more
difficult with multiple joins and providing criteria.
My current work around is to create a link table to the ODBC source
and then add the link table to the query. This, however, does not
provide the same level of control that the query does - making the
data retrieval a snapshot for example. The database is in an open
user environment so I'd like to avoid the open link tables if I can.
Thank you for any help that you can provide,
David