Microsoft KB Archive/135570

= Using REQUEST With FieldCount DDE Topic Causes Macro Error =

Article ID: 135570

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0c
 * Microsoft Excel 4.0c
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q135570





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

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.

