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

    Unanswered: Access VB is neurotic with queries

    Hey there!

    I'm building a application using Access. Using some nifty forms, I have the user create a new record in a temp table so they don't mess up the "real" data until I validate the information they enter. Then I have a query append the new record to the "real" table and have another query delete the temp table entry.

    My problem is that while I've used this method many times, when I call the queries using Visual Basic scripts attached to the form, they run but they paste zero records and delete zero records. When I run the queries manually, they work just fine. I thought it might be a delay in the record being recorded or something but I don't think that's the case.

    I don't get it. Is something corrupt? I have many, many hours into this database ap and I would really, really hate to start over.

    Any thoughts? Anyone see this behavior before?

    Thanks in advance!

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

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Queries

    What is your vba coding which runs the queries? I run append/delete queries from vba code and they execute just fine. Seems wierd that it would work when you run it manually but doesn't work when you run it using vba code to execute the query. I'm guessing something's amiss with the code. Again, what is the code you've written? You mention Visual Basic scripts...Are you actually trying to shell and run a VB script or do you have vba code to execute the query?

    You can also just copy the append/delete query syntax and use that as part of your vba code to do the work so you don't need the queries at all (as an alternative method).
    Last edited by pkstormy; 09-07-06 at 13:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2003
    Posts
    18
    Thanks for the reply! I'm using what I think is pretty standard VBA language:

    DoCmd.Setwarnings False 'So that the user doesn't see it happening
    DoCmd.Openquery "queAPPENDthis"
    DoCmd.Openquery "queCLEARtemp" 'A delete query
    DoCmd.Setwarnings True

    I had a suspicion that the record was "saving" too slowly - sometimes it worked sometimes it didn't. So I added a delay, I had it do some refresh/ housekeeping chores before I ran the queries and now it works right every time!

    I had this happen once before where a called a query from VBA and it failed. I fixed it before by (of all things) opening the query and re-saving it. That shouldn't have worked I know, but it did.

    I'm self taught and I know there's a way to call queries with scripting but it's not in my books. Is it something you can tell me in a couple of lines?

    Many thanks!!!!

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

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To get you started, look at the toolbar which has the Design View, SQL View, DataSheet View drop down (usually under the File menu in the top left). Click on the SQL View. This shows you the code the query is actually running. There's different ways you can execute this code in vba (someone help me out here) but you can copy and paste this code into a vba code statement. You'll probably have to tweak it a little for the syntax.

    Your vba code looks like it should work with no problems (same way I would do it) so the only thing I can tell you regarding that is make sure your form is refreshed and you're on the record you want to be (especially if the query references/has parameters based on the form! If not, then something else is going on.) It's very easy for a user to scroll to a blank record on a form so you might want to look at some of the properties of the form (maybe set the allowadditions to false, or the cycle equal to Current Record, do a check to see if a certain primary key is null before running the queries in code, etc.)
    Last edited by pkstormy; 09-07-06 at 14:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    consider replacing:

    DoCmd.Setwarnings False 'So that the user doesn't see it happening
    DoCmd.Openquery "queAPPENDthis"
    DoCmd.Openquery "queCLEARtemp" 'A delete query
    DoCmd.Setwarnings True


    with:

    currentdb.execute "queAPPENDthis"
    currentdb.execute "queCLEARtemp"


    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Reason being "Openquery" can run asynchronously, execute is linear.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Oct 2003
    Posts
    18

    Smile

    Thanks everybody. I'm a department of one - it's good to have help.

    Thanks very much! You guys are great!
    "Don't you hate Perry's wife?"

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're doing a make-table type query
    Docmd.openquery "XXXX" will delete the current table if it exists and make a new table.
    currentdb.execute "XXXX" will fail if the table exists.

    There also seems to be some other differences between the two so I think there might be some pro's and con's to using each one. I believe the docmd.openquery method works better when you are referencing it with parameters to controls on a form since it is run "within" Access and even though the currentdb.execute command does execute a query faster, you may have problems with it when making changes to the parameters of the query - I think it's more designed for static queries with unchanging parameters.
    Last edited by pkstormy; 09-08-06 at 14:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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