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