Best / quickest way to pull data (1 Viewer)

tucker61 · Jun 26, 2017

I have a button that runs code to open a query (just like lots of people ) to be honest i have lots of buttons and lots of queries
Some are now redundant and are no longer required.

Is it better to convert the sql into vba and run from the button instead of running the query ?

Any thoughts.

jdraw · Jun 26, 2017

No. Not necessarily. Saved queries are fine.

How did you get to the point of "redundant queries and buttons"?

ashleedawg · Jun 26, 2017

If they are simple queries with no parameters that you need to run sometimes, there's no need to bother changing anything. ("If it ain't broke, don't fix it.")

However, if the reason you have so many buttons is because there are minor variations of the same queries - and perhaps you continue to add buttons when a slightly different result is required - then maybe it's time to start thinking about some automation; a tidy form for selecting the detail and criteria for the data you need to view.

It't tough for us to picture what you're dealing with; perhaps a screen shot of your "buttons form" would give us some insight...

kevlray · Jun 26, 2017

It sound like you may need an active filter for some of these queries (thus reducing the number of queries). There are a few ways of doing an active query. I am sure members more experienced than my self could provide some examples.

tucker61 · Jun 26, 2017

Thanks all, I won't worry about it then, I just thought it might be slower if using queries to pull the data

What I tend to do is create a query, name it and then forget what it was for originally, so I create another one next time I need data, in total I must have approx 30 queries, some of them are dependant on other queries , some of them have been replaced with new versions, with additional criteria but I have not deleted the original query

There is only me going into database and doing this, all other users have to use the buttons, if they want anything more complicated they have to ask me.

I think I just need to have a tidy up and delete all my old queries.

isladogs · Jun 26, 2017

Queries can be faster than VBA.

Before deleting lots of old queries, I would suggest you devise a system for renaming those you think are redundant e.g prefix them all with something unusual for you like # or z. Then mark them as hidden

This is also helpful in cases where other queries depend on the ones you want to delete.

If after a period of time you really don't need them then by all means bin them.

tucker61 · Jun 26, 2017

I started renaming some of them just by adding OLD to the end of the query name.

You are right, I will leave these for the next few weeks and delete them.

Thanks for all the help

Tieval · Jun 27, 2017

Go to the Database Tools ribbon, select a query and click Object Dependencies, if nothing depends on it, it is presumably obsolete.

CJ_London · Jun 27, 2017

I've got into the habit of adding a prefix to all queries

dv... in development
pd... in production
zz... to be deleted

with a name that means something e.g.

pdListCustomers
pdListCustomersSQ1

and where you have a query 'chain' use a suffix to indicate the order

using a prefix also has the benefit that you don't need multiple buttons on a form - you can use a listbox, combo or subform with a source something like

SELECT mid([Name],3) as displayName, [Name]
FROM msysObjects
WHERE [Type]=5 AND [Name] Like 'pd*' AND [Name] not like 'pd*SQ*'
ORDER BY mid([Name],3)

Which means you can add new queries and have no need to change your 'buttons' form

Same applies for referencing forms (type=-32768) and reports (type=-32764)

Alternatively for listboxes and combos, you can iterate through the querydefs/forms/reports collections to populate a value list rowsource. Benefit here is you can reference the description property so you can maintain good naming practice of no spaces for your code, whilst having a user friendly description appear in your list/combobox.

Minty · Jun 27, 2017

CJ_London said:
Alternatively for listboxes and combos, you can iterate through the querydefs/forms/reports collections to populate a value list rowsource. Benefit here is you can reference the description property so you can maintain good naming practice of no spaces for your code, whilst having a user friendly description appear in your list/combobox.

@CJ - How do you reference the description? I can't see it in the object table.

CJ_London · Jun 27, 2017

It's a property and only exists if completed. You need to complete the description in object properties (right click on query name, general tab) - not in the query properties

vba would be something like

Code:

Dim QDef As QueryDef
Dim valList As String
 
For Each QDef In CurrentDb.QueryDefs
    With QDef
        On Error Resume Next
        valList = valList & .Name & ";" & .Properties("Description") & ";"
    End With
Next QDef
Debug.Print valList

which works on the assumption you only included descriptions for the queries you want to appear in the list/combobox.

Galaxiom · Jun 27, 2017

tucker61 said:
I started renaming some of them just by adding OLD to the end of the query name.

Beware NameAutoCorrect. It is turned on by default. If often catches out new developers renaming objects as "backups" when creating a replacement object.

NAC will propagate name changes in tables and queries to dependent objects. So renaming your query or the fields in it will automatically rename these objects in dependent queries.

The renaming actually happens next time the dependent query is run.

Best / quickest way to pull data (1 Viewer)

tucker61

Registered User.

jdraw

Super Moderator

ashleedawg

"Here for a good time"

kevlray

Registered User.

tucker61

Registered User.

isladogs

MVP / VIP

tucker61

Registered User.

Tieval

Still Clueless

CJ_London

Super Moderator

Minty

AWF VIP

CJ_London

Super Moderator

Galaxiom

Super Moderator

Similar threads

Users who are viewing this thread