Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Unanswered: Access application using Queries

    Hi,

    I have a app I wrote which receives flat files (CSV) and processes them depending on the file name. I achieved this by a combination of VBA functions to import the files into tables and then a series of SQL language and stored Queries triggered by Docmd.openquery "Process1", Docmd.openquery "Process2" for example, with multiple steps and multiple queries to sort and calculate the new data and place it in different tables.

    While this works fairly well, and reasonably fast, recently I've noticed that the process may not be running correctly. Certain steps seemed to be skipped although no errors are reported. I have error 'traps' that should report errors and at which point the error occurred but none are evident.

    I remember something I heard once, maybe here, about the possibility of Queries running out of sequence when executed by the DoCmd.openquery method. I can't seem to find the alternate way to execute preset queries on my own so I thought I'd post.

    Let me just add that the queries are too complex to write as SQL script in VBA and there is a possibility that my theory on why certain queries aren't running may be entirely wrong.

    Any suggestions are very welcome. Bonus points if you know the source of my signature quotation.

    FlipperJ
    "Don't you hate Perry's wife?"

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This example might help on the browse and find the file to import if you're interested:
    http://www.dbforums.com/showpost.php...6&postcount=24 (there is another one for exporting in the code bank.)

    For troubleshooting your code, I'd also throw in a few message boxes in certain places and return some of the variable values to make sure when it has reached that step (and not skipping it), it's passing some kind of value.

    Queries should run in the sequence that you issued the docmd.openquery on them. The only difference is if you have nested queries to retrieve 1 query. The lowest nested query will execute first and then on up to the 1 query you used in the docmd.openquery.
    To alternalatively execute queries on your own, you could make a listbox on the form, in that listbox put rowsource query, you might have sql code like this: SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.DateCreate, MSysObjects.DateUpdate FROM MSysObjects WHERE (((MSysObjects.Name) Not Like 'MSys*' And (MSysObjects.Name) Like "Export" & "*") AND ((MSysObjects.Type) In (5))) ORDER BY MSysObjects.Name, MSysObjects.Type;

    Which returns all the query names in the mdb that begin with the wording "Export" (Note: you can remove the Like "Export" & "*" criteria.
    Then put some code in a button to export the highlighted name in the listbox....
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "[QueryNameSelectedInListboxName]", "C:\SomeFolder\SomeFile.csv", True
    or transfer it using TransferText
    Last edited by pkstormy; 09-22-08 at 21:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Bonus points if you know the source of my signature quotation.
    Arthur??...
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Source of my signature quotation?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Quote is from

    the movie Arthur, 1981. Ain't google grand?
    --If its free, take it for what its worth!

Posting Permissions

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