Changing/trimming query field names using VBA

[bini]

Is there a way to rename the fields in queries using vba. I am using the code below but get the error "Can't assign to read only property." at the point that I have highlighted.

The reason that I need to do this is because I am renaming all the fields in the tables to eliminate white spaces. I then need to run code to try and catch all these changes in as many queries, forms, reports and calculations as possible. The query part is beating me up right now.....



Function ChangeQueryFieldNames()
Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
Set db = CurrentDb
For Each qry In db.QueryDefs
For Each fld In qry.Fields
fld.SourceField = trim(fld.Sourcefield) '<<<<<<<<<<<
Next
Next
End Function

 

[boblarson]

Field names in queries are set via the SQL of the query. Why are you wanting to change the names?

Nevermind - I see why

 

[boblarson]

Oh, sorry, I didn't see the why part

Try using the FREE V-TOOLS Total Deep Search. It can do all of the renaming for you. Also, make sure that Name AutoCorrect (also known as AutoCorrupt) is turned off.

 

[Khalid_Afridi]

you can alias the query fields by prefixing the field name with the alias and ':' (colon) in the query grid or QBE for example MyLongFieldName to:
MyField: MyLongFieldName

But the question is why you want to rename the tables field to something else, if you do so, then your are messing up with the database for everything, because your fields might reffed to so many other fields having INNER, OUTER joins OR even referencing those fields in queries.
All your queries and functions will create errors and it will be difficult for your to resolve it.

Its better to analyse the database and re-structure it in steps.

 

[bini]

Thanks guys.
The reason for the terrible task is because I am converting the backend to mysql to have a webbased applicated. But I can't build the webbased app in a day so I am going to have to keep the access databse going for a while. The spaces in the fields in mysql have caused me so many problems that I finally decided I need to bite the bullet and trim them all.


I know that I'm going to hit problems with the Access database, but it seems like a slightly less nightmare than the tasks I have to keep repeating in the web app.

When I'm ready for the change over, probably on a friday night to give me two days to tweak before the busy Monday, I will run the following:

*A Procedure that splits 2 of the tables appropriately (tested)
*A Procedure that renames all the tables
*A Procedure that cleans all the table field names
*A Procedure that creates a query to replace the split tables
*Transfer the tables over to mysql
*A Procedure to delete all the tables from Access
*Link the tables from mysql to Access
*A Procedure to check all the fields in each report and change the name to the trimmed field
*A Procedure to check all the fields in each form and change the name to the trimmed field
*A Procedure to check all the fields in each query and change the name to the trimmed field (this one I don't have yet)
*Find/Replace field references in the modules

RUN!

Yes, it's been a nightmare, but I am so close.... I've been doing practice runs on copies of the database..... it's the query thing that I can't figure out. I may have to do that by hand . I guess I could use the code below to list all queries that have a field with a space and then hand change them in a copy of the database, and then when comes to the big day import all those queries. Just doing by hand adds more room to mess up....

 

[boblarson]

Because of the nature of the way you will have to change the queries' SQL, you will need to have a list of the original table names in order to replace the spaces in only those names and not in any of the rest of the SQL. So, to make it easy you may want to create a temp table of the names which you can then use the code to replace the spaces in the SQL of each using something like this (untested):

Function ReplTableNames()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strSQL As String
 
Set db = Currentdb
 
Set rst = db.OpenRecordset("YourTempTableWithTableNames")
 
For Each qdf In db.QueryDefs
   Do Until rst.EOF
        If Instr(1, qdf.SQL, rst(0)) > 0 Then    ' where rst(0) is the field which has the original table name in it
           qdf.SQL = Replace(qdf.SQL, rst(0), RepTableNameSpaces(rst(0)))
           qdf.Close
        End If
        rst.MoveNext
   Loop
    rst.MoveFirst
Next
 
rst.Close
Set rst = Nothing
Set qdf = Nothing

And here's the function I used in the code above:

Function RepTableNameSpaces(strOrigName As String) As String
        RepTableNameSpaces = Replace(strOrigName, " ", vbNullString)
End Function