Is Name Autocorrect fixed in Access 2003?

[Jim M]

Just got my copy of Access 2003. Is the 'Name Autocorrect' feature in
Access 2003 fixed yet, or is it still 'Name Autocorrupt', as many in
this newsgroup reported in the past.

I would like to finally clean up poor field names I put in my
application in 1994 (when I was young and foolish). For example, I used
"Date" as a name in eight different tables. I need to fix this. The
"Autocorrect" feature sounds like it is designed to make global changes
based on dependencies and relationships... That is just what I need.
The tangled web I need to fix feels too complex for a simple search and
replace engine (Speed Ferret found 450 uses of the field "Date" in my
queries alone.) I would like to make the changes globally, but limit
them using dependencies.

Is it worthwhile to try this - or is Autocorrect still terrible...

Thanks in advanve for you advice

[Allen Browne]

Although MS fixed a couple of the issues, Name AutoCorrect is still a very
good way to corrupt your database in A2003.

The feature does not perform the rename anyway, so it can't do what you ask.
There is a new feature in A2003 which traces dependencies. I tried that once
in a database where we knew the queries referred to tables that no longer
existed, hoping to trace the broken dependencies, so we tried some code that
used DependencyObjects, DependencyInfo etc. However, we never did manage to
get the code to run to completion. A2003 constantly crashed (shut down by
Windows), so we eventually gave up on this approach as unworkable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim M" <ma*****@rci.rutgers.edu> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

Just got my copy of Access 2003. Is the 'Name Autocorrect' feature in
Access 2003 fixed yet, or is it still 'Name Autocorrupt', as many in
this newsgroup reported in the past.

I would like to finally clean up poor field names I put in my
application in 1994 (when I was young and foolish). For example, I used
"Date" as a name in eight different tables. I need to fix this. The
"Autocorrect" feature sounds like it is designed to make global changes
based on dependencies and relationships... That is just what I need.
The tangled web I need to fix feels too complex for a simple search and
replace engine (Speed Ferret found 450 uses of the field "Date" in my
queries alone.) I would like to make the changes globally, but limit
them using dependencies.

Is it worthwhile to try this - or is Autocorrect still terrible...

Thanks in advanve for you advice

[Jim M]

Thanks muchly for the warning. I read the MS literature, and was ready
to give it a try. It mostly listed limitations of Autocorrect in 2003:
I.e It won't work for Microsoft Access projects, replicated databases;
Pass-through, data-definition, and union queries; Data access pages,
macros, and modules, or objects from linked tables. It does not warn
about data corruption... If you won't use it, then I won't dare to try.

I thought I could use to to cut down on some of my 450 uses of 'Date'
in my queries, and then run Speed Ferret on the rest. Speed Ferret does
not pay attention to dependencies, only names and properties. Are there
any other third party or shareware/freeware products that traces and
renames dependencies?

By the way, I have found the code samples and examples from your web
site tremendously helpful over the years - I appreciate you taking the
time to help the rest of us, especially people like myself who have a
lot to learn.

Regards,
Jim Mandala

[Allen Browne]

Perhaps someone else can suggest other dependency tracers.
I've not had occassion to test them.

It may be possible to trace some of these dependencies yourself.
For example, this query shows which queries depend on other queries:
SELECT MSysObjects.Name FROM MSysQueries INNER JOIN
MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE MSysQueries.Expression Like "*" & [qryName] & "*"
GROUP BY MSysObjects.Name;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim M" <ma*****@rci.rutgers.edu> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

Thanks muchly for the warning. I read the MS literature, and was ready
to give it a try. It mostly listed limitations of Autocorrect in 2003:
I.e It won't work for Microsoft Access projects, replicated databases;
Pass-through, data-definition, and union queries; Data access pages,
macros, and modules, or objects from linked tables. It does not warn
about data corruption... If you won't use it, then I won't dare to try.

