Microsoft KB Archive/316975

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

Article ID: 316975

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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).



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



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) <> &quot;MSys&quot; 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 = &quot;Forms&quot; intConst = acForm

Case 2 strCntName = &quot;Reports&quot; intConst = acReport

Case 3 strCntName = &quot;Scripts&quot; intConst = acMacro

Case 4 strCntName = &quot;Modules&quot; 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

</li> Save the module as RenameObjects .</li>  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 </li> Save the module.</li>  In your database, create a new macro that has the following properties: <pre class="fixed_text">  Action:  Run Code Function Name: ObjectsRename Then, save the macro as RenameObjects. </li> Create a new form in Design view, and save it as frmRename .</li> Make sure that the wizard is turned on, and then add a command button to the form.</li> Under Categories, click Miscellaneous.</li> Under Actions, click Run Macro, and then click Next.</li> In the list, click the RenameObjects macro that you created in step 10, and then click Finish.</li> Save the form.</li> Open the form in Form view and click the command button to run the code.</li> When the code is complete, any object names that previously had spaces are now displayed as a single word.</li></ol>

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

<div class="references_section">