Check Query Status

[modest]

If you dont feel like reading my long post, I want to delete a query (after it's run) when the query window closes.

I know how to delete a query, it's more a question of finding how when a query window no longer exists, and triggering it to perform the deletion.


About myself:
I'm new to this site, and somewhat new to Access. I'm self-taught (the best way to go) =) I have had some experience in programming (not necessarily VB), but some C++. I've messed with Visual C++'s API (not using MFC). I know C++'s API gives more control when programming, but I can't really use that on the fly. I know Access's VB is scaled down, but was wondering if anyone had an answer. This leads me to my question.


The dB Background:
I hope this section answers some questions as to why I might be doing something. This dB is for someone at work to manage off-record accounts. Older women, that aren't tech-savy, use it. These women make mistakes and do things that might screw it up when I leave. They don't know how to run queries, so I've made a form to do it for them. There are about 10 - 15 fields that they can populate or leave blank, and just click "search" to find all the queries.


The Problem:
In a sub, I call on a function to check whether a query (queryName) exists; if it doesn't, it creates a new query (I'm using strSQL to store a SELECT statment).

I'm using a docmd.openquery to run the query, and have also set it to readonly.

at the end of the Sub i have a line that deletes the query. the problem with this is that users cannot use the "A-Z" or "Z-A" sort, to requery the data (because the query is deleted).

because they can't do that, i've commented out the delete query statement.


To anyone who can help:
I want to delete the query when the query window is closed. I need a way to check if the query window is open (maybe some way of storing the window's handle if possible), and a way to send a message when the window closes.

Please let me know.

 

[Mile-O]

You are letting non-technical people into the Query Grid? That can spell disaster.

Why not just make the queries they need? And if this is going to change all the time then allow them to create queries from a form - listing fields, tables, etc. in listboxes and then build a QueryDef.

 

[john471]

SJ,

Several hours ago when I first read this, it was my interpretation that modest is/was using a form to build a querydef (with code behind the form), which is then opened to display the results (datasheet) to the user.

The Problem:
In a sub, I call on a function to check whether a query (queryName) exists; if it doesn't, it creates a new query (I'm using strSQL to store a SELECT statment).

I'm using a docmd.openquery to run the query, and have also set it to readonly.

at the end of the Sub i have a line that deletes the query. the problem with this is that users cannot use the "A-Z" or "Z-A" sort, to requery the data (because the query is deleted).

because they can't do that, i've commented out the delete query statement.

Modest,

One possibility might be to create querydefs with names that adhere to a strict standard (e.g. including date created), and regularly purge ones that are more than x days old.

Another (more interesting ) possibility is to delete the query when the query window is closed (as per your original stated request).

I've had a foray into APIs (I'm still an extreme novice at using APIs) in search of an answer to the more interesting challenge. I take NO CREDIT for most of the code as it is borrowed (and manipulated a bit) from other posts/links in/from this forum - credit, where available in the source, was left intact.

Attached is a DB with the result I achieved. (AC97 / WIN2K).
It creates querydefs, and (usually) deletes them when they are closed. At the moment all Temporary Querdefs (TQ from here on in) have the same SQL, but I get the impression that you know what you are doing in that regard.

See the form in the attached DB. The data is (a subset of) NorthWind.

It has the following features:-
+* The TQ Name is has date, time & random component, so should be OK for multiple users.
+* You can have more than one TQ open at a time (either per user, or in total)
+* When a TQ is closed only that TQ is deleted
+* TQs are not deleted until they are closed, so users can access all of the functionality described in the original post that is not available if the TQ is deleted immediately after it is opened
+* TQs are saved immediately before they are deleted, so the user is not asked if they want to save changes
-* If a TQ window is open when the database is closed, it is not deleted.
-* If you go into design view on a TQ, and close it from there, it is not deleted
-* probably several others yet unknown

It also gives rise to the question of database bloat. Does creating/deleting numerous queries cause (or contribute to) this ?

You may need to write a cleanup routine to remove TQs that don't get deleted.

Of course now that I've done all that, another thought just crossed my mind; but it is very late so I am not going to experiment with it - here is a summary:-
Create a Form to show the query results (set the form to datasheet view). After you create your TQ, assign the form's record source to the TQ, and have that form delete the TQ as it closes (or unloads???). Don't know if this is feasible of not ??? Just another thought.

HTH.

At least I learned something

John.

 

[modest]

Thank you for the help

SJ,
Like John pointed out, I am already doing what you have suggested. Regardless, thank you for your response and attempt



John,
I appreciate the help thus far. I'm at work and security is tight so I cannot open a zip file (because I have no decompressor). I will see how helpful the provided file is when I get home.

I just wanted to add: don't assume I know anything technical =) It's been a while since I've delved in API calls, and I know less about specific VB functions.


again I haven't had a chance to look at the file yet, but I already have another question:
Is there a way to close the query window when the window loses focus.



I guess my questions all relate back to window control. John, I'm surprised you actually read all that I wrote in my first post, I applaud you for taking the time and not just being a thread-browser. Thank you again!

 

[IfButOnly]

A couple of thoughts on this:

I don't understand why you want to delete the Query when you do. As you have the Select statement stored in strSQL there are a number of other options:-
- delete and create a new one each time,
- modify the SQL on a change and update the QueryDef
- delete QueryDef on application close
..etc

Your question regarding closing the query window when it loses focus, you could try using the forms GotFocus and LostFocus events -eg when the main form has gets focus, check it the query window is open and close it.


Peter

 

[modest]

-eg when the main form has gets focus, check it the query window is open and close it.

1)Why I want to do this: because i want to =)
Right now i have the query set to hidden, to help provide some protection. However, if someone were to show hidden files, or go into the query window and run it, they could change data. It's easier for me to just delete then password protect or whatever.

Most people at my work are unfamiliar with actual VB code, but more familiar with changing settings of the application. It would just be safer to code it when I need it, and delete it when it's finished. (additionally, at this point, I don't want to password protect anything)


2)The quote above goes back to my question. HOW do i check and see if a query window is open & how do i close it.

 

[IfButOnly]

Here is some code couresty of 'the Access Web' & Dev Ashish to check if a form is open. If this is what you are after the use docmd.close object instead of the fIsLoaded.

Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
fIsLoaded = True
End If
End If
End Function

 

[modest]

is a form the same thing as a query window?

would the same thing be true for acQuery ...

 

[john471]

You can definiteley use something like

If SysCmd(acSysCmdGetObjectState, acQuery, szQueryName) <> 0 Then

to determine if the query is open, but if you need to further determine the current view; I have no answer for that at present.

 

[modest]

John,

I'm just now taking a look at the zip file you provided.

I am surprised to see a wndproc() or WindowProc() =) Again, I'm not an expert VB programmer, but this gives me hope that there is something useful here.

Currently, I'm debugging the database you've given me. It seems as though vba332.dll, which was once used for Access 97, has now been replaced with vbacv20.dll and in order to use it, I must reference it's full address.


I am still running into two problems with the provided db that I have to look further into, before I can investigate it's use:

1) When the query is open the scrollbar doesn't work, the only way i can scroll down is to use the mousewheel.

2) When the query window is closed an error occurs. (no error number is given --- this may be tricky to solve)

** my guess is it has something to do with vbacv20, or something related to converting from 97 to Access 2003


if anyone has any guess about these problems, please take a look at John's attachment and help a brother out =)

 

[john471]

I don't know if this suggestion is valid or not; but if the vba332.dll has now been replaced with vbacv20.dll, does the new dll include the same functions? can you just change the declaration statement to use the new dll ?

My scroll bars (vertical and horizontal*) work fine on the query window when it is opened, both in maximized and non-maximized state.

*although it is not wide enough to get a horizontal one in a maximized state - so I didn't actually test that.

HTH

John.

 

[john471]

OK,

