Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Thanked 0 Times in 0 Posts

    make table (Access 2000)

    With the help of Hans from the present Forum i have succeded to build a code for making a table.
    I have a field called CustomerID.This field, in the original database, is Number, however it is a loookup field,looking up the fields CustomerId and Company Name in the table Customers.Therefore on opening the table, one sees the Company Name and not its number.However when i build the code,only the number it possible to attach the lookup property to CustomerId by code?I presume i will have to write something additionalo beside LONG to the CustomerID

    The code i have is the following:

    dbs.Execute "CREATE TABLE OrdersArchive " & _
    "CustomerID LONG, OrderDate DATETIME," & _

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Newbury, Berkshire, UK, Berkshire, England
    Thanked 0 Times in 0 Posts

    Re: make table (Access 2000)

    I think the lookup is an Access field property, so you won't be able to create this using SQL.
    You'll have to look at altering or adding to the TableDef / Fields - looking at the Help on the Field object and its properties, can see any for a "lookup", so I suspect this is one you'll have to create and add to the field object
    (sorry, I'm still working with DAO objects, rather than ADO, so not sure how to do this with ADOX)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: make table (Access 2000)

    As Steve C. mentions, you can't use SQL to set these properties (AFAIK) - they're specific to Access.

    You can use DAO to do this. Here is an example. I have included more properties than you probably need to set. Some of them will be set automatically. I have included them for completeness.

    Sub ChangeLookup()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs![tblMyTable]
    Set fld = tdf.Fields![CustomerID]
    With fld.Properties
    ' Set Display Control to combo box (integer)
    !DisplayControl.Value = acComboBox
    ' Set Row Source Type to Table/Query (string)
    !RowSourceType = "Table/Query"
    ' Set Row Source to Customers table (string)
    !RowSource = "tblCustomers"
    ' Set Column Count to 2 (integer)
    !ColumnCount = 2
    ' Set Column Heads off (Boolean)
    !ColumnHeads = False
    ' Set Bound Column to 1 (integer)
    !BoundColumn = 1
    ' Set Column Widths to 0 inch and 1 inch (string)
    ' Values in twips (1 inch = 1440 twips) separated by semicolons ;
    !ColumnWidths = "0;1440"
    ' Set List Width (string, include unit: inch or cm or twip)
    !ListWidth = "2 inch"
    ' Set List Rows to 6 (integer)
    !ListRows = 6
    ' Set Limit to List to True (Boolean)
    !LimitToList = True
    End With
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts