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

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

1. Start Microsoft Access and open Orders.mdb.
2. Start Microsoft Excel and open a new worksheet.
3. Select cells A1-D2.
4. 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.
5. 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:

1. Start Microsoft Access and open Orders.mdb.
2. Start Microsoft Word for Windows and open a new document.
3. On the Insert menu, click Field.
4. 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:

1. Select the field you inserted and click the right mouse button.
2. Click Toggle Field Codes. You should see the formula:
   
   {= DDE MSAccess "C:\\ACCESS\\ORDERS.MDB;Table Categories" FieldNames;T MERGEFORMAT }
3. Modify the formula so that it does not contain the "=" sign as follows:
   
   {DDE MSAccess "C:\\ACCESS\\ORDERS.MDB;Table Categories" FieldNames;T MERGEFORMAT }
4. Select the field again and click the right mouse button.
5. Click Update Field.