RE: [Berkeley-Access-Database-Users-Group] Powerful Queries topic - question

From: Cory N.
Sent on: Tuesday, June 5, 2012 11:29 PM

Global variables are stored in local memory. Users have their own memory space usually on their own computers or virtual machine if a cloud environment. So, there’s no crossover nor any ability to share.

 

 

Cory

 

 

From: [address removed] [mailto:[address removed]] On Behalf Of Suzie
Sent: Tuesday, June 05,[masked]:52 PM
To: [address removed]
Subject: Re: [Berkeley-Access-Database-Users-Group] Powerful Queries topic - question

 

That's an elegant way of handling it Cory.  I'm taking over a project right now where the programmer is storing filter values in a table (single row) and then using that table in queries without a join but I like your idea better (I also think the table idea is unstable).  I've used global variables for user login purposes (to check for security access and for audit trails) but I haven't done it your way.

 

I do have a question.  In a multi user system; if two or more users are querying the same recordset but with different values, can the global variable handle their 'instance' separately?  The reason I'm asking is that normally I get my users to all have their own copy of the front end on their computer (or at least their own copy somewhere) so that there's no way they can 'bump into each other' with temp values or tables; however sometimes they insist on all using the same file at the same time.  It doesn't seem to cause a problem when using the form for criteria but I'm wondering what happens with the global variable being set and then re-set by different users.

 

I hope that makes sense to everyone.  

 

JoAnne

 


From: Cory Nott <[address removed]>
To: [address removed]
Sent: Tuesday, June 5,[masked]:25 PM
Subject: RE: [Berkeley-Access-Database-Users-Group] Powerful Queries topic

 

Thank you Joanne.

 

You are right, I did neglect to mention that queries can call parameters from forms. The format would  usually be something like [forms]![xxxform]![txtfldname]

 

I usually avoid using those because the query loses portability. Instead, I will usually use VBA to store the value in a global variable or class property and then return it with a function. Using this, you can use a pop up form to define a filter, save the data to a global variable and then close the form. No need to have pop up forms all over the place or ensure that the form is open before calling the query. It’s the most basic of code, fortunately, so easy for any beginner in VBA to attempt.

 

On the subject of performance, I was testing some queries tonight that I had written a few years ago for the EEG system. At the time, one of the queries would 3-6 minutes to run. Now it just takes seconds. While my system is built for high performance, I think the Access[masked]x has far greater performance than its predecessors. It should not be overlooked as an option for large databases, unless there’s a need for robust security.

 

Cory

 

 

From: [address removed] [mailto:[address removed]] On Behalf Of Suzie
Sent: Tuesday, June 05,[masked]:36 PM
To: [address removed]
Subject: Re: [Berkeley-Access-Database-Users-Group] Powerful Queries topic

 

Hi Everyone,

 

I thought it was a great demonstration.  I think Cory hit some major hot spots of query design; for instance I've avoided subqueries because I've always heard they were slower than creating linked queries but I can see now when they can be used to advantage.

 

For myself one annoying aspect of Access is how often I have to build temp tables because a query would simply be too top heavy to run if I link many queries together.  I've hoped that SQL Server would be able to take on a greater load in that area because my routines become littered with temp tables and it makes it difficult to document and update a complex routine.  Maybe we can look at that in the future.

 

One thing does occur to me; and this would be for the people who are a bit new, we didn't go over parameters from forms; it's probably a good idea to explain how to pull values from a particular form as well in case not everyone knows.  I loved the global variable function as a filter.  It never occurred to me to use a function to pull a value from a global variable; that's a major time saver, normally I would write the sql statement in VBA because the function hadn't occurred to me.  

 

Thanks so much Cory for a great talk.  I'm looking forward to charts and to VBA from Dan.  I have my own style but it's always extremely helpful to see someone else's code.

 

Joanne

 

 

 


From: Cory Nott <[address removed]>
To: [address removed]
Sent: Tuesday, June 5,[masked]:24 PM
Subject: [Berkeley-Access-Database-Users-Group] Powerful Queries topic

 

Greetings all,

 

For any testimonials that anyone can provide from last night’s meeting, I would be extremely grateful.

 

Constructive feedback is welcome as well.

 

Also, feel free to pose any questions you might have, particularly on deriving better performance. We can turn those questions into blogs and share with everyone. I feel like I only covered the surface of some of those techniques.  

 

Thank you,

 

Cory Nott

 

 

Description: Description: Nott Logo - Blue
 Mail Sig

 

Principle Software Designer

Nott Ltd Software

2350 Stanwell Dr

Concord, CA 94520

Cell:[masked]

Office:[masked]

[address removed]

 

Description: cid:image003.jpg@01CCCF0D[masked]B0Description: cid:image004.jpg@01CCCF0D[masked]B0Description: cid:image005.jpg@01CCCF0D[masked]B0

 

Making software that is Nott Ltd

 





--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Cory Nott ([address removed]) from Berkeley Access Database Users Group.
To learn more about Cory Nott, visit his/her member profile
Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, PO Box 4668 #37895 New York, New York[masked] | [address removed]

 





--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Suzie ([address removed]) from Berkeley Access Database Users Group.
To learn more about Suzie, visit his/her member profile
Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, PO Box 4668 #37895 New York, New York[masked] | [address removed]





--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Cory Nott ([address removed]) from Berkeley Access Database Users Group.
To learn more about Cory Nott, visit his/her member profile
Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, PO Box 4668 #37895 New York, New York[masked] | [address removed]

 





--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Suzie ([address removed]) from Berkeley Access Database Users Group.
To learn more about Suzie, visit his/her member profile
Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, PO Box 4668 #37895 New York, New York[masked] | [address removed]

This email message originally included an attachment.

Our Sponsors

  • FMS, Inc.

    World Class Access Add-In Software

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy