Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    8

    Unanswered: Query forces database exit...

    I have an Access 2007 database linked to a MySQL database via an ODBC connection. When I try to run this query...

    SELECT [ref_no] AS [refnum] FROM incidents WHERE c_id = 2118864 AND org_id = 1292881;

    the database closes (displaying the error dialog box) instead of returning the results of the query. I've tried every variation I can think of for this query (which works just fine using MySQL Query Browser), but nothing has worked. What it's supposed to return is the value 330-085. Anyone have any idea as to why this query crashes the database when every other query through the same ODBC link works just fine?

    Since the result is an expression (at least I'm assuming it is, because of the minus sign in the middle), I've been using the AS clause and square brackets, but the same thing happens whether I use them or not. I seem to have the same issue when trying to retrieve phone numbers, too.

    Kevin

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just guessing you are using pass through queries rather than JET queries on linked tables

    the [ qualifier is fairly unique to JET, in this case I don't think you need them.
    SELECT ref_no AS refnum FROM incidents WHERE c_id = 2118864 AND org_id = 1292881;Im also assuming that c_id and org_id are numeric not text, and with the suffix ID they ought to be numbers in my books
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2009
    Posts
    8
    My original query didn't use the square brackets. When that crashed the database, that's when I started searching for alternative syntax. You're correct, the two ID fields are numeric, which is why I didn't include quotes around them. The ref_no field is text.

    In my QTP application, I'm setting up the query in a variable and running it using this statement...

    Set dbExecuteQuery = objDBConnection.Execute(strSQLQuery)

    I also get the same result if I open the Access database itself (i.e., the one that's linked to the MySQL database) and execute the query directly.

    Any other suggestions will be greatly appreciated.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK
    so try to diagnose where the error lies
    what happens if you run the query inside MySQL Query Browser. I'd fine tune your queries there to prove you can extract data. having got confidence you are asking the right question of the db (ie right syntax) then you can move on to find out why it isn't working inside Access

    have any queries run correctly within that syntax. Im just wondering if the connection is a valid MySQL connection. if there is something wrong with the way its defined. it could be you have an incompatible MySQL client library for the version of server you are using.

    it may be worthwhile checking that the query works without the where clause.. to see if its there.

    it may be worthwhile to debug the connection object and see what properties are exposed, and take it step by step to make certain that the process to execute the SQL is correct. it may be that the error is upstream of this, but only comes to light when you try to execute the SQL. so check the DB, userids' and password are correctly specified. check that Access knows it has a valid MySQL object at easch stage.
    do you have any error trapping turned on
    do you examine the MySQL error code of any SQL.. Im not even certain you can within Access

    have you looked in the MySQL logfile to see if there is additional information there?

    the fact its dieing suggests there is a fundamental error somewhere, my guess is its going to be in how the connection to the MySQL DB is defined and opened. theres something the server doesn't like and its terminating the connection

    if its only this row thats causing the crapout it could be that there is corrupt data that either MySQL or Access doesn't like

    bear in mind the datatypes used in Access may not be compatable in MySQl and vice versa
    ms access mysql datatype - Google Search
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •