UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Getting the name of a user-created query    
 
   
rdemyan
post Sep 19 2005, 09:53 AM
Post #1

UtterAccess Guru
Posts: 976
I need to get the name of user-created query which was created using the built-in Access new query wizard.

The built-in new query wizard is launched from my app via (in my app this is the only way to launch it due to custom menus):

RunCommand acCmdNewObjectQuery

The user then uses the query wizard, does whatever, and processing should then return to my code module.

Is there a way I can get the name of the query just created by the user? I need to modify the name if it doesn't meet naming conventions I've established.

Thanks.



Edited by: rdemyan on Mon Sep 19 10:59:01 EDT 2005.
Go to the top of the page
 
+
Fletch
post Sep 19 2005, 10:06 AM
Post #2

UtterAccess Ruler
Posts: 2,329
From: Northern Virginia, USA
My initial thoughts would be to either go through all the queries and look at the DateCreated property and find the most current one (probably my preference) or to store the names of all queries in a data structure (array, collection, etc.) and then compare all queries against this data structure after that command to detect the new query.

Alternatively, if you're trying to match a naming convention, you could cycle through all queries and detect all queries that don't meet your naming convention and rename them as appropriate.

HTH (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)
Go to the top of the page
 
+
rdemyan
post Sep 19 2005, 10:21 AM
Post #3

UtterAccess Guru
Posts: 976
I think the date idea should work. To provide you with more info:

1) I've created a read-only ad-hoc query app (no deleting, updating, appending, etc) that goes against my backend tables where all permissions have been removed and I have RWOP queries to access the tables.

2) I need to differentiate user-created queries from RWOP queries so I can display the user's quereis to them in a listbox. I don't want the RWOP queries displayed because entries in the listbox can be deleted or renamed (so only user created entries should show up).

3) If the user uses the wizard, Access creates a default name for the new query which includes the data source (in my case, the RWOP query). So I currently have a 3-letter identifier added to my RWOP query names and I use this to prevent them from being listed in the user-created query listbox. But if the default Access name for the new query is used, then the Identifier is in that name and the user's new query won't show in the listbox.

So what I would like to do is after the user creates the new query, check the new query name for the identifier and replace the 3-letter identifier with "" if it is present.

How do I extract the Date Created for queries.

Thanks.
Go to the top of the page
 
+
Fletch
post Sep 19 2005, 10:50 AM
Post #4

