ADO recordset fails on "memo" datatype during import into Excel

I am trying retrieve data from a SQL server, for use in some Excel 2003 macros. I would like to avoid the use of QueryTables, as I don't want this intermediate step of writing and reading from actual sheets. It seems time-consuming and pointless.

I have managed to get the recordset, but it contains empty data where the datatype is "memo", on the server.

Further, the program crashes where it tries to store the data into a Range. It appears to make it to the first "empty" field and then it gives me a 1004 Error Code.

Clearly the memo field is giving me grief. Can anyone make a suggestion as to how to get around this, or what I should be doing differently开发者_如何学JAVA?

    objMyConn.connectionString = "ODBC;" _
    & "Provider=SQLOLEDB;DRIVER={SQL Server};SERVER=VANDB;" _
    & "APP=Microsoft Office 2003;DATABASE=WPDB_BE;Trusted_Connection=Yes;"
    objMyConn.Open

I've been searching online for ages, but this Access / ADO / Excel stuff is exceedingly painful. Please help.

Edit 1: I later modified the SQL query with "TOP 1" (SQL version of "LIMIT 1") and found that with that recordset, the memo fields were returned correctly. Similarly, I could SELECT a single problematic field, and get more rows, e.g. "SELECT TOP 52 bad_field FROM ..."

So I suspect that the issue is an ADO connection data size limit of some sort? It seems the Access "memo" type is simply like a "MEDIUMTEXT" MySQL type, so how would I get around such a limit? It's a separate question then, but what alternatives are there to the ADO connections?

You can use your ADO Connection object (objMyConn) to discover the data type (among other attributes) as ADO interprets it:

With objMyConn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "your_table_name_here"))
  .Filter = "COLUMN_NAME = 'your_column_name_here'"
  MsgBox .Fields("DATA_TYPE").Value
End With

This will return the integer value of its respective SchemaEnum enum value, use the object browser to discover the enum value. Posting the results here could give a further clue to your problem.