Getting all my SQL in one place

[Rog]

I write hundreds of Access queries but only know a little bit of VB. What would make my life a lot easier is if I could see all a database's queries in one place, do global 'find and replaces', then replace all the queries where they came from. It's so time-consuming going into every query and making similar changes over again...

Does anybody know how I would do it?

 

[Chris RR]

Are you using parameters in your queries? They can drastically cut the number of queries that you need to create.

You should also take the leap and look into learning VBA and how to create pass-through queries. Definitely worth it.

 

[Rog]

I know how to use basic parameters in Access. My problems stem from processing data for 8 regions within my company, and wanting to keep the data separate.

I sometimes copy info to a common file, process it, (often lots of queries) and then copy results out to a specific file again.

But mostly I have lots of queries, which I process sequentially for one region, then want to process on another region's data.

I know it's ham-fisted. Just being able to parameterise file names would save me hours every week.

 

[raskew]

Believe you've identified the problem yourself, i.e., maintaining separate tables for each region, when each table containing identical fields.

While this approach might be necessary with a spreadsheet approach (similar pages for each region), it serves no purpose in a relational database and is in fact self-defeating. All you need is a field {e.g. txtRegion)which identifies the region, and one set of queries with the criteria for field txtRegion set to [Enter region]. It will return whatever the query is setup to return, based on the region you enter.

 

[Rog]

Thanks 'r' - you're right. I needed someone to point this out even tho I really knew it.

Now I've just got to think thru the paranoia which led me to keeping all the data separate in the first place, and being confident that I'm keeping control of all the individual files that the regions throw at me.