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.