ms access - What are the limits for ADO data types?

Question

I'm trying to determine the appropriate ADO Command Parameter data types to use for calling a SQL Server (2005) stored procedure. I was specifically first trying to determine the appropriate ADO data type that would correspond to a SQL Server data type of varchar(MAX). I think it might be adVarChar, but I'm not sure.

Why isn't the size (e.g. number of characters for 'string' types, range for numeric types) for each of these data types listed in the documentation?! And why is it seemingly impossible to find a handy table listing each of the data types and the maximum amount of info you can stuff in each of them?! You'd think someone would notice the probably millions of questions related to variants of "Why is my data being truncated?" ...

Clarification – The above info is merely a concrete example illustrating the utility of knowing the limits of the ADO data types, e.g. to choose an appropriate ADO data type to handle specific data types for various data sources.

Answer 1

Specific part

varchar(MAX) can be used from ADO as an input parameter.
The data type in this case would be adLongVarChar, max length is &h7FFFFFFF, as documented here.

It cannot be used as an output parameter though.
Nor can it be consumed as a field type in a returned recordsed (funny -- .Value is Empty, because it's actually a long type, but GetChunk may not be called to retrieve the actual data because ADO thinks it's not a long type).

If you need to consume varchar(MAX) as an output parameter using VBA/ADO, you will have to select it to return a recordset to the client, and you will have to cast it to text while doing that:

select cast(@var as text) as data;
return 0;

Then you would say s = .Fields(0).GetChunk(.Fields(0).ActualSize) to get the data from the opened recordset.

---

Abstract part

The very point of ADO is to abstract away differences between different data sources. As soon as there's a data access driver around that supports an interface, you (ideally) may talk to it without bothering what it is.

As any abstraction, this one is also leaky.

The exact knowledge of what data types of what servers map to which ADO data types comes from experience. That is.

Some rules of thumb, hovewer, may be developed quite quickly:

• It is not difficult to figure possible ADO data types by matching their names with data type names of the particular server:

  • int - adInteger
  • datetime - adDBDate (although here you might be forced into some trial and error)

• Certain data types are called BLOBs (binary large objects). They are designed to contain a huge piece of data and usually presented in the data source documentation as such. For these, a corresponding ADO data type is likely to contain Long in its name, which, in ADO world, means "BLOB" (adLongVarBinary, adLongVarChar, adLongVarWChar).

• Any information on exact length of a data type is to be found in the documentation for the data source, not the ADO documentation. For things like:

  • Maximum length set by a developer for a specific column in this particular table (such as varchar(10))
  • Maximum theoretical length of a BLOB data type (such as varchar(max))

  you are going to consult the corresponding data source, not ADO.