I haven't got this cracked yet, but what I can tell you is that the use of the vba332.dll is to provide the address of the hooked procedure. That method of getting the address is required in AC97 because the AddressOf operator is not supported. In 2000+, you can remove those declarations, and the procedure that called them (AddrOf) , and then in cmdMakeTemporaryQuery_Click, remove the lngAddr variable (and associated lines), but first - change the last line to use the supported AddressOf operator

HookQuery lngHWnd, AddressOf WindowProc

Of course, this still freezes up, so there are still issues. Unfortunately (or perhaps fortunately) I won't be able to spend any more time looking into it for at least 24 hours.

Hope you make some head-way; or that soemone else (perhaps someone who knows what they are doing ) can take a look.

Regards

John.

 

[modest]

can you just change the declaration statement to use the new dll ?

Yes sir, and I did that, that's what I meant by "in order to use it, I must reference it's full address"

Doing that got rid of the Run-time error, but the scrollbar problem and the on close error still occured.


John, again I appreciate your help and please take your time; I don't deserve your full attention. I was hoping this forum would be a collaborative effort, but thus far it seems like I could have just emailed you back and forth.

SO if anyone else has suggestions I'm ready to take them on


Thanks,
Modest


Addendum:
This error has occured on my home computer running Windows XP SP2 & Access 2003 .... and my work computer running Win2k & Access 2002


Edit:
1. The two errors still occured whether I use vba332 or vbacv20 (I'll keep using vbacv20 since it is newer).

2. John, I made the address call changes that you suggested and have found some relief. The vertical scrollbar is working, however there is no response when I try to do anything with the query, as you previously stated.

I will look into the error. It sounds like a bad handle call of the query window, or possibly a WndProc loop problem and bad termination of the query window.

 

[Rich]

Why not just use temporary QueryDefs?

 

[modest]

Please explain how temp querydefs are used, I'm not as familiar with them.

Furthermore, John I am unsure what the problem is up to this point. I have determined that the handles are correct, I've even tried setting the handle to the form instead of the query (and the form pauses in this case).

My guess is it has something to do with either PrevProc or the way the window was called. Or, even possibly, the fact that it is a Query window instead of a form or report (I hope this is not the case).

If anyone has ideas, this is still the season of giving =)


Note:
As a precaution I've checked the code with
http://www.ilook.fsnet.co.uk/vb/vbntserv.htm and everything seems the same
I've also looked at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dninvb00/html/callback.asp for any differences. If anyone has any ideas on how to posting queries to a form, I'm also accepting

 

[john471]

On another tangent, how about hiding the form, and setting a form timer when the form opens the query, and then have the timer event check if the query is still open, when it is no longer open, delete the querydef, and re-display the form, (or close the form - whichever is most appropriate).

Option Compare Database
Option Explicit

Private mszTempQueryName As String

Private Sub cmdMakeTemporaryQuery_Click()
    Dim Q As New QueryDef
    Dim szSQL As String
    
    Dim szRandomChar As String
    
    Randomize
   
    ' Work out your own SQL here, depending on selections made in form
    szSQL = "SELECT Customers.CompanyName, Customers.ContactName, Customers.Phone, Orders.OrderDate, Orders.ShippedDate "
    szSQL = szSQL & "FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID "
    szSQL = szSQL & "ORDER BY Customers.CompanyName, Customers.ContactName, Orders.OrderDate, Orders.ShippedDate;"
    
    Q.SQL = szSQL
    ' Generate three random letter between a and z.
    szRandomChar = Chr$(Int((26 * Rnd) + 1) + 96)
    szRandomChar = szRandomChar & Chr$(Int((26 * Rnd) + 1) + 96)
    szRandomChar = szRandomChar & Chr$(Int((26 * Rnd) + 1) + 96)
    mszTempQueryName = "qryTemp" & Format(Now(), "yyyymmddhhnnss") & szRandomChar
    Q.Name = mszTempQueryName
    
    'Save the queryDef to the database's QueryDefs collection
    CurrentDb.QueryDefs.Append Q
    'release the object reference
    Set Q = Nothing
    
    'open the query display window
    DoCmd.OpenQuery mszTempQueryName, acViewNormal

    Me.TimerInterval = 500
    Me.Visible = False
    
