Some Help on Navigation Categories and Newly Created Queries

[Brilstern]

Ok so after extensive searching I have only found one answer to my question. You can not use VBA to auto assign a new item to a category. Which would be awesome but apparently the only way to do so is to do it the the UI.

So my question is, has anybody came up with or know of a simpler solution than having to navigate to unassigned items and manually adding it every time I create one. Its not that big of a deal but I run a command very often that creates 6 new queries every time, and I would like to avoid "cluddering" up my Navigation pane.

Any help would be appreciated.

Sgt B

[Rabbit]

I have no idea what you mean by category.

[Brilstern]

If you right click on the top of the navigation pane it gives you the option to select a category. Within each category there are groups. Essentially I want to find out if there are any shortcuts for adding new queries to a certain group within a certain category other than the normal UI way of just right clicking on them and adding them or dragging them in the Nav pane to the group desired.

[NeoPa]

Correct me if I'm wrong, as I'm largely guessing here and trying to read between the lines (which indicates how poorly expressed the question is Stevan), but are you perhaps referring to the assigning of the database objects (Tables; Queries; Forms; Reports; etc) to Groups - normally available from the main database window?

If so, you should explain which version of Access you're using (as I believe these groups are very version dependent) and you should understand that the groups merely contain links (similar to shortcuts) to the said items, rather than these items being moved out of the main Table or Query window per se.

[Brilstern]

I apologize for the vagueness as it is kinda difficult to explain. Here goes though...

Vr. Access 2007
I do understand that none of these objects actually move from Category to Category/Group to Group. They are indeed shortcuts unless the are in the OM created group "Unassigned Objects", which is automatically added to all Categories UI or OM created alike.

I understand that there are UI and OM created categories. For ex. I use a UI created category of "Working" for my normal Navigation Pane view. Within this category it contains various groups particularly "Weekly Build Queries" which contains 6 queries for each week of the year (sometimes more than one in a week)in ex.

"A001-11" 
"A001-11E"
"T001-11"
"T001-11E"
"X001-11"
"X001-11E"

These 6 queries were created on the first week of the year 2011.

Now when I create these they automatically are shown in the "Unassigned Objects" group within the "Working" Category. I then manually highlight all 6 new queries, right click and add to group "Weekly Build Queries".

My questions is there any way to expedite this. From what I have found online there is no a way to move them except through the UI. Do you know of a better way than my current action?

**Reason being I currently manage this database and make all changes but I will soon return home from this deployment and change over with my replacement. He might not be as access savvy so I need to make this as dummy proof as possible.**

Thx in advance. Hope there is a solution

Sgt B

[NeoPa]

I will reply promptly by saying I have no experience with this programatically at all. I've used Groups a couple of times in 2003 (My current version) but found the manual overhead required for my circumstances outweighed any benefits, so lost interest. I will have a quick look around for you, but if any other experts have any experience it would certainly be worth their jumping in.

PS. I think your last post made the situation (and the question) much clearer, so experts can at least focus on your question now :-)

[NeoPa]

I found this somewhat less than hopeful comment in Programatically accessing "object groups" :

Anthony England:
This is what I put into the Google Groups search page:
"database window" objects groups favorites
All answers (some by very experienced Access folk) seem to point to the
answer:

"Abandon all hope of doing anything with these - they are not accessable in
any way apart from the Access UI which is provided"

My own researches found very little more than this. I found nothing that would help you I'm afraid :-(

[ADezii]

@Stevan:
I'm afraid that your initial assumption may be correct in that there is no way to programmatically add a New Item to a Category, the only Option being through the User Interface.

[Brilstern]

Thank you both very much for your replies! I was afraid from the get go that I was SOL but I figured I would at least ask. I appreciate the prompt replies.

[Brilstern]

This may be a stretch but if you go to your navigation options in a database and select show system objects, you will see a series of tables in ex.

MSysAccessStorage
MSysAccessXML
MSysACEs
MSysComplexColumns
MSysIMEXColumns
MSysIMEXSpecs
MSysNavPaneGroupCategories
MSysNavPaneGroups
MSysNavPaneGroupToObjects
MSysNavPaneObjectIDs
MSysObjects
MSysQueries
MSysRelationships
 

These are all the system objects on one of my particular database's.
Is there any connection here do you think that be used like an update query or something. I know this is really dangerous messing with system objects but just curious.

Sgt B

***I can provide my database if that would be easier.***

[NeoPa]

If it works at all it would only be for 2007 or later as the MSysNavPane... tables don't exist in a 2002/2003 database :-(

[Brilstern]

I think I might make the better decision here and leave it alone. Thx for everything.

Sgt B

[ADezii]

I would strongly suggest NOT utilizing the System Tables to accomplish your goal, unless you knew in advance the purpose, Data Types, and acceptable Values allowed in each Field.

[Brilstern]

That is exactly my reason in putting it to rest. I know pretty much nothing about the inner workings of the system tables so I am just gonna put this aside indefinitely.

sgt B

[NeoPa]

Stevan:
I think I might make the better decision here and leave it alone

As ADezii so aptly says, unless you are absolutely sure of your ground in that area, this decision sounds eminently sensible.