Using REQUEST With FieldCount DDE Topic Causes Macro Error

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

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
				

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.

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