Change Qry/Tbl Name Tool

[wallis1905]

Hello all,

Hope somebody can help with this. I am currently trying to tidy up a large messy database. I am standardising the naming convention for tables and queries etc, as they are a mess. I want to create a form with some code perhaps, that will allow me to change the name of a query etc, and automatically update the name change into any dependant queries, Reports, Lookups.
So far I have managed to do just this for Queries, but not the others. I can select an entity from a combo box, and enter its new name in a text box. I then find and replace all sql statements swaping the old name with the new name.
Anybody know how to drill into the rowsource property of a report, or lookup( in a table) and find replace that string?Preferably without opening each table,/Report to check rowsources. This must have been done before but cant seem to find it. Pretty useful tool if possible.
Any help is greatly appreciated.

 

[realvilla]

I would start with the QueryDef object. You can easily change the SQL property, which is the SQL string, by replacing the table/query name with a variable and concatenating it with the rest of the string. For example, you could have a string like so:

sSQL = "Select * from " & TableName

The combo box could be the mechanism to choose the table or query name from a list obtained from MSysObjects. Be sure and choose type 5 for query.

As for Dependencies, here's some code that might be helpful.

Public Function GetTableDependencies(objInput As String)

Dim db As Database, rs As DAO.Recordset
Dim ObjQuery As Access.AccessObject
Dim objAccessObject As Access.AccessObject
Dim objDependencyInfo As Access.DependencyInfo

' Assume you have a query named "Program Query"
' that depends on a table named "Program".
Set db = CurrentDb
Set rs = db.OpenRecordset("DEPENDENCIES")
Set ObjQuery = Application.CurrentData.AllTables.Item(objInput)

If ObjQuery.GetDependencyInfo.Dependants.Count = 0 Then

Debug.Print ObjQuery.Name & " has no dependencies."

Else

For Each objAccessObject In ObjQuery.GetDependencyInfo.Dependants
' Displays "Program Query depends on Program."
rs.AddNew
rs!ObjectName = ObjQuery.Name
rs!DatabaseName = db.Name
rs!SourceName = objAccessObject.Name
rs!RunDateTime = Now()
rs.Update

Next objAccessObject

End If

 

[wallis1905]

Ok thanks,
That code looks good I'll give it a try.

 

[wallis1905]

Sorry, can you tell me what this means?
Set rs = db.OpenRecordset("DEPENDENCIES")

You mean I should have a tabe called DEPENDENCIES?

Thanks a lot

 

[petehilljnr]

I'm pretty sure that is where the code is outputting the results to.

Create the table with the fields shown in the code, ie. ObjectName, DatabaseName, SourceName etc

Regards,
Pete.

 

[deBassMan]

Hi there

Access is good at quite a few things - but if you need to change object names - you are on your own.

This is because Access lacks some of the requisites of a true Object Oriented environment - Inheritance, polymorphism etc.

I believe there are third party tools that can help with regard to Inheritance.

good luck

 

[wallis1905]

This is because Access lacks some of the requisites of a true Object Oriented environment - Inheritance, polymorphism etc.

I believe there are third party tools that can help with regard to Inheritance.

Whoa, can you explain?
Is there really a problem with just finding and replacing an sql statement swaping the old name for a new name? I'm basically looking to automate all the things I normally must do when I change the name of a table.(ie Go to any query that looks at the table/query, fix that, go to any tables that might have lookups that are affected and update those, and finally find any reports that might need their recorsource updated)
Am I missing something here.