Access 02 sp3 on XP sp2.

So, I have inherited a access database from my predesessor that gathers and
reports financial data for the previous year and current year-to-date. As a
result, many of the table names and column headers have names specifically
referencing "2008" and "2009". I now need to modify this database for the
approaching new year. Everything that says "2008" will need to be changed to
"2009" and everything that says "2009" will need to be changed to "2010".
Unfortunately this also means that all the queries, forms and modules that
reference these names also need to be updated. In short it's a mess.

Is there any way to globally search a database for any and all references to
a particular value and replace that value with another? It's all just code
after all, right?

Thanks in advance for any help offered.

-Chris

If you've got tables and fields with dates in their names, your database
isn't properly designed. You've got metadata buried in the names, and that's
a bad idea (it also makes querying far more difficult)

However, in answer to your specific question, take a look at Find & Replace:
http://www.rickworld.com/products.html or Speed Ferret:
http://www.moshannon.com/speedferret.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Chris Moore" <(E-Mail Removed)> wrote in message
news:67FEE52B-145E-47F8-ADBE-(E-Mail Removed)...
> Access 02 sp3 on XP sp2.
>
> So, I have inherited a access database from my predesessor that gathers
> and
> reports financial data for the previous year and current year-to-date. As
> a
> result, many of the table names and column headers have names specifically
> referencing "2008" and "2009". I now need to modify this database for the
> approaching new year. Everything that says "2008" will need to be changed
> to
> "2009" and everything that says "2009" will need to be changed to "2010".
> Unfortunately this also means that all the queries, forms and modules that
> reference these names also need to be updated. In short it's a mess.
>
> Is there any way to globally search a database for any and all references
> to
> a particular value and replace that value with another? It's all just code
> after all, right?
>
> Thanks in advance for any help offered.
>
> -Chris

Thanks Douglas. Unfortunately downloading unapproved third party apps is
against the corporate policy so neither of these utilities is an option for
me. I thought maybe there would be a way to do this by querying the system
tables. I took a quick look but I'm not seeing everything I would expect to
see if that were possible. If you have any other ideas please let me know.

-Chris

"Douglas J. Steele" wrote:

> If you've got tables and fields with dates in their names, your database
> isn't properly designed. You've got metadata buried in the names, and that's
> a bad idea (it also makes querying far more difficult)
>
> However, in answer to your specific question, take a look at Find & Replace:
> http://www.rickworld.com/products.html or Speed Ferret:
> http://www.moshannon.com/speedferret.html
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Chris Moore" <(E-Mail Removed)> wrote in message
> news:67FEE52B-145E-47F8-ADBE-(E-Mail Removed)...
> > Access 02 sp3 on XP sp2.
> >
> > So, I have inherited a access database from my predesessor that gathers
> > and
> > reports financial data for the previous year and current year-to-date. As
> > a
> > result, many of the table names and column headers have names specifically
> > referencing "2008" and "2009". I now need to modify this database for the
> > approaching new year. Everything that says "2008" will need to be changed
> > to
> > "2009" and everything that says "2009" will need to be changed to "2010".
> > Unfortunately this also means that all the queries, forms and modules that
> > reference these names also need to be updated. In short it's a mess.
> >
> > Is there any way to globally search a database for any and all references
> > to
> > a particular value and replace that value with another? It's all just code
> > after all, right?
> >
> > Thanks in advance for any help offered.
> >
> > -Chris
>
>
> .
>

It's definitely not possible using the system tables. Not only are field
names not included there, but the system tables aren't updatable through
SQL.

If you're simply look for table and field names, you can use code like:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
If InStr(tdfCurr.Name, "2008") > 1 Then
tdfCurr.Name = Replace(tdfCurr.Name, "2008", "2009")
End If
For Each fldCurr In tdfCurr.Fields
If InsStr(fldCurr.Name, "2008") > 1 Then
fldCurr.Name = Replace(fldCurr.Name, "2008", "2009")
End If
Next fldCurr
End If
Next tdfCurr

Of course, all your queries and VBA code will now be incorrect, as well as
the forms and reports that used the old tables.

