Clever Geek Handbook

What are the lengths of lines, long text and short text? Setting long text as SQL parameter results in error 3001

When I create an append Query in ms-access 2013 with parameters, and any of these parameter types are set to a value LongText

, the query fails with an error code 3001 Invalid Argument

. However, changing the type ShortText

results in a working request. Both versions can be run by double-clicking the query in access itself, but the first one fails with the following code:

Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As QueryDef
Set qdf = db.QueryDefs("NeuerFachlicherInhalt")
    qdf!Inhalt = inhalte("DefaultInhalt")
    qdf!Formular = inhalte("Formular")
qdf.Execute

The table I am inserting the parameter into has a field type LongText

and so I expect this to work - what is the root cause of the problem here? And how can I pass in long text if I cannot provide a parameter LongText

?

I think it might be due to the limitations on the length of strings in access. What exactly are these limitations? Google redirects you to concatenate and max string length in VBA, access to the string length question, but I cannot find a definite answer to the length question.

  • How long can the text be for ShortText

    ?
  • How long can the text be for LongText

    ?
  • How long can text be for vba String

    ?

My queries in two cases look like

PARAMETERS Inhalt LongText, Formular Short;
INSERT INTO FachlicherInhalt ( Inhalt, Formular )
SELECT [Inhalt] AS Expr1, [Formular] AS Expr2;

PARAMETERS Inhalt Text ( 255 ), Formular Short;
INSERT INTO FachlicherInhalt ( Inhalt, Formular )
SELECT [Inhalt] AS Expr1, [Formular] AS Expr2;
+3


source to share


2 answers


  • ShortText

    (just Text

    before Access 2013) can contain up to 255 characters.
  • LongText

    ( Memo

    up to Access 2013) can be up to 1 GB in length, but most access controls can only display 64,000 characters. (A text box on a form will start to behave strangely when editing text if it contains much less than 64,000 characters.)

See Accessing the 2013 documentation for details .

  • VBA variable length String

    can be up to 2 ^ 31 characters

For more information, see Visual Basic for Application Language for Applications .



Now for your question regarding the LongText parameter in QueryDef-Object. Unfortunately, DAO does not support LongText as a type parameter for a request, even though it allows you to create a parameter in the request design.

As a workaround, you have the following options:

  • Open the recordset and add / update the record there
  • Use ADO-Command-Object for this query
  • Hardcode your function inhalte("DefaultInhalt")

    in a SQL query
  • Or concatenate your own SQL string including values ​​(total SQL is limited to 64,000 characters!)
+5


source


As long as I'm reading your question correctly, I'm pretty sure you can't use a longtext / memo field as a parameter. As per the info found here: Any way to have long text (memorable) parameters in DAO and MS Access?



+2


source






More articles:


All Articles