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.
Full Version: Getting the name of a user-created query
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
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
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.
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.
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
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. . .).
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 & "."
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.
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.
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.
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.
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.
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.
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.
Use the ReDim statement to redimension an array. First, declare it something like
Dim ExistingQueries() as String
Then, your code can look something like
HTH
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 likeFor k = 0 To db.QueryDefs.Count - 1
ExistingQueries(k) = db.QueryDefs(k).Name
Next k
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.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
HTH
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.