Re-Using Queries

[Sonny Jim]

I am new to Access and would like to know the best way to set up queries for a database that uses 20 - 30 reports. Will it run better with a query built for each report or should I re-use queries for more than one report if possible? I am concerned that if two separate reports are run using the same Select query using different parameter values that there will be trouble. All my queries are Select queries.

 

[boblarson]

I am new to Access and would like to know the best way to set up queries for a database that uses 20 - 30 reports. Will it run better with a query built for each report or should I re-use queries for more than one report if possible? I am concerned that if two separate reports are run using the same Select query using different parameter values that there will be trouble. All my queries are Select queries.

You can definitely reuse queries and, based on your wording of your statement, I would assume that you are using this with multiple people all accessing the same MDB file from a server.

Two things, if that is true -

1. Split your database into a frontend/backend

2. Make sure each user has their OWN copy of the frontend ON THEIR MACHINE.

This will save you a load of grief.

 

[Sonny Jim]

Thanks Bob, actually I have set up multiple front ends and a single back end, however, my client's network guy is having everyone access the front ends from their server using Window's Remote Desktop Connection. Each front end has it's own name. Users come from different geographical locations and I guess this is their way around using a WAN or VPN. I know this strays a lot from my original question but you have me concerned about the front ends being all being on the server. What's your take on this? What risks are being introduced by this kind of set up?

 

[boblarson]

Thanks Bob, actually I have set up multiple front ends and a single back end, however, my client's network guy is having everyone access the front ends from their server using Window's Remote Desktop Connection. Each front end has it's own name. Users come from different geographical locations and I guess this is their way around using a WAN or VPN. I know this strays a lot from my original question but you have me concerned about the front ends being all being on the server. What's your take on this? What risks are being introduced by this kind of set up?

As long as they have their own frontends the Queries are going to be completely separate and changes by one person will not affect the others.

Now, it should be fine to have the front ends on their server, but it will present unique challenges as how do you give them the new updated front end? Of course it will require lots of work copying it and renaming and all. But, if that's the way they want it, then that's the way it will be.

 

[Sonny Jim]

Yes, I see. There won't be a lot of users, perhaps 5 or 6 at most. Thank you very much for your help.