• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

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!
0
mds-cos
Asked:
mds-cos
  • 5
  • 3
  • 2
  • +3
1 Solution
 
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
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now