Alternately if you run a make table query using already formatted fields as an input, you will find that the new table does not carry over the formatting of your input fields. Therefore in each situation, you need to set the format for the field.

The format and decimal places properties of a field do not exist until they are created, so if you query a field's "format" property before it is created, you'll get an error saying there is no such property. So here's some code which creates a simple table, and then sets the format and decimal places properties for a couple of fields. You can strip out the code for the format section to create a new function for setting the format for a table after running a make table query.

(Note: This code does not work using ADO in Access 2000, in fact there is no similar ability to set decimal places or formatting of fields if you use ADO as the data access method of your db in Access 2000. You must create a temporary reference to or use DAO as the data access method in Access 2000 to support this capability.)

Function acg_CreateTable(strTable As String) As Integer
'-------------------------
'Purpose:  Creates A new table and sets field format
'Accepts:  strTable, the name of the new table
'Returns:  True (-1) on success, False on failure
'-------------------------
On Error GoTo ErrCT

Dim TDB As Database
Dim fld1 As Field, fld2 As Field, fld3 As Field
Dim fFormat2 As Property, fFormat3 As Property
Dim idxTbl As Index
Dim idxFld As Field
Dim Newtbl As TableDef
Dim Newtbl2 As TableDef

acg_CreateTable = True

'First Create the table

Set TDB = CurrentDb()
Set Newtbl = TDB.CreateTableDef(strTable)
Set fld1 = Newtbl.CreateField("MyStringField", dbText, 75)
Newtbl.Fields.Append fld1
Set fld2 = Newtbl.CreateField("MyNumberField", dbDouble)
Newtbl.Fields.Append fld2
Set fld3 = Newtbl.CreateField("MyDateTimeField", dbDate)
Newtbl.Fields.Append fld3
TDB.TableDefs.Append Newtbl

'Create an index for our table.  Need to use a new tabledef 
'object for the table or it doesn't work

Set Newtbl2 = TDB.TableDefs(strTable)
Set idxTbl = Newtbl2.CreateIndex("PrimaryKey")
idxTbl.Primary = -1
idxTbl.Unique = -1
Set idxFld = idxTbl.CreateField("MyStringField")
idxTbl.Fields.Append idxFld
Newtbl2.Indexes.Append idxTbl

'Format the single field to have two decimal places
'and the datetime field to be a medium time.
'Note that decimal places has no space in the name

Set fld2 = Newtbl2.Fields("MyNumberField")
Set fFormat2 = fld2.CreateProperty("Format", dbText, "Fixed")
fld2.Properties.Append fFormat2
Set fFormat2 = fld2.CreateProperty("DecimalPlaces", dbByte, 2)
fld2.Properties.Append fFormat2
Set fld3 = Newtbl2.Fields("MyDateTimeField")
Set fFormat3 = fld3.CreateProperty("Format", dbText, "Medium Time")
fld3.Properties.Append fFormat3

TDB.Close
    
ExitCT:
    Exit Function
ErrCT:
    If Err <> 91 Then TDB.Close
    acg_CreateTable = False
    Resume ExitCT
End Function

What this capability provides is the ability set a query as a source for a field in a table, so that you can reference look up tables. (e.g. state abbreviations, specific product types etc.) You can also set a limit to list property for the look up field, which is generally recommended, since you can't respond to an event where the user enters a value directly in the table other than what's in the look up list. (You can respond on a form using vba.)

Here's some practical advantages and ways to use this new capability:

1. First, unlike Access 2, the Look Up specification allows you to use combo boxes in a datasheet view of a table to provide prespecified values and limit entry in a field.

2. You can save data storage space in your tables by storing only numeric values for look up strings which are in your look up table. To do this:
   1. Add a corresponding numeric number for each string value in your look up table.

   2. Set the field type of the target table to number.

   3. Add both the string and numeric values to the query for Look Up.

   4. Set the bound column for the Look Up in the target table to the number field in the look up query.

   5. Set the column width for the number field as zero width to make it invisible, and the column width for the string field to something appropriate to accomodate its width.

      Now when your user uses the combo box to select a value from the Look Up only the string value which they understand will be shown, but a number will be stored in the target table.

3. In similar fashion, if you create a query based on the table with a predefined lookup, and have set up the look up field as a number as shown above, only the corresponding string value will be displayed in your query as Access automatically will create a subquery for the look up.

   This eliminates many of reasons to have to use the "Choose" function of Access which was tremendously slow when used in queries.

4. When you set a look up field query, then when you create a form based on the table or query which includes the table, a combo box will be the default control type created rather than a text box.

Run-time Error: 3251
"Object or provider is not capable of performing requested operation."

This will occur if you set the Connection for the ADOX catalog object to be equal to connection string returned by CurrentProject.Connnection. 

Access' base connection does not support table creation because Access uses an intermediate OLEDB provider to access the SQL Server.  To successfully create a table on a SQL Server from within Access, you must open a new connection to the server and use that connection on the server's catalog as shown below:

Function CreateSQLTbl()
'-------------------------
'Purpose:  Creates A new table on a SQL Server Northwind Db
'Accepts:  strTable, the name of the new table
'Returns:  True (-1) on success, False on failure
'-------------------------
On Error GoTo ErrHandler

Dim objCat As ADOX.Catalog
Dim objTbl As ADOX.Table
Dim objCol As ADOX.Column
Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
'Note fill in your server name in the connection string below!!!!
conn.ConnectionString = "Provider=SQLOLEDB.1;Security Info=False;" & _
    "Data Source=YourServerNameHere;Integrated Security=SSPI;" & _
    "Initial Catalog=Northwind"
conn.Open
Set objCat = New ADOX.Catalog
Set objTbl = New ADOX.Table
Set objCat.ActiveConnection = conn
objTbl.Columns.Append "TestField", adInteger
objTbl.Name = "TestTable"
objCat.Tables.Append objTbl
Set objTbl = Nothing
Set objCat = Nothing
conn.Close
Set Conn = Nothing

ExitCT:
    Exit Function
ErrHandler:
    CreateSQLTable = False
    Resume ExitCT
End Function