What are the String, LongText and ShortText lengths? Setting Long Text as SQL Parameter results in Error 3001

Question

When I create an append Query in ms-access 2013 with parameters and any of those parameters' type is set to LongText the query fails with error code 3001 Invalid Argument. Changing the type to ShortText however results in a working query. Both version are runnable by double clicking the query in access itself, but the first one fails when running it via 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 insert the parameter to has a field type of LongText and therefore I would expect this to work - what is the root cause of the issue here? And how can I pass in a long text if I am unable to specify a LongText as parameter?

I think it might be connected to the length limitations of Strings in access. What exactly are those limitations? Google redirects you to concatenation and max length of string in VBA, access regarding the question for string lengths, but i can not find a definite answer to the length question(s):

• how long can the text for ShortText be?
• how long can the text for LongText be?
• how long can the text for a vba String be?

My queries in the 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;

Scalawag

If inhalte is a Recordset object then try explicitly specifying the .Value property, e.g., inhalte("DefaultInhalt").Value and see if that makes any difference. – Eboat

Still the same incredible useful error message. – Scalawag

Answer 1

• ShortText (simply Text prior to Access 2013) can be up to 255 characters in length.
• LongText (Memo prior to Access 2013) can be up to 1 GB in length, but most Access controls can only display 64000 characters. (A Textbox in a Form will start behaving weird when editing the text, if it contains much less than those 64000 characters.)

See the Access 2013 Documentation for further details.

• A VBA variable-length String can be up to 2^31 characters

See the Visual Basic for Applications Language Reference for further details.

---

Now for your question regarding the LongText-Parameter in the QueryDef-Object. Unfortunately DAO does not support LongText as Parameter-Type for a Query even though it lets you create the parameter in query design.

You have got the following options as a workaround:

• Open a recordset and add/update the record there
• Use an ADO-Command-Object for that query
• Hardcode your function inhalte("DefaultInhalt") into the SQL of the query
• Or concatenate your own SQL string including the values (Total SQL lenght limited to 64000 characters!)

Mender

I ended up using just plain old sql inside my vba code, not as clean, but it is the least time intensive workaround and it works without any problems. – Scalawag

Answer 2

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

Divinity