Results 1 to 7 of 7

Thread: access and sql

  1. #1
    Join Date
    Feb 2004
    Location
    Bucharest
    Posts
    37

    Post Unanswered: access and sql

    hello

    i have managed to pull out a long query that can generate a report in mysql out of 3 tables that are linked toghether.
    in my sql statement i am using CASE to determine which value i choose for a certain field.

    i know that if i execute that query in ACCESS to generate my report (a list with products based on categories, subcategories and brands), the CASE expression will not be supported by ACCESS.

    if i change that expression to IIF (which is easy), then it becomes compatible with ACCESS but is no longer supported by MySQL. IMPORTANT: i must mention that i am using external linked tables (from MySQL) to retrieve the date in the report.

    What alternatives do i have? Can i make that case/if in the report (and not in the query statement)? I need some small examples (i heard VBA code can do this..).

    Serban

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    one possibility:

    you can write anything you like in a passthru query and A will not try to (mis-) understand it. if CASE is supported on the backend then A can pass it thru.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Location
    Bucharest
    Posts
    37

    re

    ok, you say that if CASE is supported by the backend (MySQL), then it will work in ACCESS too?
    that would be great. i am going to try the query.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    take care: that's a PASSTHRU query.

    make a new query,
    CLOSE the dialog asking which tables/queries - do not select anything
    switch to SQL view
    menu: Query/SQL Specific/PassThrough
    ....type (paste) your non-A SQL, anything at all that your backend understands
    properties: set your connection string
    save
    done!

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Location
    Bucharest
    Posts
    37

    Talking re

    many thanks, it works great, if i have problems i'll get back to you.

    Serban

  6. #6
    Join Date
    Feb 2004
    Location
    Bucharest
    Posts
    37

    Post problem

    when i am trying to modify something in the report, or when i delete a field (or any operation with a field) the 'Select Data Source' keeps comming back, even if i've added a .dns file and a data source via MySQL ODBC driver, and saved the settings.

    after some clicks selecting the source (many times, repetead), it stops and show the report.

    how can i make it not asking me over and over the source?

    thanks!

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    glad it worked for you

    next step - delete all those linked tables and work unbound/passthru: why waste one of the fastest SQL-serving machines in the universe by doing all the smart stuff on the clients?

    izy

    LATER - i see it didn't work 100% for you.
    sorry - no great ideas. i guess there is a lot of internal communication going on between the report and the server when you modify the report but i have no details, but why it should keep asking you the same question i don't know.
    Last edited by izyrider; 02-09-05 at 14:39.
    currently using SS 2008R2

Posting Permissions

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