Question Code to Remove Spaces from Table Names

[ApexJim]

Hello All Access Members - Can anyone help me please?
We use a Database which has been constantly developed over 10 years using earlier versions of Access2003.
We have Upgraded to Access 2013 and we are experiencing many conversion issues which I need to resolve.

One key problem is that many of the earlier tables were developed with spaces in their names (Hindsight is a wonderful thing?).
There are over 200 Tables that need changing.
I would like to update the Table names replacing every "space" with an underscore"_".
AT the same time I would also need to Update all of the QUERIES that use these particular Tables to be updated to reflect these changes made and still work as normal.

I can do this manually but it will be very time consuming and perhaps someone has already had to do this when recently upgrading to Access 2013?

Can anyone guide me into developing VB code to achieve this please?

Kind Regards ApexJim

 

[bob fitz]

You will need to make these changes to any VBA code that references the tables as well.

 

[pr2-eugin]

Well this code, in theory should work.

Public Sub ReNameTablesNQueries()
    Dim tbD As TableDef, oldTblName As String, newTblName As String
    Dim qdObj As DAO.QueryDef
    
    For Each tbD In CurrentDb.TableDefs
        If InStr(tbD.Name, " ") <> 0 Then
            oldTblName = tbD.Name 
            newTblName = Replace(tbD.Name, " ", "_")
            For Each qdObj In CurrentDb.QueryDefs
                If InStr(qdObj.SQL, oldTblName) <> 0 Then
                    qdObj.SQL = Replace(qdObj.SQL, oldTblName, newTblName)
                End If
            Next
            tbD.Name = newTblName
        End If
    Next
End Sub

I would suggest you first play with a mock/sample file before doing it in the main/live file.

EDIT: +1 on Bob's concern. That is going to be a tricky one.

 

[spikepl]

With 200 tables I'd advise you to sit on this renaming urge until it goes away.

Leave this thing as is - if it works now, making sure that ieverything works after renaming will be a struggle. And at the end of the day you have exactly what you started off with, functionality-wise. Who has the budget and time to spend on nothing?

Note that renaming is not automatic in VBA code ... you can end up with a bricked db!

 

[Galaxiom]

You say you are already having problems. You would be better off finding those problems before you change the names. I very much doubt the spaces in names would be the issue or they would have been a problem before.

Otherwise you could be introducing another problem that will make it harder to recognise the issues caused by the version change.

Better to leave the names alone but if you really want to do it, get V-tools and use the Replace function in the Total Deep Search. It handles the VBA too.

Search for each table name separately and do the replace.

 

[gemma-the-husky]

as well as changing code and queries, you also need to change record sources of forms and reports, maybe linkages between forms and subforms.

doable, but hardly worth the effort, I would have thought.

what about the possibility that the table name without the space is already used as an identifier, and renaming the table causes problem because of subsequent ambiguity.

 

[smig]

You say you are already having problems. You would be better off finding those problems before you change the names. I very much doubt the spaces in names would be the issue or they would have been a problem before.

Otherwise you could be introducing another problem that will make it harder to recognise the issues caused by the version change.

Better to leave the names alone but if you really want to do it, get V-tools and use the Replace function in the Total Deep Search. It handles the VBA too.

Search for each table name separately and do the replace.

+1
V-Tool is a must

 

[CJ_London]

not to mention the dreaded lookups if used in table design

 

[ApexJim]

Hello to all members that have given me advice.

All comments and information you have given is both understood and very much appreciated.

I will try the different suggestions to learn more on the subject but I understand that if it is not broken it does not need fixing.

The reason I thought this might help is because the Shared database on the network bloats by nearly 40% each day and eventually runs very slow and says "Updating Tables" on the users PC's.

The only remedy so far is to remove everyone off the network and compact and repair and then everything is fine again

I have searched and took advice on this, all Tables Subdatsheet Names are set to None, Auto correct is off but I have not made much progress and I thought it may be the Table name spaces that may be adding to the problems being experienced.

Thank you to everyone and I will continue to learn more about Access 2013 and hopefully get the Database Stable in due course

Kind Regards
ApexJim

 

[CJ_London]

the Shared database on the network

Presumably your db is split and this is the backend.

So far as I am aware, spaces in table names do not cause bloat - this may be due to a) db not split frontend/backend, b) use of OLE Objects in tables and/or c) excessive use of delete/make tables