ACC97: No Error When AddFromFile Creates Duplicate Procedure

• Microsoft Access 97

SYMPTOMS

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

The AddFromFile method does not generate an error when it adds a duplicate procedure to a module.

CAUSE

When the AddFromFile method inserts a procedure into a module, Visual Basic for Applications tries to compile all code in the target module first, and then inserts the procedure if successful.

If you use the AddFromFile method multiple times to insert the same procedure into the same target module, Visual Basic for Applications does not detect a compile error until the third attempt. On the first attempt, Visual Basic for Applications successfully compiles the code, and then inserts the procedure from the text file. On the second attempt, Visual Basic for Applications successfully compiles the code in the target module, which now includes one instance of the procedure from the text file; it then inserts a second copy of the procedure from the text file. On the third attempt, Visual Basic for Applications detects a compile error because there are two instances of the same procedure in the target module.

RESOLUTION

To prevent the AddFromFile method from inserting a duplicate procedure, use the Find method of the Module object to determine if the procedure already exists in the module. To do so, follow these steps:

1. Repeat steps 1-4 from the "Steps to Reproduce Behavior" section later in this article.

2. Type the following procedure:

        Function ImportProc(vbProcName As String)
           Dim mdl As Module
           Dim vbLineStop As Long
   
           Set mdl = Modules(0)
           vbLineStop = mdl.CountOfLines
           If Not mdl.Find(vbProcName, 1, 1, vbLineStop, 1, False) Then
              mdl.AddFromFile "C:\My Documents\Test.txt"
           Else
              MsgBox "'" & vbProcName & "' already exists in module."
           End If
        End Function
   
   

3. To test this function, type the following line in the Debug window, and then press ENTER:

         ?ImportProc("Sub AddFromFileTest()")
   

   Note that the AddFromFileTest procedure is successfully inserted into the module.

4. Repeat step 3 to run the procedure again.

   Note that the procedure is not inserted into the module, and that you receive the message:

          'Sub AddFromFileTest()' already exists in module.
   

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Using Notepad or another text editor, create a new text file, and add the following text to the text file:

         Sub AddFromFileTest()
            MsgBox "Hello"
         End Sub
   
   

2. Save and close the text file as C:\My Documents\Test.txt.

3. Start Microsoft Access and open the sample database Northwind.mdb.

4. Create a module and type the following line in the Declarations section if it is not already there:

         Option Explicit
   

5. Type the following procedure:

         Function ImportProc()
            Dim mdl As Module
            Set mdl = Modules(0)
            mdl.AddFromFile "C:\My Documents\Test.txt"
         End Function
   
   

6. On the Debug menu, click Compile And Save All Modules. When prompted to save the module, type "ImportTest" (without the quotation marks) and then click OK.

7. Type the following line in the Debug window, and then press ENTER:

         ?ImportProc()
   

   Note that the AddFromFileTest procedure is successfully added to the module.

8. Repeat step 7 to run the ImportProc procedure again.

   Note that a second copy of the AddFromFileTest procedure is successfully added to the module, and no error is generated.

9. Repeat step 7 to run the ImportProc procedure a third time.

   Note that you receive the message:

         Compile Error:
   

         Ambiguous name detected: AddFromFileTest
   

REFERENCES

For more information about using the AddFromFile method, search the Help Index for "AddFromFile method."