A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Change all Names: objects, fields, code, etc.



 
 
Thread Tools Display Modes
  #1  
Old December 18th, 2009, 05:24 PM posted to microsoft.public.access.tablesdbdesign
Chris Moore[_2_]
external usenet poster
 
Posts: 23
Default Change all Names: objects, fields, code, etc.

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
  #2  
Old December 18th, 2009, 07:38 PM posted to microsoft.public.access.tablesdbdesign
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Change all Names: objects, fields, code, etc.

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" wrote in message
...
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



  #3  
Old December 18th, 2009, 09:01 PM posted to microsoft.public.access.tablesdbdesign
Chris Moore[_2_]
external usenet poster
 
Posts: 23
Default Change all Names: objects, fields, code, etc.

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" wrote in message
...
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



.

  #4  
Old December 18th, 2009, 09:28 PM posted to microsoft.public.access.tablesdbdesign
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Change all Names: objects, fields, code, etc.

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" wrote in message
...
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" wrote in message
...
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



.



  #5  
Old December 18th, 2009, 10:07 PM posted to microsoft.public.access.tablesdbdesign
Chris Moore[_2_]
external usenet poster
 
Posts: 23
Default Change all Names: objects, fields, code, etc.

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" wrote in message
...
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" wrote in message
...
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


.



.

  #6  
Old December 19th, 2009, 12:20 AM posted to microsoft.public.access.tablesdbdesign
Larry Daugherty
external usenet poster
 
Posts: 1,012
Default Change all Names: objects, fields, code, etc.

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 a

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" wrote in message
...
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



  #7  
Old December 19th, 2009, 09:51 AM posted to microsoft.public.access.tablesdbdesign
.Len B
external usenet poster
 
Posts: 81
Default Change all Names: objects, fields, code, etc.

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" wrote in message
...
| 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



  #8  
Old December 22nd, 2009, 05:21 PM posted to microsoft.public.access.tablesdbdesign
Chris Moore[_2_]
external usenet poster
 
Posts: 23
Default Change all Names: objects, fields, code, etc.

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" wrote in message
...
| 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



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.