I thought I could use to to cut down on some of my 450 uses of 'Date'
in my queries, and then run Speed Ferret on the rest. Speed Ferret does
not pay attention to dependencies, only names and properties. Are there
any other third party or shareware/freeware products that traces and
renames dependencies?

By the way, I have found the code samples and examples from your web
site tremendously helpful over the years - I appreciate you taking the
time to help the rest of us, especially people like myself who have a
lot to learn.

Regards,
Jim Mandala

[downwitch]

This code traces dependencies of any string search in a query, and
prints them out nicely for you in a tree. I haven't gotten around to
writing the replace version of it, mainly because I usually find I like
to watch. It's not much of a leap, though, from find to replace, as
the little ReplaceMe bit should show you.

*** CODE START ***
Public Function uFindStringInAllQries(pstrFind As String, _
Optional pblePrintToScreenNow As Boolean =
True, _
Optional pbleRecurse As Boolean = True, _
Optional pbytRecursionLevel As Byte = 0)
As String
On Error GoTo HandleErrors

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Dim i As Integer
Dim strSQL As String
Dim strFound As String
Dim strDebug As String

Dim bleForQry As Boolean

Const DBL_QT As String = """"

Set db = CurrentDb
db.QueryDefs.Refresh

If pbleRecurse = True And pbytRecursionLevel = 0 Then
If Right(pstrFind, 1) = "." Then
bleForQry = IsQuery(ReplaceMe(pstrFind, ".", ""))
Else
bleForQry = IsQuery(pstrFind)
End If
End If
If bleForQry Then pbytRecursionLevel = 1

For Each qdf In db.QueryDefs
If pstrFind <> qdf.Name Then
strSQL = qdf.SQL
strSQL = ReplaceMe(strSQL, "[", "")
strSQL = ReplaceMe(strSQL, "]", "")

i = InStr(1, strSQL, pstrFind, vbTextCompare)
If i > 0 Then
strFound = strFound & vbNewLine & Space(4 *
pbytRecursionLevel) & qdf.Name
If pbleRecurse = True Then
strFound = strFound & vbNewLine & strFound &
uFindStringInAllQries(pstrFind:=qdf.Name & ".", pbleRecurse:=True,
pbytRecursionLevel:=pbytRecursionLevel + 1)
End If
End If
End If
Next qdf

If pblePrintToScreenNow = True And (pbytRecursionLevel = 0 Or
bleForQry = True) Then
If Len(strFound) > 0 Then
strDebug = "The following queries contain the test value "
& DBL_QT & pstrFind & DBL_QT & "." & vbNewLine & strFound
Else
strDebug = DBL_QT & pstrFind & DBL_QT & " not found in this
file's queries."
End If

Debug.Print strDebug
End If

If bleForQry = True And Len(strFound) > 0 Then strFound = pstrFind
& vbNewLine & strFound

uFindStringInAllQries = strFound

ExitHere:
Set qdf = Nothing
Set db = Nothing
Exit Function

HandleErrors:
'etc.
Resume ExitHere

End Function

Public Function ReplaceMe(ByVal varValue As Variant, _
strFind As String, _
strReplace As String) As Variant

Dim intLenFind As Integer
Dim intLenReplace As Integer
Dim intPos As Integer

If IsNull(varValue) Then
ReplaceMe = Null
Else
intLenFind = Len(strFind)
intLenReplace = Len(strReplace)

If Len(strFind) > 0 Then
intPos = 1
Do
intPos = InStr(intPos, varValue, strFind)
If intPos > 0 Then
varValue = Left(varValue, intPos - 1) & strReplace
& Mid(varValue, intPos + intLenFind)
intPos = intPos + intLenReplace
End If
Loop Until intPos = 0
End If
End If

ReplaceMe = varValue
End Function

Public Function IsQuery(pstrQryNmOrSQL As String) As Boolean
On Error Resume Next

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs(pstrQryNmOrSQL)
IsQuery = (Err = 0)

ExitHere:
Set qdf = Nothing
Exit Function

End Function
*** CODE END ***