TEXT data type length?

[Guest]

I am just learning SQL for ACCESS 2003. When I make the following statement I
get a Text data type with a maximum length of 255 characters:

CREATE TABLE tblTest1 (test_field1, text);

I have checked several books and Microsoft Online Help and they all say that
TEXT is a 2.14 gig text field (same as MEMO) and that CHAR is the 255 byte
length data type. I can't imagine they are all wrong so I must be doing
something wrong because when I try either of them I can never get larger than
a 255 length.

Please, can anyone straighten me out on this matter.

 

[Douglas J. Steele]

Try using Memo instead. You're right: the Help file appears to be in error,
as the Text data type in Access has never been able to accept more than 255
characters.

 

[Brendan Reynolds]

It's a DAO vs ADO thing ...

Public Sub TestText()

CurrentDb.Execute "CREATE TABLE TestText1 (TestField TEXT)"
CurrentProject.Connection.Execute "CREATE TABLE TestText2 (TestField
TEXT)"

End Sub

After running this code, the field is Text in table TestText1, but Memo in
table TestText2.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Thanks for the responses. I think Brenden is heading me in the right
direction. Unfortunately I haven't worked with Data Access Objects yet so it
is confusing to me. By chance, do you know a book or web page that gives
better explination of how the Text data types behave in Access SQL as opposed
to directly in Access?

Regards, Jeff in Oregon USA

 

[Brendan Reynolds]

I wasn't aware of the difference myself, until I tested it as a result of
reading your post.

There are a number of different ways that we can run a make-table query in
an Access MDB. We can use the DAO or ADODB Execute methods, we can run the
query via the graphical query designer, or we can use DoCmd.RunSQL. In my
tests, the only one of these methods that causes TEXT in the SQL statement
to create a Memo field is the ADODB Execute method. DAO, the query designer,
and RunSQL all create a 255 character Text field.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

Thanks again Brenden. I find quirky things like this all the time. Symptom of
being a deep studier I guess. It drives my professor nuts.

I have also seen Microsoft documents that state 4 different possible data
lengths for MEMO fields; 128K (64K UNICODE CHARACTERS), 1 gig, 2.14 gig, and
unlimited for text and numbers under DAO. I'll have to test these to find out
what's what. Now, if I can just remember where I saved that 3 gigabyte text
file...Hmmm.

Regards, Jeff In Oregon USA