In the long run, you'd be far better off getting approval to download a
proper tool. (Well, actually you'd be far, far better off redesigning the
application correctly!)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Chris Moore" <(E-Mail Removed)> wrote in message
news:2F302310-3B7F-49E8-B289-(E-Mail Removed)...
> Thanks Douglas. Unfortunately downloading unapproved third party apps is
> against the corporate policy so neither of these utilities is an option
> for
> me. I thought maybe there would be a way to do this by querying the system
> tables. I took a quick look but I'm not seeing everything I would expect
> to
> see if that were possible. If you have any other ideas please let me know.
>
> -Chris
>
> "Douglas J. Steele" wrote:
>
>> If you've got tables and fields with dates in their names, your database
>> isn't properly designed. You've got metadata buried in the names, and
>> that's
>> a bad idea (it also makes querying far more difficult)
>>
>> However, in answer to your specific question, take a look at Find &
>> Replace:
>> http://www.rickworld.com/products.html or Speed Ferret:
>> http://www.moshannon.com/speedferret.html
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Chris Moore" <(E-Mail Removed)> wrote in message
>> news:67FEE52B-145E-47F8-ADBE-(E-Mail Removed)...
>> > Access 02 sp3 on XP sp2.
>> >
>> > So, I have inherited a access database from my predesessor that gathers
>> > and
>> > reports financial data for the previous year and current year-to-date.
>> > As
>> > a
>> > result, many of the table names and column headers have names
>> > specifically
>> > referencing "2008" and "2009". I now need to modify this database for
>> > the
>> > approaching new year. Everything that says "2008" will need to be
>> > changed
>> > to
>> > "2009" and everything that says "2009" will need to be changed to
>> > "2010".
>> > Unfortunately this also means that all the queries, forms and modules
>> > that
>> > reference these names also need to be updated. In short it's a mess.
>> >
>> > Is there any way to globally search a database for any and all
>> > references
>> > to
>> > a particular value and replace that value with another? It's all just
>> > code
>> > after all, right?
>> >
>> > Thanks in advance for any help offered.
>> >
>> > -Chris
>>
>>
>> .
>>

Damn, no easy answer. Which I figured was probably the case. Thanks again for
your input Doug.

"Douglas J. Steele" wrote:

> It's definitely not possible using the system tables. Not only are field
> names not included there, but the system tables aren't updatable through
> SQL.
>
> If you're simply look for table and field names, you can use code like:
>
> Dim dbCurr As DAO.Database
> Dim tdfCurr As DAO.TableDef
> Dim fldCurr As DAO.Field
>
> Set dbCurr = CurrentDb()
> For Each tdfCurr In dbCurr.TableDefs
> If (tdfCurr.Attributes And dbSystemObject) = 0 Then
> If InStr(tdfCurr.Name, "2008") > 1 Then
> tdfCurr.Name = Replace(tdfCurr.Name, "2008", "2009")
> End If
> For Each fldCurr In tdfCurr.Fields
> If InsStr(fldCurr.Name, "2008") > 1 Then
> fldCurr.Name = Replace(fldCurr.Name, "2008", "2009")
> End If
> Next fldCurr
> End If
> Next tdfCurr
>
> Of course, all your queries and VBA code will now be incorrect, as well as
> the forms and reports that used the old tables.
>
> In the long run, you'd be far better off getting approval to download a
> proper tool. (Well, actually you'd be far, far better off redesigning the
> application correctly!)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Chris Moore" <(E-Mail Removed)> wrote in message
> news:2F302310-3B7F-49E8-B289-(E-Mail Removed)...
> > Thanks Douglas. Unfortunately downloading unapproved third party apps is
> > against the corporate policy so neither of these utilities is an option
> > for
> > me. I thought maybe there would be a way to do this by querying the system
> > tables. I took a quick look but I'm not seeing everything I would expect
> > to
> > see if that were possible. If you have any other ideas please let me know.
> >
> > -Chris
> >
> > "Douglas J. Steele" wrote:
> >
> >> If you've got tables and fields with dates in their names, your database
> >> isn't properly designed. You've got metadata buried in the names, and
> >> that's
> >> a bad idea (it also makes querying far more difficult)
> >>
> >> However, in answer to your specific question, take a look at Find &
> >> Replace:
> >> http://www.rickworld.com/products.html or Speed Ferret:
> >> http://www.moshannon.com/speedferret.html
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "Chris Moore" <(E-Mail Removed)> wrote in message
> >> news:67FEE52B-145E-47F8-ADBE-(E-Mail Removed)...
> >> > Access 02 sp3 on XP sp2.
> >> >
> >> > So, I have inherited a access database from my predesessor that gathers
> >> > and
> >> > reports financial data for the previous year and current year-to-date.
> >> > As
> >> > a
> >> > result, many of the table names and column headers have names
> >> > specifically
> >> > referencing "2008" and "2009". I now need to modify this database for
> >> > the
> >> > approaching new year. Everything that says "2008" will need to be
> >> > changed
> >> > to
> >> > "2009" and everything that says "2009" will need to be changed to
> >> > "2010".
> >> > Unfortunately this also means that all the queries, forms and modules
> >> > that
> >> > reference these names also need to be updated. In short it's a mess.
> >> >
> >> > Is there any way to globally search a database for any and all
> >> > references
> >> > to
> >> > a particular value and replace that value with another? It's all just
> >> > code
> >> > after all, right?
> >> >
> >> > Thanks in advance for any help offered.
> >> >
> >> > -Chris
> >>
> >>
> >> .
> >>
>
>
> .
>

To get you over the current hurdle:

