255 Character Limit when copying data

Untested:
Use ado to establish the connection and SQL to put the data into a recordset. Then loop through the recordset putting the data into cells. I am not sure if you will still hit the truncation using this method or not, but it might be worth a shot.

Thansk Oorang.

When you state that I should try using ADO to establish the connection I assume I should change part of the following string I use at the moment :



What do I need to change to make this an ADO connection?

Hi Tom

I believe that if you add your column with the comments in excel and then use ADO and sql you are stuck with the 255 characters.

However, you could improve your process by creating the columns that house the big strings via ADOX. After the columns are created you can edit the excel as usual. The advantage of creating the extra columns with ADOX (with columns.append) is that you can specify the type like in a database field. In your case, for the comments column, you specify it as adLongVarWChar (equivalent to MEMO). This way you'll have no problem with the 255 chars limit and it's transparent to the ADO.

If you have problems implementing it post back and I can show you what I have (it was not exactly your problem but you can go from there).

Hope this helps
PGC

hi all,

I've entered this thread via another http://www.mrexcel.com/board2/viewtopic.php?p=1370427 and Tom asked for further postings in this thread.

A couple of things. I concur with Oorang and PGC, the ADO approach will still have the same 255 character limit. I didn't actually check this but will do so when I have 5 minutes spare today. I do not know about ADOX at all.

I'm posting here, Tom - in reply to the other thread where you said not to post there anymore - to refer you to a recent thread where there was an ADO example using late binding. That is, it will work without the reference being set from Excel VBA. As you probably know, a late binding approach is less than ideal.

That other thread is http://www.mrexcel.com/board2/viewtopic.php?t=274938 The sample code in that thread omits loading of the field headers, they are not included in the recordset, but they are available. If you want some code for that please post again & I can find a sample from somewhere. There is a collection of fieldnames available when the connection is open, it is just a matter of looping through them. (A look at the object browser will find them if you have the reference set, or a quick google.)

HTH, Fazza

Whilst looking through some notes I keep for a different matter, I stumbled upon notes relative to this thread.

See http://support.microsoft.com/kb/189897 which is titled "Data truncated to 255 characters with Excel ODBC driver"

Hi Fazza

I concur with Oorang and PGC, the ADO approach will still have the same 255 character limit.

Click to expand...

I think I've explained mysef wrong. That's not what I meant. ADO/SQL is OK.

What I was trying to say is that the problem is not the ADO, you can use the ADO with SQL the usual way, it works OK. The problem is in the data source: excel worksheets, by default, behave like datatables with the 255 limit. ADO simply reads what is available.

One solution is to use ADOX to create in the excel specific fields in the worksheets that are like MEMO fiels and do not have that limit.

This is just done at the beginning. You then use ADO as usual and you don't have that limit.

In case someone needs it, I can try to modify what I have and create a simple example just to validate the concept.

Best regards
PGC

Thansk all for your replies - I'm goign to work on this over the next few days.

Thanks again