none
Access 2007 query through ACE returns an empty result set RRS feed

  • Question

  • Hi all

    I'm supporting an application from which the users can access various access databases and retrieve their content (tables, queries...)
    I'm using the 2007 Office system driver (ace)  (http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en).

    Everything was working OK until one of the users discovered recently he could not open one of the queries he wrote in a database.

    No exception is thrown but OleDbDataReader.Read always returns false (OleDbDataReader.HasRows is false).
    The thing is that the records CAN be retrieved using Access 2007. The query definitely returns records when using access.

    My code is very basic

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
    	using (OleDbCommand command = new OleDbCommand("SELECT * FROM Query", connection))
    	{
    		OleDbDataReader reader = command.ExecuteReader();
    
    		reader.Read() --> returns false
    My connection string is: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=...accdb

    Any help appreciated
    Wednesday, July 1, 2009 12:20 PM

Answers

  • Yes, technically there is no such thing as a "stored procedure" in Microsoft Access. The query is parsed and executed according to the database access method used.

    Could you post the exact SQL statement you're trying to execute?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, July 1, 2009 2:18 PM
  • I actually isolated the issue:

    Create an access table (Names) with just a Name text column.
    Add the following records: 'aa', 'a' and 'b'

    Create a query in access defined as SELECT * FROM Names where Name like "a*"
    --> Works using Access but not from ADO.net

    Change the query to: SELECT * FROM Names where Name like "a%"
    --> Works from ADO.net but not using Access

    Change the query to: SELECT * FROM Names where Name alike "a%"
    --> Works from ADO.net and using Access

    I guess I have no other choice than ask the user to change his query accordingly

    Many thanks for your help Paul

    Wednesday, July 1, 2009 2:36 PM

All replies

  • Does the query function properly when run from Access? Also, is there a reason why the query is being executed as a sub query in a SELECT statement? Why not run it directly as a "stored procedure" using a Command object?

    If you could post the contents of the query that may help as well.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, July 1, 2009 12:39 PM
  • Actually the user can select which columns he wants to retrieve
    So I give him the possibility to define the columns from a list and then only select the columns from the query

    The query is an inner join between 3 tables like the following:

    SELECT t1.ID, t2.Title, t1.Name
    FROM (t1 INNER JOIN t3 ON t1.ID = t3.ID) INNER JOIN t2 ON t1.ID = t2.ID
    Wednesday, July 1, 2009 1:15 PM
  • I think I found it, the query uses like filters (f1 like 'john*'), will it work if I use % instead?

    There's still one thing I don't understand, this query should be executed by the access engine right? So it shouldn't be a problem as the engine supports the * wildcard. Or am I missing something??
    Wednesday, July 1, 2009 1:27 PM
  • Microsoft Access and DAO uses a different variation of SQL than ADO/OLEDB/ADO.NET. If you run the query from within Access with LIKE, the wildcard character is an asterisk. In ADO/OLEDB it's a percent sign.

    Both variations of SQL will support the percent sign when used with the undocumented keyword ALIKE.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, July 1, 2009 1:42 PM
  • Thanks for the quick reply

    Does this mean that when an access database contains a query, the query's SQL text is actually parsed by ADO.net and not by the Access engine???
    Would it be different if I used a stored procedure command type?
    --> no, just tried it, still no records returned

    same thing when using alike and %
    Wednesday, July 1, 2009 1:46 PM
  • Yes, technically there is no such thing as a "stored procedure" in Microsoft Access. The query is parsed and executed according to the database access method used.

    Could you post the exact SQL statement you're trying to execute?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, July 1, 2009 2:18 PM
  • I actually isolated the issue:

    Create an access table (Names) with just a Name text column.
    Add the following records: 'aa', 'a' and 'b'

    Create a query in access defined as SELECT * FROM Names where Name like "a*"
    --> Works using Access but not from ADO.net

    Change the query to: SELECT * FROM Names where Name like "a%"
    --> Works from ADO.net but not using Access

    Change the query to: SELECT * FROM Names where Name alike "a%"
    --> Works from ADO.net and using Access

    I guess I have no other choice than ask the user to change his query accordingly

    Many thanks for your help Paul

    Wednesday, July 1, 2009 2:36 PM