MS Access VBA - MS SQL 2005 VARCHAR(MAX) parameter

See more:

SQL-Server-2005

SQL-Server

, +

Existing project that has a column defined as TEXT type in SQL2005. Going to convert it over to VARCHAR(MAX) in the backend. None of that's an issue. But in the ADP front end, where I'm passing parameters to and from procedures, I'm not sure what to use. Currently, I have:

cmd.Parameters.Append cmd.CreateParameter("@BigVariable", adLongVarChar, adParamInput, -1, "sample text")

The above has been working fine for several years.

I tried using the same code in a test proc and it seems to work but when i try to return a varchar(max) param I get an error that the parameter is not properly defined. I ran code on the proc from the adp to see what the param values were and the "type" is 200 and the "size" is 2147483647. I tried using those values instead of adLongVarChar and -1 and got the same error that it's not defined right.

Interestingly, adLongVarChar is 201 but that doesn't really explain anything.

Can anyone tell me definitively what the param type is for varchar(max) and the size for either input or output params?

thanks,

Keith