[AccessD] Trouble creating a memo field

Jim Dettman jimdettman at verizon.net
Thu Jul 28 05:22:06 CDT 2011


 Not sure what Allan has posted, so this might be duplication, but there is
a series of three MSKB articles that shows what you can do with JET 4.0 and
SQL.

 This one:

http://msdn.microsoft.com/en-us/library/aa140015(v=office.10).aspx#acintsql_
intddl

 Has a section on DDL statements.

 Good series of articles (links to the others are at the bottom).

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jack drawbridge
Sent: Wednesday, July 27, 2011 05:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Trouble creating a memo field

William,
Allen Browne has samples of various DDL statements here.
http://allenbrowne.com/func-DDL.html

jack

On Wed, Jul 27, 2011 at 4:35 PM, William Benson (VBACreations.Com) <
vbacreations at gmail.com> 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?
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list