Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Long Island, New York, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    querydef problem (Access 2000)

    Very strange problem, don't even know if I can describe it properly.

    I am printing letters (mail merge) through code in access. I have all this code working fine.

    For this particular set of letters I had to get a little creative, and I do the following thngs:
    Build Query string,
    use querydef to create a persistent query with a where clause in there
    Pass query to printing function
    printing function uses new query to produce letters
    delete query
    start over

    For some unknown reason, even though I am deleting the query, when it recreates the query , it ends up with the same criteria as the originally created query, as though I never deleted it. the query looks good till it gets into the printing function where it takes on the old queries criteria So if I am printing like 100 diff letters I will instead get 100 copies of the first letter.

    It almost seems that because the querydef was just created it is not becoming the one that is persistent in the DB? Is this possible? Is there anyway to force it to lock in so its visible from everywhere?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: querydef problem (Access 2000)

    It might be a timing problem, but although you outline your procedure, we don't really have enough information to know what is going on. Do you mean mail merge to a letter in Word, or to a report in Access?

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Location
    Long Island, New York, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: querydef problem (Access 2000)

    I was trying to be as brief as possible, and knew that wouldn't work <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    Ok, I have a separate function that works as its supposed to up till now. I pass it a word doc path , an access query name and an array of filters. It applies the filter and merges the query with the word doc located at the provided path and prints out the docs, keeping word invisible. Its quite cool I might add.

    In another section of the program I have a weird scenario where I need to print many different word docs in an odd order. Because of this, I have to pass the print function a query that contains only 1 record. So I am calling the print function 100 times (example) . Prior to calling it each time I create a new query using DB.createquerydef & a SQL String & some criteria. The query ends up with only 1 record as required. Then I do the print. Then I come back to the main function where I delete the query I just created using docmd.deleteobject. Then loop goes back to the top where it creates another query with the same name but with different criteria for the next letter. When I pause the code the query is created with the correct criteria (good so far) Once I pass the query name to the print function one of the things it does is check the query for any where clauses, so I do this

    Dim tmpqry as querydef
    tmpqry = DB.QueryDefs(QueryName)
    str = tmpqry.sql

    At this time, if I check the value of str, I get a sql string that matches the original string value of the first query which has been deleted! I have checked and seen the query successfully deleted and then created the new one but it persists in failing. Very strange. So I am wondering if Access is somehow keeping a version of the old query in memory, and that version is not getting updated when I do the delete and recreate of a query with the same name.

    Very frustrating

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: querydef problem (Access 2000)

    You might have to refresh the QueryDefs collection, but I suspect you will still have problems. It is also possible to pass an SQL string to the mail merge procedure instead of a query name; see (for example) the demo attached to <post#=362500>post 362500</post#>.

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: querydef problem (Access 2000)

    Just curious...
    Does assigning each queryDef in a batch a unique name (i.e. tmpQry1, tmpQry2,.., tmpQry100) correct the problem?

    Miguel

  6. #6
    New Lounger
    Join Date
    Jan 2004
    Location
    Long Island, New York, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: querydef problem (Access 2000)

    This is crazy. I am still having this problem. The query from over a month ago is still being pulled from an old querydef object. Isn't there some way to force it to update? I thought I had solved this problem and now I am pulling my hair out again. The idea of doing what you said tmp1, tmp2 etc seems ok, but then the next time when I start over at 1 it will pull the old one again. This is ridiculous. and I have done a repair and compact several times.

  7. #7
    New Lounger
    Join Date
    Jan 2004
    Location
    Long Island, New York, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: querydef problem (Access 2000)

    I also tried inserting a SQLString instead of a query and it took literally over 10 minutes to print each letter.

    I def need to figure out how to get the querydef object to update.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: querydef problem (Access 2000)

    I never experience the problems you mention, and I fear we won't be able to help you without seeing the database. If you like, you can post a stripped down version of the database:
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
    <LI>Attach the zip file to a reply.[/list]That will enable Loungers to look at the problem directly.

Posting Permissions

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