[AccessD] Trouble creating a memo field

Hi William

Use DAO to create tables and fields (and indexes and relations, even databases) and all your trouble will end. Seriously.
That way you will be in control of the finest details including all properties of the objects. Example:

<code>
Public Function SetTableProperties() As Boolean
   
  Dim dbs             As DAO.Database
  Dim tdf             As DAO.TableDef
  Dim fld             As DAO.Field
  Dim prp             As DAO.Property
  Dim strPropertyName As String
  
  Set dbs = CurrentDb()
  Set tdf = dbs.TableDefs("tblTest")
  Set fld = tdf.Fields(0)
  
'  On Error GoTo Err_Prop
  
  strPropertyName = "Description"
  
  Set prp = tdf.CreateProperty()
  prp.Name = strPropertyName
  prp.Type = dbText
  prp.Value = "Table Description"
  tdf.Properties.Append prp
  
  strPropertyName = "Caption"
  
  Set prp = fld.CreateProperty(strPropertyName, dbText, "Field Caption")
  fld.Properties.Append prp
  
  SetTableProperties = True
  
Exit_Prop:
  Exit Function
  
Err_Prop:
  If Err.Number = 3265 Then
    ' Item not found in this collection.
    Resume Next
  Else
    Debug.Print Err.Number, Err.Description
    Resume Exit_Prop
  End If

End Function
</code>

/gustav

>>> vbacreations at gmail.com 27-07-2011 22:35:50 >>>
I sometimes try to create new tables using SQL but not using CREATETABLE.
Example:	Select 'Test' as Field1 Into Tbl1

One problem I had recently was, how can one make a MEMO field this way? (I
can't find a way, not even with
     MyDB.Execute ("Select '" & string(257,"a") & "' as Field1 Into
Tbl1"),DBFailonError
When I do that I get only 255 characters and the field is Text.

Another problem is that I often like the table to be created without a first
row in it.  So I might write something like
      	Select 'Test' as Field1 Into Tbl1 From Tbl2 WHERE 1 = 2
Trouble is, What if there is no table named Tbl2? (Obviously it fails). But
I can't seem to get the WHERE clause to be acceptable when I do not specify
a valid table in the FROM clause, unlike the first example.

Now, I might be able to solve the latter problem by specifying a system
table as the FROM table, something I know will always be present... such as:
      	Select 'Test' as Field1 Into Tbl1 From MSysObjects WHERE 1 = 2

But then that code probably would not work in other (non-MSAccess)
databases. In fact, I am not sure this process of using Select Into works in
non-Access databases anyway (does it?)

So... I am now leaning towards using CREATE TABLE always and forever as my
choice for the best performing method of creating a table using inline SQL.

Does anyone beg to differ with this and/or have a way to resolve the issues
I have found?