get a copy of FindAndReplace from Rick Fisher at www.rickworld.com
It's shareware and is specific to Access versions. It will allow you
to fint and replace the names of any objects in your database
structure such as the mis-named tables, etc. Remember to rename the
highest dates first so that you don't collide names.

What you really need to do is to re-do the structure of your orjects
such that you are never using the name of an object to store data.
Where dates are involved, use a datetime datatype and put the actual
date of the event being recorded and tracked into that column.. There
are ample date and time functions to enable handy resolution of just
about anything you want to know about date values and relationships.
It may take a bit of thinking and adjustment to get it right and the
changes will percolate upward through queries, forms and reports.
Once done, there should never be a need to modify the structure simply
to accommodate a new year. The work arises from someone's ignorance
in doing it the wrong way around in the first place. In othe words,
it took more work to do it that way than it would have taken to have
found out the right ways to get the job done in the first place.
There is a lot of help in the Help file and there are always these
newsgroups.


A couple of newsgroups I always recommend for Access newbies are:

microsoft.public.gettingstarted
microsoft.public.tablesdesign

A list of priceless Access resources I cribbed from the frequent posts
of John Vinson is below

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


HTH
--
-Larry-
--

"Chris Moore" <(E-Mail Removed)> wrote in message
news:67FEE52B-145E-47F8-ADBE-(E-Mail Removed)...
> Access 02 sp3 on XP sp2.
>
> So, I have inherited a access database from my predesessor that
gathers and
> reports financial data for the previous year and current
year-to-date. As a
> result, many of the table names and column headers have names
specifically
> referencing "2008" and "2009". I now need to modify this database
for the
> approaching new year. Everything that says "2008" will need to be
changed to
> "2009" and everything that says "2009" will need to be changed to
"2010".
> Unfortunately this also means that all the queries, forms and
modules that
> reference these names also need to be updated. In short it's a mess.
>
> Is there any way to globally search a database for any and all
references to
> a particular value and replace that value with another? It's all
just code
> after all, right?
>
> Thanks in advance for any help offered.
>
> -Chris

Whoa!! If you rename a table named x2009 containing data for 2009 to
x2010 and you also
rename the fields do you not now have 2009 data in a 2010 table with 2010
field names?

Apart from a redesign, wouldn't it be just a case of adding new set of
tables for 2010 and
modifying the queries or making new ones. Then everything still works or
its just a matter
of changing the RecordSource of forms and reports?
--
Len
______________________________________________________
remove nothing for valid email address.
"Chris Moore" <(E-Mail Removed)> wrote in message
news:67FEE52B-145E-47F8-ADBE-(E-Mail Removed)...
| Access 02 sp3 on XP sp2.
|
| So, I have inherited a access database from my predesessor that gathers
and
| reports financial data for the previous year and current year-to-date.
As a
| result, many of the table names and column headers have names
specifically
| referencing "2008" and "2009". I now need to modify this database for
the
| approaching new year. Everything that says "2008" will need to be
changed to
| "2009" and everything that says "2009" will need to be changed to
"2010".
| Unfortunately this also means that all the queries, forms and modules
that
| reference these names also need to be updated. In short it's a mess.
|
| Is there any way to globally search a database for any and all
references to
| a particular value and replace that value with another? It's all just
code
| after all, right?
|
| Thanks in advance for any help offered.
|
| -Chris

Thanks everyone for the comments. I'm currently in the process of manually
making all the necessary design changes so this won't have to be done again
in the future. The only trick I thought of to make the job a little easier
was to use the documenter to output all the SQL so I can search the document
to see if there are any references that I missed.

".Len B" wrote:

> Whoa!! If you rename a table named x2009 containing data for 2009 to
> x2010 and you also
> rename the fields do you not now have 2009 data in a 2010 table with 2010
> field names?
>
> Apart from a redesign, wouldn't it be just a case of adding new set of
> tables for 2010 and
> modifying the queries or making new ones. Then everything still works or
> its just a matter
> of changing the RecordSource of forms and reports?
> --
> Len
> ______________________________________________________
> remove nothing for valid email address.
> "Chris Moore" <(E-Mail Removed)> wrote in message
> news:67FEE52B-145E-47F8-ADBE-(E-Mail Removed)...
> | Access 02 sp3 on XP sp2.
> |
> | So, I have inherited a access database from my predesessor that gathers
> and
> | reports financial data for the previous year and current year-to-date.
> As a
> | result, many of the table names and column headers have names
> specifically
> | referencing "2008" and "2009". I now need to modify this database for
> the
> | approaching new year. Everything that says "2008" will need to be
> changed to
> | "2009" and everything that says "2009" will need to be changed to
> "2010".
> | Unfortunately this also means that all the queries, forms and modules
> that
> | reference these names also need to be updated. In short it's a mess.
> |
> | Is there any way to globally search a database for any and all
> references to
> | a particular value and replace that value with another? It's all just
> code
> | after all, right?
> |
> | Thanks in advance for any help offered.
> |
> | -Chris
>
>
>
> .
>