Troubleshoot MS Access query problems

Wildcard Filters not working with MS Access Linked Tables

If you are using MS Access linked tables you may find that your queries return different results in the ipushpull DBLoader compared to Access itself. This problem occurs if you are using wildcards in your query criteria e.g.

    SELECT * FROM CUSTOMERS WHERE NAME LIKE 'Da*'

When you run this in MS Access itself it will return the data you expect. However, if you run the same query in the DBLoader (or other Windows applications like MS Excel) you will either get no data or the filter will be ignored completely and all rows will be returned.

To solve this problem you have to use the % (percent) character instead of the * (asterisk) for the wildcard in your query criteria i.e.

    SELECT * FROM CUSTOMERS WHERE NAME LIKE 'Da%'

Note that the query with the % symbol will not work in Access! So you will have to create two queries - one using asterisks for running in Access itself, and one using the percent symbol for running in the DBLoader.

This problem only occurs with linked tables, not regular Access tables.

More details

This problem occurs because when you run a query in MS Access itself it uses the Access Database Engine. When you run the same query from an external app (e.g. MS Excel or the ipushpull DB Loader) it uses the ANSI 92 standard. These two engines use different symbols for wildcards (* and %).

For more information see this post on the Microsoft website.