ACC: How to Get Field Data Types from a Table Using DDE

ID: Q102520

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

The DDE FieldNames item has been enhanced in Microsoft Access 1.1 and later by adding the ability to get the field type for the field. In Microsoft Access 1.0, the FieldNames item could get the field name, but not the field type. The new item is called FieldNames;T, and can be requested on any open Microsoft Access database.

Examples showing how to use this DDE feature from Microsoft Excel and Microsoft Word for Windows are listed below.

MORE INFORMATION

The following sample Microsoft Excel macro demonstrates how to use the FieldNames;T item to get the field name and field type from the Categories table in the sample database ORDERS.MDB

Start Microsoft Access and open Orders.mdb.

Start Microsoft Excel and open a new worksheet.

Select cells A1-D2.

Type the formula:

=MSAccess|'ORDERS.MDB;Table Categories'!'Fieldnames;T'

Note: If you want Microsoft Excel to open Microsoft Access and get the information from the specified database, the complete path to the database file should be specified. For example:

C:\Progra~1\Micros~1\office\samples\orders.mdb

It is required to use the DOS alias name when specifying the path as DDE does not support long file names.

Press CTRL+SHIFT+ENTER. This enters the formula as an array formula. The results are propagated through the selected range.

The following information is returned:


   Category ID    Category Name    Description    Picture
       10             10               12           11

The numeric values returned describe the data type for the field. The data types are:


    0    Invalid
    1    True/False (non-Null)
    2    Unsigned byte (Byte)
    3    2-byte signed integer (Integer)
    4    4-byte signed integer (Long)
    5    8-byte signed integer (Currency)
    6    4-byte single-precision floating-point (Single)
    7    8-byte double-precision floating-point (Double)
    8    Date/Time (integral date, fractional time)
    9    Binary data, 255 bytes maximum
   10    ANSI text, not case sensitive, 255 bytes maximum (Text)
   11    Long binary (OLE Object)
   12    Long text (Memo)

The following example for Microsoft Word for Windows demonstrates how to use the FieldNames;T item to get the field name and field type from the Categories table in the sample database Orders.mdb:

Start Microsoft Access and open Orders.mdb.

Start Microsoft Word for Windows and open a new document.

On the Insert menu, click Field.

Type the following formula in the Field Codes box, and then click OK:

=DDE MSAccess "ORDERS.MDB;Table Categories" FieldNames;T

Note: If you want Microsoft Word to open Microsoft Access and get the information from the specified database, the complete path to the database file should be specified. For example:

C:\\Progra~1\\Micros~1\\office\\samples\\orders.mdb

You are required to use the DOS alias name when specifying the path as DDE does not support long file names.

The following information is displayed:


   Category ID    Category Name    Description    Picture
       10              10              12           11

Some formatting of the text may be necessary to align the field type descriptor numerals under the table names.

NOTE: If you see the formula you typed instead of this data, and you are using Microsoft Word 6.0, 7.0, or 97 you need to clear the Field Codes check box on the View tab by clicking Options on the Tools menu. If you are using Microsoft Word 2.0, click Field Codes on the View menu.

If you are running Microsoft Word 7.0 or 97, you may see the error "!Syntax Error, MSACCESS" after clicking OK. If so, then follow these steps to correct the error:

Select the field you inserted and click the right mouse button.

Click Toggle Field Codes. You should see the formula:

{= DDE MSAccess "C:\\ACCESS\\ORDERS.MDB;Table Categories" FieldNames;T MERGEFORMAT }

Modify the formula so that it does not contain the "=" sign as follows:

{DDE MSAccess "C:\\ACCESS\\ORDERS.MDB;Table Categories" FieldNames;T MERGEFORMAT }

Select the field again and click the right mouse button.

Click Update Field.

Keywords : kbinterop TblFldp
Version : 1.0 1.1 2.0 7.00 97
Platform : WINDOWS
Issue type : kbhowto