ACC2000: How to Remove Spaces from Object Names By Using DAO (316975)

MORE INFORMATION

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

1. Open the Northwind Sample Database.
2. On the Tools menu, click Options.
3. On the General tab, make sure that the Track name AutoCorrect info and Perform name AutoCorrect check boxes are selected, and then click OK.
4. Under Objects, click Modules, and then click New.
5. Create a module. Type the following line in the Declarations section, if it is not already there:

   Option Explicit
   					

6. Type the following function:

    
      '============================================================
      ' The following function will:
      '   - Loop through the object collections in the database.
      '   - Call the Findspaces function to find spaces in the 
      '     object names if they exist.
      '============================================================
      Public Function ObjectsRename()
   
      On Error Resume Next
   
      Dim db As Database 'Database to import
      Dim td As TableDef 'Tabledefs in db
      Dim qd As QueryDef 'Querydefs in db
      Dim cntContainer As Container 'Containers in db
      Dim doc As Document 'Documents in db
      Dim strTDef As String 'Name of table or query rename
      Dim strCntName As String 'Document container name
      Dim x As Integer 'For looping
      Dim strDocName As String 'Name of document
      Dim intConst As Integer
      Dim strFName As String 'Name of field to rename
      Dim MyText As String
      
      'Set database object
   
      Set db = CurrentDb
      
      'Review tables and rename if necessary.
   
      For Each td In db.TableDefs
   
      strTDef = td.Name
   
      If Left(strTDef, 4) <> "MSys" Then
        MyText = FindSpaces(strTDef)
        If MyText <> strTDef Then
          DoCmd.Rename MyText, acTable, strTDef
        End If
      End If
      
   
      Next
   
      'Review queries.
   
      For Each qd In db.QueryDefs
   
      strTDef = qd.Name
      MyText = FindSpaces(strTDef)
      If MyText <> strTDef Then
       DoCmd.Rename MyText, acQuery, strTDef
      End If
   
      Next
   
      'Loop through containers and rename if necessary.
   
      For x = 1 To 4
   
      Select Case x
   
      Case 1
      strCntName = "Forms"
      intConst = acForm
   
      Case 2
      strCntName = "Reports"
      intConst = acReport
   
      Case 3
      strCntName = "Scripts"
      intConst = acMacro
   
      Case 4
      strCntName = "Modules"
      intConst = acModule
   
      End Select
   
      Set cntContainer = db.Containers(strCntName)
      
      For Each doc In cntContainer.Documents
   
        strDocName = doc.Name
        MyText = FindSpaces(strDocName)
        If MyText <> strDocName Then
         DoCmd.Rename MyText, intConst, strDocName
        End If
        
        
      Next doc
      Next x
   
      'Clean up variables to recover memory.
   
      Set td = Nothing
      Set qd = Nothing
      Set cntContainer = Nothing
      Set doc = Nothing
   
      db.Close
      Set db = Nothing
   
      End Function
   
   					

7. Save the module as RenameObjects.
8. Add another function to the module:

    
      '============================================================
      ' The following function will:
      '   - Find the Spaces in the object names.
      '   - Call ReplaceSpaces function to remove the spaces.
      '============================================================
   
      Public Function FindSpaces(WhichObject As String) As String
         Dim intCounter As Integer
         Dim strText As String
         Dim intStart As Integer
         
         intStart = 1
         intCounter = 1
         strText = WhichObject
         
         Do Until intCounter = 0
            ' Chr(32) is the Space character.
            intCounter = InStr(intStart, strText, Chr(32))
            intStart = intCounter + 1
            If intCounter > 0 And Not IsNull(intCounter) Then
               strText = ReplaceSpaces(intCounter, strText)
            End If
         Loop
         
         FindSpaces = strText
         
      End Function
   
      '==================================================================
      ' The following function is called from the FindSpaces() function. It
      ' accepts two arguments, intStart and strText. The function removes the
      ' spaces from the object name and returns the updated text.
      '==================================================================
   
      Public Function ReplaceSpaces(intStart As Integer, strText As String) As String
         ' Remove the space.
         strText = Left(strText, intStart - 1) & Right(strText, Len(strText) - intStart)
         ReplaceSpaces = strText
      End Function
   					

9. Save the module.
10. In your database, create a new macro that has the following properties:

       Action:  Run Code
       Function Name:  ObjectsRename ()
    						

    Then, save the macro as RenameObjects.
11. Create a new form in Design view, and save it as frmRename.
12. Make sure that the wizard is turned on, and then add a command button to the form.
13. Under Categories, click Miscellaneous.
14. Under Actions, click Run Macro, and then click Next.
15. In the list, click the RenameObjects macro that you created in step 10, and then click Finish.
16. Save the form.
17. Open the form in Form view and click the command button to run the code.
18. When the code is complete, any object names that previously had spaces are now displayed as a single word.

NOTE: If Name AutoCorrect is turned on and your database was created in Access 2000, Access picks up most of the object changes. However, you need to modify the code by changing any of the hard-coded values that refer to the previous object names.

For additional information about Name AutoCorrect, click the article number below to view the article in the Microsoft Knowledge Base: