Getting all my SQL in one place (1 Viewer)

Rog

New member
Local time
Today, 09:58
Joined
Jul 31, 2001
Messages
8
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

Registered User.
Local time
Today, 04:58
Joined
Mar 2, 2000
Messages
354
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

New member
Local time
Today, 09:58
Joined
Jul 31, 2001
Messages
8
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

AWF VIP
Local time
Today, 04:58
Joined
Jun 2, 2001
Messages
2,734
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

New member
Local time
Today, 09:58
Joined
Jul 31, 2001
Messages
8
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.
 

Users who are viewing this thread

Top Bottom