End Sub

Private Sub Form_Timer()
    If SysCmd(acSysCmdGetObjectState, acQuery, mszTempQueryName) = 0 Then
        Me.TimerInterval = 0
        CurrentDb.QueryDefs.Delete mszTempQueryName
        CurrentDb.QueryDefs.Refresh
        Me.Visible = True
        'OR
        'DoCmd.Close acForm, Me.Name
    End If
End Sub

'And maybe add a clean-up routine to the form unload procedure
'Just to clean up anything that might have been left lying around...

Private Sub Form_Unload(Cancel As Integer)
    Dim q As QueryDef
    Dim szDeleteNames() As String
    Dim nCounter As Integer
    
    nCounter = 0
    For Each q In CurrentDb.QueryDefs
        If q.Name Like "qryTemp##############???" Then
            If SysCmd(acSysCmdGetObjectState, acQuery, q.Name) = 0 Then
                nCounter = nCounter + 1
                ReDim Preserve szDeleteNames(1 To nCounter)
                szDeleteNames(nCounter) = q.Name
            End If
        End If
    Next q
    
    If nCounter > 0 Then
        CurrentDb.QueryDefs.Refresh
        On Error Resume Next
        For nCounter = LBound(szDeleteNames) To UBound(szDeleteNames)
            CurrentDb.QueryDefs.Delete szDeleteNames(nCounter)
        Next nCounter
    End If
    Set q = Nothing
End Sub

 

[modest]

One Final Miracle

John,

Unfortunately, it was my last day of working for the company, so I couldn't implement my new design. However, I saved a copy of it for me to work on at home sowhen I get it working, I'm sure they'll let me go update it.

As my problem progressed (which soon became your problem as well), I seemed to encounter more and more errors (much like the freezing up) and so I began to cave in and become more and more open to the idea of using a different form with a datagrid.

Fortunately though, I think I stumbled over an answer, but I'll need your help to verify.

In an effort to get my friend's help (he's a very advanced window's programmer), I tried sending the file over MSN for him to look at. I noticed the file was around 500KB (rather big for a small file), so I figured I'd compress it, with a zip. Then I remembered Access's "compact and repair", and use that.

When I was expecting him to say "What did you do to make my machine freeze!?" ... I, instead, got "What's the problem?" ... I'm figuring Access repaired the problem for me , which was probably an error with the window addressing/memory as I assumed.

In any case, please try the "Compact and Repair Database..." feature located under "Tools" => "Database Utilities" and let me know if this fixed it for you too.

 

[john471]

Compacted & repaired; but problem still same - freezes up. If the choice were mine, I'd go with the last solution I proposed, which has no API calls.

 

[modest]

Hehe, yes, but the idea of using a timer just doesn't sit right with me. I like being as accurate as possible.


Do you mind posting your latest file that freezes up? I want to see if we made the same changes... additionally, I noticed with my own, that the only way i get it to freeze, is when i go to the VB code interface, leave the VB interface, and try to run the code again.

 

[john471]

... additionally, I noticed with my own, that the only way i get it to freeze, is when i go to the VB code interface, leave the VB interface, and try to run the code again.

I went back and made a further change, and am now experiencing the behaviour that you describe.

In the first (least successful) set of changes I was still obtaining (or trying to) the AddressOf WindowProc in the form module, and passing it as an arguement to HookQuery in the API module. Now I have re-considered that, and the HookQuery now reads (with previous version commented out)

Public Sub HookQuery(hwnd As Long) ', lngAddr As Long)
    PrevProc = SetWindowLong(hwnd, GWL_WNDPROC, AddressOf WindowProc) 'lngAddr)
End Sub

Which works untill after I open the code window, and then freezes up, requiring a termination of Access and re-launch .

Don't have too much fun

John.