How to do a global "search and relpace" type function in Access queries

I am converting an extensive Access 2000 database to SQL.  Unfortunately, the original developer used "-1" / "0" as criteria in the queries insted of "Yes" / "No".  Since SQL does not recognize -1 as a "Yes", I need to change every query that has a "-1" criteria to a "Yes" criteria instead.

Does anybody know if there is a simple way to do this short of manually opening each query and changing.  Ideally I am looking for a search / replace type functionality or an simple VBA coding idea.

Ultimately I can run documenter on the queries, export to rtf, then do a search through there to identify queries that contain "=-1" in the SQL.  Then go back to each identified query and manually change the dozen or so identified.  This would take about 30 minutes to an hour -- so any proposed answer needs to be less work than this.

Thanks!
LVL 14
mds-cosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Rick Fisher's 'Find and Replace' utility ($39 buy, free demo) allows you to find and replace words in all Access objects:
http://www.rickworld.com

Speed Ferret ($199 buy) does the same thing, but with VB6, Access, and SQL, all in one swoop:
http://www.moshannon.com
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
therealmongooseCommented:
The sub attached will work to an extent, however you should be aware that it will replace all instances of "= -1" and "= 0" with "Yes" and "No" respectively wihtin the query - potentially changing criteria that you don't want changing....

Depending on how many queries you have it may be easier to make the changes manually....
sub changequeryclause(strQueryName as string)
 
dim dbs as database
dim qdf as querydef
dim strSQl as string
 
set dbs = currentdb()
set qdf = dbs.querydefs(strQueryName)
strsql = qdf.sql
 
strSQL = replace(strsql, "= -1", "= 'Yes')
strSQL = replace(strsql, "= 0", "= 'No')
 
qdf.sql = strsql
 
qdf.close
set qdf = nothing
dbs.close
set dbs = nothing
 
end sub

Open in new window

0
therealmongooseCommented:
you could also wrap the procedure in another to interate through all of your queries... BUT you need to be sure that you are ok for all queries containing the syntax "= -1" or "= 0" can be changed....

You may need to use "=-1" and "=0" (no spaces....) in the replace function above - open a copule of queries in sql view to check the format of the where clause...

I'd also strongly recommend making a backup copy before running the procedures...
sub changequeries()
 
dim dbs as database
dim varitem as variant
 
set dbs = currentdb()
 
for each varitem in dbs.querydefs
 
   changequeryclause(varItem.anme)
 
next varitem
 
dbs.clsoe
set dbs = nothing

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

aikimarkCommented:
@mds-cos

1. I agree with JimHorn about the third party utilities.  Very good expenditure of your $$$$

2. I do not think you should use Yes/No in SQL Server.  Intead, you should use True/False.

3. The translation of MSAccess SQL to TSQL can be quite tricky.  For instance, into what kind of column type are you converting the Access Yes/No columns?  You might need to compare to 1/0 literals.

4. Even Roll-Your-Own SQL changes need consider how/where the literal values are used.
Example:
strSQL = replace(strsql, "= -1 ", "= True")
strSQL = replace(strsql, "= 0 ", "= False")
strSQL = replace(strsql, "=-1 ", "= True")
strSQL = replace(strsql, "=0 ", "= False")
strSQL = replace(strsql, "= -1;", "= True;")
strSQL = replace(strsql, "= 0;", "= False;")
strSQL = replace(strsql, "=-1;", "= True;")
strSQL = replace(strsql, "=0;", "= False;")

5. This is a good opportunity to rewrite some of your queries, taking advantage of the Server-grade database engine's capabilities.  Start with the queries that are run most often or that run the slowest.
0
Jeffrey CoachmanMIS LiasonCommented:
mds-cos,

Please consider *Not* changing the Number to Text values.
Obviously, leave Zero, but change the -1's to +1's

Then make a Yes/No table

Table Name: tblYesNo
Fields:
YN_ID, Primary key, Integer
YN_Text, Text, 3 chars

YN_ID    YN_Text
   0           No
   1          Yes

This way you can ues this table for all your Yes/No lookups
It is far easier to refer to variables in VBA code when they are numbers, than if they are text strings.
It is easier to get counts of the Yes's and No's if they are numbers.
Yes/No is typically used for Option groups and checkboxes. Option groups and checkboxes are based on numbers *Not* text. If you use text, you will have to create "Translation code", to get them to work.

Basically it is applying the same database theory as you do in all your other tables.
(You store the number, not the text)

Just my 2c

JeffCoachman
0
aikimarkCommented:
I think the Jet engine translates these boolean values as they come into the PC.  In the MSAccess/VBA world, 0 is False and any non-zero value is True.
0
Jeffrey CoachmanMIS LiasonCommented:
aikimark,

<I think the Jet engine translates these boolean values as they come into the PC.  In the MSAccess/VBA world, 0 is False and any non-zero value is True.>
Yeah that's what I thought.
I just was not sure with all the different flavors of and SQL server floating around these days!
;)

My only concern is the confusion for new users over storing the actual text string "Yes" or "False", versus storing the value of 1, or False.
Thanks for the clarification.
:)

JeffCoachman
0
aikimarkCommented:
Yes/No seems to be an MSAccess 'feature' that isn't implemented in other relational databases' SQL.

Avoid string comparisons whenever possible for performance reasons.  The advantage of using a checkbox control is that it minimizes user confusion.
0
Jeffrey CoachmanMIS LiasonCommented:
aikimark,

<Yes/No seems to be an MSAccess 'feature' that isn't implemented in other relational databases' SQL.>
Ha! Ha! Ha!!...
I Had to laugh!
:)

See here:
http://allenbrowne.com/NoYesNo.html

Thanks for the insight.
Have a great weekend!
:)

JeffCoachman
0
mds-cosAuthor Commented:
Thank you all for your comments.

A random comment about yes/no true/false and Access vs. SQL.  I do find it quite interesting that TRANSACT-SQL does not support a statement like SELECT * FROM employeestatus WHERE inoffice = 'yes'.  The same statement will however work in Access even as a pass-through query.  It was nice of M$ to consider doing at least a bit of translation before "passing through" to the back-end server.

For now I am giong to stick with Yes/No to make it easy for others to follow what I am doing (the data in question represents information that is truely "Yes / No" vs. "True / False").  When this project progresses to performance concernes I will be re-writing a lot of stuff, and certainly developing views and stored procs.  At that point yes/no logic will disappear as everything becomes SQL centric rather than Jet centric.

This is such a huge application (I truely have never seen anybody push Access to the extreeme like this before, and am a bit suprised to see Access handeling it so well) that my first step is to get the thing working with SQL as the back-end datastore.  After that I can start wading in to performance improvement.  As somebody noted, the minute differences between Access and TRANSACT-SQL can be tricky at times -- so it is better not to create a situation where I must figure out if bugs were created by the conversion vs. the performance re-writes.

Thanks again, everybody!
0
mds-cosAuthor Commented:
Just to round this out for future folks,  I gave Rick Fisher's 'Find and Replace' utility a whirl since it sounded like a rather useful tool.  It would not install on my system (I'm running Access from the Office XP Developers Edition with most of the Microsoft updates installed on a Windows XP Pro platform).

I Found a similar utility -- and free to boot.  MDBSearch by Ucora (also referred to as "ucora Find and Replace").  This will be a great addition to my bag of tools!
0
aikimarkCommented:
never refund points for abandoned questions.
0
mbizupCommented:
akimark,

> never refund points for abandoned questions.

Please read this (cleanup guidelines):
http://www.experts-exchange.com/help.jsp#hi131

I posted the PAQ/Refund recommendation, because the asker posted the solution used.  If you still have an objection with that after reading the guidelines I'm using,  let me know (but please be specific).

Thanks,
mbizup
EE cleanup Volunteer
0
aikimarkCommented:
@mbizup

Actually, jimhorn ought to be awarded the points since he suggested the utilities that seemed to get results.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.