UtterAccess Ruler
Posts: 2,329
From: Northern Virginia, USA
Thanks for the background. As I read it, though, your question is just about DateCreated (so if I'm missing something else, please let me know!).

CurrentDb.QueryDefs("name of query").DateCreated

returns the date created where you replace name of query with the name of a query (or lose the quotes and insert a number to reference a query).

So, see if this code snippet gets you going in the right direction
CODE
Dim qry As DAO.QueryDef, dtmDate As Date, strQueryName As String

For Each qry In CurrentDb.QueryDefs
    If dtmDate < qry.DateCreated Then
        dtmDate = qry.DateCreated
        strQueryName = qry.Name
    End If
Next qry

Debug.Print "The most recent query is " & strQueryName & " created on " & dtmDate & "."
Basically, replace the debug statement with whatever code you want to use to work on the query name b/c at that point you've established the most recently created query. Also note that there are system queries--haven't played with them much, but to be absolutely safe you might want to filter them out just in case one gets created right after a user creates one (highly unlikely, but just in case. . .).
Go to the top of the page
 
+
rdemyan
post Sep 19 2005, 12:22 PM
Post #5

UtterAccess Guru
Posts: 976
Thanks for the code, but I decided to instead compare the new list of queries with the old list stored in array, because it is possible that the system date could get changed.

I'm trying to improve my coding technique, so I've listed my code below. It seems to do what I want, but any suggestions for improvement are welcome. Note that the IsObjectOpen function is a standard one that I took from somewhere, so I've not include the code for that here. I'll be adding error handler code to the CheckNewQueryName subroutine. Also, global variables are defined up at the top of the module (not shown here) and I'm not good about defining all variables. Note that I have added a single quote on blank lines, because this is the only way I know of to include blank lines so that the code is more readable. If you know of a better method please lete me know.

Finally, I have a counter, NbrNewQueries, at the end. Strangely when a new query is created, I also get back that two queries have been created. One is, in fact, the new query and the other one is always the same query that starts with ~sql. It's not really a problem as it doesn't get renamed, I just don't know why this same query pops up as being new.

CODE




Private Sub New_Query_Button_Click()

'

Dim NewlyCreatedQuery As String

'

On Error GoTo Err_New_Query_Button_Click



'First get all existing queries and put them into an array so that

'the code can find the new query afterwards.

Set db = CurrentDb()

'

For k = 0 To db.QueryDefs.Count - 1

    ExistingQueries(k) = db.QueryDefs(k).Name

Next k

TotalNbrExistingQueries = k + 1

'

'Now call built-in new query wizard

RunCommand acCmdNewObjectQuery

'

'Make sure that if the dbWindow was displayed that it is closed so

'user cannot see it

DoCmd.SelectObject acTable, , True

DoCmd.RunCommand acCmdWindowHide

'

'Get the name of the newly created query, if any

Call CheckNewQueryName(NewlyCreatedQuery)

'

Exit_New_Query_Button_Click:

    Exit Sub

'

Err_New_Query_Button_Click:

'

    If Err = 2501 Then

        Resume Next

    End If

'  

    'Make sure that the db Window is not displayed

    DoCmd.SelectObject acTable, , True

    DoCmd.RunCommand acCmdWindowHide

'

    MsgBox Err.Description, vbOKOnly + vbInformation

    Resume Exit_New_Query_Button_Click

'  

End Sub

'

'+++++++++++++++++++++++++++++++++++++++++++++++++

Public Sub CheckNewQueryName(NewUserQuery As String)

'

'Get all of the current queries

Set db = CurrentDb()

'

NbrNewQueries = 0

For k = 0 To db.QueryDefs.Count - 1

    For j = 1 To TotalNbrExistingQueries

        If db.QueryDefs(k).Name = ExistingQueries(j) Then

            'Move to next query name

            GoTo 100

        End If

    Next j

        NewUserQuery = db.QueryDefs(k).Name

        'Close the query if it is open so it can be renamed

  '      

        If IsObjectOpen(NewUserQuery, 1) = True Then

            DoCmd.Close acQuery, NewUserQuery

        End If

'        

        'Now modify the name to remove the identifier

        If InStr(1, NewUserQuery, "rjd") > 0 Then

            ModifiedQueryName = Replace(NewUserQuery, "rjd", "")

            DoCmd.Rename ModifiedQueryName, acQuery, NewUserQuery

            'Now open the query again so the user can see it

            DoCmd.OpenQuery ModifiedQueryName, , acReadOnly

        End If

'

        NbrNewQueries = NbrNewQueries + 1

100: Next k

'

End Sub


Edited by: rdemyan on Mon Sep 19 13:24:15 EDT 2005.

Edited by: rdemyan on Mon Sep 19 13:28:56 EDT 2005.
Go to the top of the page
 
+
Fletch
post Sep 20 2005, 07:20 AM
Post #6

UtterAccess Ruler
Posts: 2,329
From: Northern Virginia, USA
A few comments if you're interested. I think the one thing that you should always avoid is the use of GoTo. The only place for the GoTo statement is for error trapping. Otherwise, there is always another way to get by without using it. Almost all programmers acknowledge that its use is poor programming practice.

As you mentioned, you have some global variables that you're not showing. However, I'm not seeing how you dimension the ExistingQueries array. Seems like something you'd want to do on the fly since you don't know in advance how many queries will exist. And, when you say "I'm not good about defining all variables," I would highly encourage you to dimension all variables. By placing Option Explicit at the top of your code, you will get an error if you don't dimension a variable (you can change an option in VBA to automatically place Option Explicit at the top of all code modules if you require variable declaration). I can't tell you how many times I've seen people stumped by code that appears not to work only b/c they mistyped a variable name somewhere. With Option Explicit, that mistake would be caught.

As far as using TotalNbrExistingQueries, you don't need that variable. CurrentDb.QueryDefs.Count will give you the total number of queries.

Anyway, just some quick thoughts for you--take 'em or leave 'em. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)
Go to the top of the page
 
+
rdemyan
post Sep 21 2005, 12:13 PM
Post #7

UtterAccess Guru
Posts: 976
Thanks for the comments. Yeah, I've been concerned about dimensioning the ExistingQueries array. Right now I have it set to 1000, which I know is not good programming practice. How can I set it on the fly?

I agree with you on the GoTo. Most of the time I don't use it, but if I'm in a hurry, I'll use it because my mind seems to see this possibility much quicker than an alternative. I have started to increase my use of Option Explicit and actually have a few forms where I use this. Actually, I hate it, because I create code on the fly mostly and this slows me down. Still it has been helpful in the manner you stated.

The big point for this module is the array dimensioning. If you could help me with that, that would be great.

Thanks.
Go to the top of the page
 
+
Fletch
post Sep 21 2005, 02:07 PM
Post #8

UtterAccess Ruler
Posts: 2,329
From: Northern Virginia, USA
Use the ReDim statement to redimension an array. First, declare it something like
Dim ExistingQueries() as String

Then, your code can look something like
CODE
ReDim ExistingQueries(0 to db.QueryDefs.Count - 1)
For k = 0 To db.QueryDefs.Count - 1
    ExistingQueries(k) = db.QueryDefs(k).Name
Next k
Note that when you use ReDim, all contents of the array are lost. However, you can use the Preserve keyword to keep them intact (see the VBA help files for more info). So, another solution to your problem might be something like
CODE
ReDim ExistingQueries(0 to 0)
For k = 0 To db.QueryDefs.Count - 1
     If k > 0 Then
          ReDim Preserve ExistingQueries(0 to k)
     End If
    ExistingQueries(k) = db.QueryDefs(k).Name
Next k
Clearly, since you know the upper bound to begin with, this approach is much less efficient as you're constantly re-demensioning the array. But, it's purpose is just to show you another solution. If you didn't know the upper bound when you started your For loop, you might have to take an approach like this one.

HTH (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 25th May 2013 - 04:29 AM