[AccessD] Trouble creating a memo field

Select ...Into  doesn't allow you to set PKs, indexes or constraints(if avaiable in the 
destination)  and doesn't let you specify the destination datatype.   It is a quick and dirty 
solution in a limited set of cases.

I'd stick with Create table if I were you,

-- 
Stuart

On 27 Jul 2011 at 16:35, William Benson (VBACreations. wrote:

> 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?
>