Is there any special setup required for a Query in Microsoft Access to enable CardExchange to connect to it.

I am trying to connect to a Query created in Microsoft Access, but it is not shown in the View Properties dropdown menu in the data definition wizzard.
All the tables are visible but not the Query that has been created.

Queries in MS Access should automatically be seen.
Certainly this is the normal behavior I have experienced.

I have however experienced an issue with Queries linked directly to external data sources not being available.
(The ones I have had trouble with are "passthrough" queries and th Omnis database.
The omnis ODBC driver does not let CE to connect to the table, in fact MS access cannot connect directly to the tables, you need to create the passthrough query in MS access.)

I would suggest creating a very basic query from internal tables and seeing if it is available to see in CardExchange.
This may help isolate the issue to a specific query or query type.

As a work around to the Omnis problem above I have been able to create a new MS Access query to link to the passthrough query and the new query can be seen fine by CE. This does however cause some performance issues with the double query scenario. Ultimately I created a one button MS Access macro that ran a make table query on the passthrough query and had CE connect to the new table. If new records are added the customer opens MS access and clicks the update button. Not elegant, but functional as the customer only updates the records a couple of times a year.

Thanks Jason,

I created a simple query (Name and ID Number) but still not visible.
However if I create an ODBC source that points to the database and connect that way the query appears in the list.

Having thought about it, I also had this a while back when trying to connect to a view in a SQL database that we set up for a customer.

Not sure why but it seems you can connect using the ODBC source option, but not directly to the database.

Just checking was the simple query you created using an internal table ?

Just thought I would check that it wasn't a link table because I am doing MS Access queries regularly and on "plain vanilla" MS access database queries I have not had an issue.

I have attached a basic MS access database that works for me here. The Query name is qry_test.

I have tested the file as an MS Access 2007 format and then saved it as MS access 2000, tested again and attached here as a Zip.
All scenarios work fine with CardExchange 4.0 Premium on my PC.

If this one works for you perhaps you can email a sample of the non working database to me and I am happy to try and help.
BTW, which release and edition of CardExchange are you using.

Hi Craig and Jason,

By design, CardExchange should show all queries and all user tables of Access. There is a mechanism that filters out system tables, but that filter should not affect the queries. Only the pass-through queries are known to be hidden in CardExchange, which is not a design feature of CardExchange, but of the underlying OleDb software (ask Bill for more information...).

For the ones that like some technical info: for Access 2003 and older, table and view names are retrieved using the GetOleDbSchemaTable of the OleDbConnection object, after which tables with their TABLE_TYPE set to "SYSTEM TABLE" or "ACCESS TABLE" are ignored. or Access 2007, the generic GetSchema method of the DbConnection object is used, which unfortuentely leads to not-so-generic bunch of data. CardExchange shows all entries of the Views collection and the entries of eth Tables collection that have theri TABLE_TYPE set to "TABLE".

All this doesn't give me any clue about why you cannot see your queries (unless they are pass-through queries). We'll indeed need to have a look at the database in question.

Thanks for the technical insight Rutger.

It's good to understand these things.

Hopefully Craig had some success with the table I attached and we can work towards a resolution.

PS: Bill doesn't work there any more....

Well it seems that this was most likely a human interface error !!!!!

Started PC up next day and pointed CE to the database query and it saw it first time.
I can also connect to the table and query supplied by Jason.

So hopefully this was just me doing something wrong, but I will update if I do find further problems.

Thank you both for the help