why is my expression being truncated?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gareth - 30 Mar 2006 20:10 GMT

I am trying to export some data via a text file. I want the text file to
contain SQL insert statements so I am using an expression
SQL: "INSERT INTO products_description(products_id, language_id,
products_name, products_description) VALUES ("+Str([StockID])+", 1,
'"+[ShortDescription]+"', '"+[FullDescription]+"';"
FullDecription in the above is a memo field and can be quite long. The
expression is being truncated in some cases. Is there a maximum length for an
expression? I can't find any documentation on this.

Reply to this Message

Vincent Johns - 31 Mar 2006 01:47 GMT

The documentation is a bit obscure; in Help, look for "Access
specifications". Among them is this: "Number of characters in an SQL
statement: approximately 64,000". Now, that is indeed "quite long", but
have you considered creating a text file with your memo information (for
example, in which each line contains the unique [StockID] number, a Tab
character, your text, and a Return character)? You might be able to
generate this via Notepad or something similar. You could just import
that file into an Access Table without fiddling with this SQL stuff.
You could then modify that (imported) Table by adding fields and using
an Update Query to update the records, based on those identifying
numbers in your text file. When you're done, you can back up your
database and erase the text file you imported.

-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.

> I am trying to export some data via a text file. I want the text file to
> contain SQL insert statements so I am using an expression
[quoted text clipped - 4 lines]
> expression is being truncated in some cases. Is there a maximum length for an
> expression? I can't find any documentation on this.

Reply to this Message

Gareth - 31 Mar 2006 09:12 GMT

Vincent:

Thanks for the response, but I'm afraid it doesn't help. The data is already
in Access, I am trying to output it as formatted text - the fact it is SQL is
incidental, that is not for use in Access. By "quite long" I meant c.300
chars by the way, not as much as 64k.

I have played around a bit and found that it works OK if I put the formatted
text into a memo field in a temporary table first and then export the
temporary table. Seems a bit messy though.

Regards

> The documentation is a bit obscure; in Help, look for "Access
> specifications". Among them is this: "Number of characters in an SQL
[quoted text clipped - 20 lines]
> > expression is being truncated in some cases. Is there a maximum length for an
> > expression? I can't find any documentation on this.

Reply to this Message

Vincent Johns - 31 Mar 2006 11:13 GMT

Aha. Well, I'm pleased that you were able to find a solution. It does
seem a bit messy, but probably not any worse than having to process all
your data by moving them into SQL statements and out again! (Something
else that might be causing trouble is punctuation inside your strings,
such as quotation marks, that normally isn't a problem, but that would
interfere with parsing the SQL.)

You might make things a bit tidier by putting some of your steps into a
Macro (including deleting the temporary Table after you've written its
contents to the text file).

-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.

> Vincent:
>
[quoted text clipped - 33 lines]
>>>expression is being truncated in some cases. Is there a maximum length for an
>>>expression? I can't find any documentation on this.

Reply to this Message