Using REQUEST With FieldCount DDE Topic Causes Macro Error

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

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 

          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

Additional query words: 4.00 4.0a 2.00 odbc

Keywords :
Version : 4.x 5.00 5.00c 7.00
Platform : WINDOWS
Issue type :