none
Access Database Query RRS feed

  • Question

  • I am running a query on an access database, but am getting some strange results. The problem might be access and not Visual studio though.

    Here is the core of the problem:
    If I run the following query, I get exactly what I want

    SELECT ID, Question, Solved, Date_Created, Date_Last_Edit, Date_Closed 
    FROM( 
    SELECT F.ID AS Ident, Count(F.ID) AS Num 
    FROM(
    SELECT Keywords, ID, Question, Solved, Date_Created, Date_Last_Edit, Date_Closed 
    FROM(
    SELECT ID, Solved, Date_Created, Date_Closed, Date_Last_Edit, Question, Model, Keywords.Value AS Y FROM Questions) AS X INNER JOIN Keywords ON X.Y =Keywords.ID  
    Where Keywords = "Back" ) As F 
    Group By F.ID 
    HAVING (((Count(F.ID)) =1))) 
    As T Inner Join Questions 
    On T.Ident = Questions.ID
    

    But, if I change

    Where Keywords = "Back" 

    to

    Where Keywords Like "*Ba*"

    I get nothing from my query. No errors, just no data.

    The strange thing is, if I output my query to a message box, and copy and paste it into access and run the query, it runs just fine. I've double checked all the syntax and I don't get a syntax error back from the code anyway.

    • Moved by Tina-Shi Friday, March 13, 2015 9:08 AM the issue is related to the ado.net
    Wednesday, March 11, 2015 10:48 PM

Answers

  • Hello Mynamewasused,

    Please have a try to replace “*” with “%“ as:

    myCommand.CommandText = "select * from [Order] where Name like \"%Ba%\"";

    Queries performed from within the Microsoft Access application itself normally use * and ? as wildcard characters for the LIKE operator. OleDb connections to an Access database from an external application should use the % and _ wildcard characters instead. (The latter are actually the more commonly-used wildcard characters in other SQL dialects.), referred from this link below:

    http://stackoverflow.com/a/17000335

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Mynamewasused Tuesday, March 17, 2015 9:13 PM
    Friday, March 13, 2015 10:30 AM
    Moderator

All replies

  • Hi Mynamewasused,

    Thank you for posting in MSDN forum.

    Since this forum is to discuss: Visual Studio WPF/SL Designer, Visual Studio Guidance Automation Toolkit, Developer Documentation and Help System, and Visual Studio Editor.

    To help you find the correct forum support this issue, could you please tell me if you running a query on an access database with ADO.NET?

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 12, 2015 6:38 AM
  • Yes, the specific code snippets that are accessing the database:
    Dim AccessConn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""G:\Question Database\QDB_MAIN.accdb""")

    Dim availsql As String = "My query here, seen in first post"
    
    . . .
    
    Dim ds As New DataSet
    Dim AccessCommand As New OleDbCommand(availsql, AccessConn)
    Dim da As New OleDbDataAdapter(AccessCommand)
    da.Fill(ds)


    Thursday, March 12, 2015 9:35 PM
  • Hi Mynamewasused,

    Since this case is related to the ADO.NET, so we will move this case to the ADO.NET forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataset , you will get better support.

    Best Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, March 13, 2015 9:06 AM
  • Hello Mynamewasused,

    Please have a try to replace “*” with “%“ as:

    myCommand.CommandText = "select * from [Order] where Name like \"%Ba%\"";

    Queries performed from within the Microsoft Access application itself normally use * and ? as wildcard characters for the LIKE operator. OleDb connections to an Access database from an external application should use the % and _ wildcard characters instead. (The latter are actually the more commonly-used wildcard characters in other SQL dialects.), referred from this link below:

    http://stackoverflow.com/a/17000335

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Mynamewasused Tuesday, March 17, 2015 9:13 PM
    Friday, March 13, 2015 10:30 AM
    Moderator