Archiving queries (not data) (1 Viewer)

xBirdman

Registered User.
Local time
Today, 12:31
Joined
Oct 14, 2019
Messages
38
I'm trying to clean up a long-used database that hasn't been the most well-maintained. One really tedious aspect is the hundreds of queries that have been built, run a time or two, and then just left abandoned like so much scrap.

Doesn't anyone know of an automated method for basically exporting the SQL for all of those queries into text files? My current method involves opening each query in SQL view and copy/pasting into a text file of the same name. It would be really helpful to do it all in one or a couple of passes.

A few notes:
* I don't know (without digging in) which queries are currently being used regularly or intermittently (e.g., annually).
* I've been directed not to "just delete" because we could have public information requests and need to pull a set of data "as it was originally pulled to make sure it is the same data".

I'd love to hear how other folks would (or have) handle(d) such a task.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:31
Joined
Oct 29, 2018
Messages
21,358
Hi. There may be a utility available already for it, but it's also easy to do by looping through all the query objects and exporting the SQL statement into a file.
 

isladogs

MVP / VIP
Local time
Today, 19:31
Joined
Jan 14, 2017
Messages
18,186
Using built-in features first, I would start by using the Object Dependencies feature in Database Tools to find out which queries appear to be orphans. I would then rename each of those that look abandoned e.g to start with a z. Before doing so make sure you disable Name Autocorrect so any 'links' get broken. Carry on using the database for a few months and if something fails you can reverse the name change. Those unused after a while can be moved to an archive database or deleted.

Note that the dependency tool doesn't work for certain queries such as crosstabs.
However add-ins such as V-Tools (free) can deal with all queries if my memory serves me well.

You may also have unused forms & reports so I would check all database objects

As for knowing how often items are used, in one of my commercial apps I setup a logging feature which records each time various database objects are used. I did this both for auditing purposes (so I could prioritise future development work) and as part of an automatic error logging feature.

There is indeed a utility for logging all query SQL to a table. I saw this recently either here or at UA. If I can find it, I'll provide a link later.
 

Micron

AWF VIP
Local time
Today, 15:31
Joined
Oct 20, 2018
Messages
3,476
Renaming for a while, then importing into a 'library' db, then deleting from the main db after a period of time is what I'd do as well. You can always retrieve one if it becomes necessary. What is it that they say about great minds?:)
 

isladogs

MVP / VIP
Local time
Today, 19:31
Joined
Jan 14, 2017
Messages
18,186
:)
I described the processes I've done in exactly the same situation. Tidying up someone else's code is always a time consuming slog.
 

xBirdman

Registered User.
Local time
Today, 12:31
Joined
Oct 14, 2019
Messages
38
Some great and helpful thoughts. I have used the Dependencies function for those few which made me scratch my head, but I know that 96% of the queries are old and unused, so I'm very selective about adding that extra step - especially since, as a couple of you have suggested, I have a backup I can grab the query from if necessary.

I suppose another option, given the available copy, is to delete a block of queries (which I can reasonably assume aren't used) and let it sit for a week to see if anyone squeals. If so I track down their process and what query it points to and replace it from backup instead of trying to copy out all the code. I was just thinking having them all in a searchable folder would be useful. Hmm... Thanks for the thoughts.

Colin, if you find that link I'd love to have it, but don't spend much time on it. I'll mark this as resolved for now.
 

isladogs

MVP / VIP
Local time
Today, 19:31
Joined
Jan 14, 2017
Messages
18,186
Removing the likely candidates for deletion to another database is another approach. However its probably easier to restore renamed queries from the same database if needed

I can't find the link yet but I know it was a recent post at one of the forums.
In the meantime, you may find this example app of mine useful: View All Database Objects
 

isladogs

MVP / VIP
Local time
Today, 19:31
Joined
Jan 14, 2017
Messages
18,186
Excellent suggestion. Highly recommended.

Chrystal's analyser is like the Access database documenter on steroids!
If anything it has too much information

Another useful utility is Allen Browne's Database Issue checker
 

xBirdman

Registered User.
Local time
Today, 12:31
Joined
Oct 14, 2019
Messages
38
In the meantime, you may find this example app of mine useful: View All Database Objects

Wow, thanks. What a great tool. I need to dig around a bit - due to my older database, when I open the tool I get a

Compile error:
User-defined type not defined

which points at:
Public Function SetListBox(ObjectType As Integer) (highlighted)
with the selection on
Dim db As DAO.Database

I'm assuming it is this geriatric system I'm working on... ;-)
Really appreciate you sharing this.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:31
Joined
Aug 30, 2003
Messages
36,118
Older Access versions would need the "Microsoft DAO 3.6..." object library checked in Tools/References.
 

isladogs

MVP / VIP
Local time
Today, 19:31
Joined
Jan 14, 2017
Messages
18,186
Wow, thanks. What a great tool. I need to dig around a bit - due to my older database, when I open the tool I get a
Compile error: User-defined type not defined

I'm assuming it is this geriatric system I'm working on... ;-)

You haven't said which version of Access you are using

The View Database Objects app is an ACCDB file created in A2010.
It uses the 4 default references only



It has been tested in all versions from A2007 or later (32-bit & 64-bit) and the references will adjust automatically.

Older Access versions would need the "Microsoft DAO 3.6..." object library checked in Tools/References.

True for MDB files but not for ACCDB files (A2007 or later)
The Microsoft Office Access database engine library reference includes the DAO library items.
The older DAO reference is unnecessary and will conflict with the other reference. You can't run both at once
 

Attachments

  • RefsA2010.PNG
    RefsA2010.PNG
    12.5 KB · Views: 155
Last edited:

xBirdman

Registered User.
Local time
Today, 12:31
Joined
Oct 14, 2019
Messages
38
It is funny that I had the same issue with another routine I'd built today, and the suggested "MS DAO 3.6" fix resolved that specific issue. So now I opened up the DatabaseObjects app it works like a charm.

I think I'm dealing with front-end :: back-end differences. I built a mockup of the database on my desktop just to figure out how to set up some routines, build forms, etc. When I began moving those items into the Dev platform to actually build out the new interface, all the controls look different and I finally figured out that it was apparently the difference between my desktop front and the historic back end. I'm learning more each day than I've been forced to in a long time, and thus the questions and reliance other the collective hive knowledge for some of these issues. I love a good challenge and love working with data, but this is not an experience I could have dreamed up.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:31
Joined
Aug 30, 2003
Messages
36,118
True for MDB files but not for ACCDB files (A2007 or later)
The Microsoft Office Access database engine library reference includes the DAO library items.
The older DAO reference is unnecessary and will conflict with the other reference. You can't run both at once

I'm well aware of that, thanks. It seemed obvious from the description that the reference was needed in this case, as it turned out to be.
 

isladogs

MVP / VIP
Local time
Today, 19:31
Joined
Jan 14, 2017
Messages
18,186
My comment was aimed at the OP
As already stated, the DAO reference isn't needed for this particular app in any version of Access from A2007 onwards
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:31
Joined
Aug 30, 2003
Messages
36,118
My comment was aimed at the OP
As already stated, the DAO reference isn't needed for this particular app in any version of Access from A2007 onwards

And as already stated, the DAO reference was needed here.

You quoted me before your comment, so it's natural to assume the comments were addressed to me.
 

Users who are viewing this thread

Top Bottom