255 Character Limit when copying data

[tombrown]

Hi,

I have developed a solution to my report project using SQL queries (thanks to Fazza) - see http://www.mrexcel.com/board2/viewtopic.php?t=283369, but I am now left with the problem that any cell with more than 255 characters is truncated when I use the query to puall the data together.

Is there a way of avoiding this, while still maintaining the SQL approach to moving data around? (i.e. I know I can get round it by copying and pasting ranges, but that wont work for me)

My alternative is to limit all data to 255 characters and enforce using

 

[Oorang]

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.

 

[tombrown]

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 :

Connection:=Array(Array("ODBC;DSN=Excel Files;DBQ=D:\xyz.xls;DefaultDir=D:\A;DriverId=790;MaxB"), Array("ufferSize=2048;PageTimeout=5;")

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

 

[pgc01]

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

 

[Fazza]

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

 

[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"

 

[pgc01]

Hi Fazza

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

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

 

[tombrown]

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

Thanks again