Drop Lookup from Jet table field

[ras]

My table was created using design mode and has a lookup table.
I now need to remove the lookup table using VBA

ie
docmd.runsql "ALTER TABLE mytable COLUMN myfield DROP LOOKUP"

I have been unable to find any documentation on how to do this but any
suggestions would be appreciated.

 

[Marshall Barton]

My table was created using design mode and has a lookup table.
I now need to remove the lookup table using VBA

ie
docmd.runsql "ALTER TABLE mytable COLUMN myfield DROP LOOKUP"

I have been unable to find any documentation on how to do this but any
suggestions would be appreciated.

For help with SQL, look in the HELP Table of Contents under
Microsoft Jet SQL Reference. The ALTER TABLE statement is
in the Data Definition Language topic.

To drop a column:

ALTER TABLE mytable DROP COLUMN myfield"

Make sure you make a backup copy of the database before
trying this kind of query. If you make a mistake, you will
want to use the original tables when you try again.

 

[Allen Browne]

I don't think you will be able to do this with a DDL query.

You will need to use DAO.
If it is a linked table, OpenDatabase on the actual database that contains
the table. Otherwise use CurrentDb().

Through the TableDef, and you can refer to the Field and its Properties.
Delete the DisplayControl property.

Example:
Currentdb.TableDefs("MyTable").Fields("MyField").Properties.Delete
"DisplayControl"

 

[ras]

I don't think you will be able to do this with a DDL query.

You will need to use DAO.
If it is a linked table, OpenDatabase on the actual database that contains
the table. Otherwise use CurrentDb().

Through the TableDef, and you can refer to the Field and its Properties.
Delete the DisplayControl property.

Example:
Currentdb.TableDefs("MyTable").Fields("MyField").Properties.Delete
"DisplayControl"

THANKS FOR THE POINTERS. The following code will go thru a database
and eliminate all the comboboxes from each table. I had to do this to
fix the problems with 2003 sp3. Thanks for the help


Dim thisdb As Database

Dim dbtable As TableDef
Dim dbfield As Fields
Dim tdfloop As TableDef
Dim prploop As Property
Dim fldloop As Field

Set thisdb = CurrentDb

With thisdb

For Each tdfloop In .TableDefs
For I = 0 To tdfloop.Fields.Count - 1
For II = 0 To tdfloop.Fields(I).Properties.Count - 1
If tdfloop.Fields(I).Properties(II).Name = "DISPLAYCONTROL" THEN
If tdfloop.Fields(I).Properties(II).Value = 111 THEN
tdfloop.Fields(I).Properties(II).Value = 109
END IF
END IF

Next
Next
Next
End With