CODEWITHSUNDEEP
ms-accessvba
Paul
Paul

Reputation: 266

Programmatically rename columns in access

I'm trying to programmatically rename all columns in a database to replace spaces with underscores using the following macro:

Option Compare Database

  Public Sub RenameFields(ByRef tdf As DAO.TableDef)
    Dim fld As DAO.Field
    Dim strFieldName As String
    Dim strNewName As String

    Debug.Print "==============================================" & vbCrLf & UCase(tdf.Name)
    For Each fld In tdf.Fields
      strFieldName = fld.Name
      strNewName = Replace(strFieldName, " ", "_")
      If strFieldName <> strNewName Then
         fld.Name = strNewName 'ERROR IS THROWN HERE
         Debug.Print tdf.Name & "." & strFieldName & "=>" & strNewName
      End If
    Next fld
    Set fld = Nothing
  End Sub

Public Sub DoRename()
  Dim tdf As DAO.TableDef

  For Each tdf In CurrentDb.TableDefs
       Call RenameFields(tdf)
  Next tdf
  Set tdf = Nothing
End Sub

When I run DoRename(), I get the following error:

Run-time error '3033':

You do not have the necessary permissions to use the 'Object Type Group' object. Have you system administrator or the person who created this object establish the appropriate permissions for you.

I have enabled all permissions on the database, including "Administer" for all Object Types (Database, Table, Query, Form, Report, Macro).

Note that this error is thrown on the following line of code:

fld.Name = strNewName

How can I fix this?

Upvotes: 2

Views: 1284

Answers (2)

HansUp
HansUp

Reputation: 97101

You should not attempt to rename system table fields. Make sure to skip those tables whose names start with "MSys".

If there are any deleted tables still included in the TableDefs collection, don't bother renaming their fields either. A deleted table's name is prefixed with ~. And a deleted table will be completely removed eventually, but still no need to rename its fields when present.

If Not (tdf.Name Like "Msys*" Or tdf.Name Like "~*") Then
    Call RenameFields(tdf)
End If

Upvotes: 1

Paul
Paul

Reputation: 266

The issue was that the macro was trying to rename system tables. To solve the problem, I rewrote the DoRename() function as follows:

Public Sub DoRename()
  Dim tdf As DAO.TableDef

  For Each tdf In CurrentDb.TableDefs
       If InStr(tdf.Name, "MSys") = 0 Then
              Call RenameFields(tdf)
       End If
  Next tdf
  Set tdf = Nothing
End Sub

Upvotes: 1

Related Questions