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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|