Using REQUEST With FieldCount DDE Topic Causes Macro Error

The information in this article applies to:

• Microsoft Excel for Windows, versions 4.x, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

A macro error may occur in Microsoft Excel when using the REQUEST() macro function with the Dynamic Data Exchange (DDE) topic, FieldCount, to count the number of columns in an Access 2.0 table.

CAUSE

The FieldCount DDE topic is not accessible via a Microsoft Excel macro when used against a Microsoft Access 2.0 database. The following macro illustrates this problem:

A1: GetNumberOfFields A2: DB="C:\ACCESS\SAMPAPPS\NWIND.MDB" A3: chan =INITIATE("MSACCESS",DB&";SQL Select * from Customers;") A4: =FORMULA(REQUEST(chan,"FieldCount")) A5: =TERMINATE(chan) A6: =RETURN()

The REQUEST function returns #N/A, which causes the FORMULA function to return a macro error (#VALUE!).

RESOLUTION

To avoid this error, use either of the following methods to count the number of fields in a Microsoft Access 2.0 table. Use Method 1 if you are using Microsoft Excel version 5.0 or later.

Method 1: Use this method if you are using Microsoft Excel version 5.0 or

          later. This method uses the SQL functions provided in the
          XLODBC.XLA add-in to count the columns in the table. This method
          is preferable because it is faster and does not need to run
          Microsoft Access to work.

          Microsoft provides examples of Visual Basic procedures for
          illustration only, without warranty either expressed or implied,
          including but not limited to the implied warranties of
          merchantability and/or fitness for a particular purpose. This
          Visual Basic procedure is provided 'as is' and Microsoft does not
          guarantee that it can be used in all situations. Microsoft does
          not support modifications of this procedure to suit customer
          requirements for a particular purpose.

          Sub CountColumns()
              ' Declare local variables
              Dim Chan As Variant, NC As Variant

              ' Connects to the data source. You will need to change the
              ' connection string to refer to the appropriate data source.
              Chan = SQLOpen("DSN=Access 2.0 Databases")
              ' Check for connection error.
              If IsError(Chan) Then
                  MsgBox SQLError()(3)
                  Exit Sub
              End If

              ' Select all fields from the table. SQLExecQuery
              ' returns the number of columns in the result set.
              NC = SQLExecQuery(Chan, "SELECT * FROM Orders")
              MsgBox "There are " & NC & " columns in the table."

              ' SQLGetSchema returns an array of field names. Using
              ' Ubound will return the number of elements in the array.
              NC = UBound(SQLGetSchema(Chan, 5, "Orders"))
              MsgBox "SQLGetSchema returns " & NC & " columns."

              ' Close the ODBC channel
              SQLClose Chan
          End Sub

Method 2: Instead of using FieldCount, use the FieldNames DDE topic to

          return an array of field names from the table. Then, use the
          COLUMNS() function to count the number of names in the array, as
          shown in the following macro:

          A1: CountFields
          A2: tname=INPUT("Enter a table name:")
          A3: =DIRECTORY("C:\ACCESS")
          A4: DB="C:\ACCESS\SAMPAPPS\NWIND.MDB"
          A5: chan=INITIATE("MSACCESS",DB&";SQL Select * from "&tname&";")
          A6: =IF(ISERROR(chan))
          A7: = ALERT("Error opening database or syntax error in SQL
                statement.")
          A8: = ELSE()
          A9:     colnums=COLUMNS(REQUEST(chan,"FieldNames;T"))
          A10: =  ALERT("There are "&colnums&" columns in "&tname&".")
          A11: =  EXECUTE(chan,"[QUIT]")
          A12: =  TERMINATE(chan)
          A13: =END.IF()
          A14: =RETURN()

          Explanation of Macro
          ====================

           A1: Name of the macro.
           A2: Ask user for table name.
           A3: Change the directory to the Microsoft Access directory.
           A4: Specify the directory and file name of the database to use.
           A5: Initiate a channel to Access and open the NWind database.
           A6: Check for successful connection.
           A7: If an error occurred, tell the user.
           A8: If no error occurred, goto A9.
           A9: Get the number of fields in the table.
          A10: Display how many columns are in the table.
          A11: Quit Microsoft Access.
          A12: Terminate the DDE channel.
          A13: End the IF statement.
          A14: End of macro.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access, version 2.0 for Windows. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

REFERENCES

For more information on SQL Functions, search for one of the following topics in the Visual Basic Reference file:

   sql functions: data source information
   SQLGetSchema
   SQLExecQuery