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

View products that this article applies to.

This article was previously published under Q316975

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top

Summary

This article describes how to use Microsoft Data Access Objects (DAO) to remove spaces from object names in a Microsoft Access database.

↑ Back to the top

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.

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

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

Save the module as RenameObjects.

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

Save the module.
In your database, create a new macro that has the following properties:
```
   Action:  Run Code
   Function Name:  ObjectsRename ()
						
```
Then, save the macro as RenameObjects.
Create a new form in Design view, and save it as frmRename.
Make sure that the wizard is turned on, and then add a command button to the form.
Under Categories, click Miscellaneous.
Under Actions, click Run Macro, and then click Next.
In the list, click the RenameObjects macro that you created in step 10, and then click Finish.
Save the form.
Open the form in Form view and click the command button to run the code.
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:

231745� ACC2000: How Name AutoCorrect Works and What It Repairs

↑ Back to the top