Changing table field names and affecting all queries, forms, reports

[MJD1]

Hi, I'm working on a project where a database was created in MS access and is split BE and FE. We are now moving the BE to Oracle and have determined that the current backend is using reserved words in certain table field names. Is there a way via code to change the field names automatically and also have it change the related queries, forms, reports and macros? or am I looking at going through the entire Front end manually to make the changes?

any help is appreciated!!

martin

 

[MajP]

Yes you can do this in code. I will see if I have any code that does this. You can probably google this and find some code. If I was rolling a procedure from scratch it would be
like below. I would likely have a validation that shows the change and lets you verify before changing.

1) Make a table with three fields
tblChangeName
nameTable
oldFieldName
newFieldName

2)Loop the query defs
loop tblChangeName
if you find a candidate change
msgbox show the sql string and prompt do you want
to change
if yes replace(sqlString,oldFieldName,newFieldName)
end if
next tblChange record
loop

3)use currentproject.allforms to loop the forms and check the recordsource
open theform in design view and hidden
same find and replace as step 2
close form and save
4) Although this could be combined in 3 I would do it seperate so as to make it a little easier to error check

Same as 3 loop all forms
loop the controls collection
check to see if the .controlType = acListBox or acCombobox
check to see if the rowsourcetype = Table/query
same check as step 2

5) Loop all modules
loop the procedures
same check as step 2


If someone does not provide you a better solution and you are interested in going this route I can provide most of the code.

 

[MJD1]

Hi, thanks for your quick reponse. If you could provide the code it would b great.

Thanks again!

 

[MajP]

Hopefully someone can post a complete solution. I would also continue Googling to see if you can find a complete solution. I am sure someone has coded this before.
I have most of the code to do parts of this, I will just have to find it and wrap it up. I am little busy today, but should have you something workable by tomorrow.

 

[PHV]

Why not simply use the native tracking change ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I have never had a lot of success with setting the Name Autocorrect after the fact, but that may do it especially with the newer version of Access. You may want to look at that first and see if it is successful:

http://msdn.microsoft.com/en-us/library/aa139941(office.10).aspx

 

[dhookom]

I would use one of the Scan and Replace tools listed at Tony Toew's site.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Duane,
Have you used any of them? Any recommendations? The first one appears to provide a 30 day demo period.

 

[dhookom]

I used Rick Fischer's Find and Replace many years ago and it worked well. I have continued to hear good comments about it. Make sure you check the tool with the version of Access you are running since they don't all work with all versions.

Duane
Hook'D on Access
MS Access MVP

 

[boblarson]

For free you can use V-Tools which has Total Deep Search (it can search and replace).

http://www.skrol29.com/us/vtools.php

I like Rick Fisher's Find and Replace as well and use it at work.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[johnbward1939]

Thanks. Just tried that with a new text box with a unique name, still the same error message

 

[boblarson]

Thanks. Just tried that with a new text box with a unique name, still the same error message

What error message? (also make sure that NAME AUTOCORRUPT - I mean AutoCorrect - is turned OFF)

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[dhookom]

I think johnbward1939 meant this reply for a different thread. I don't believe we can blame autocorrupt for this mis-post.

Duane
Hook'D on Access
MS Access MVP

 

[boblarson]

I think johnbward1939 meant this reply for a different thread. I don't believe we can blame autocorrupt for this mis-post.

Now that was funny. No wonder I was a bit confused at first with the reply.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[MJD1]

Thanks everyone for all the suggestions. Gives me a few options to go with which I should be ready to try next week.