Hello,

I work on an app developped with Access/VBA and communicating with a
SQL Server database via ODBC.

My problem is that I have to manage a big string which can be 0 to
1024 characters long. The string is correctly stored in the Access and
SQL Server tables (Memo field for Access, and varchar(1024) for SQL
Server).

I also need to write this string in an Excel report created by the
application. For this purpose, I store the huge string in a variant
VBA variable, but when looking at the variant with the debugger, I
find that it has got only 255 characters (when the string is 1024
characters long). In the Excel report, I also get only the 255 first
chars.

Is this a normal behaviour ? I searched the internet but I didn't find
information about the max string length that can be stored in a
variant in VBA ?

Thank you for your help.

Greetings,

The problem is with the ODBC connection. With an ODBC connection to an
Access MDB you will only get 255 chars per field max for Text (varchar)
columns. The rest of the characters get truncated. The way to get
around this limitation with ODBC is to retrieve the desired values using
ADO (in VBA). Go to Tools/References and make a reference to the
highest version of

"Microsoft ActiveX Data Objects 2.X Library"

then, in a Standard Code module (or a Form code module)

Sub GetLongString()
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=yourDB;Trusted_Connecti on=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText
cmd.CommandText = "Select fldx from tblX Where something = 'something'"
Set RS = cmd.Execute
Debug.Print RS(0)
cmd.ActiveConnection.Close
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***

On 3 jan, 18:29, Rich P <rpng... (AT) aol (DOT) com> wrote:
Hello,

Thank you for your answer. I am not sure the problem comes from the
ODBC connexion, as the data in the Excel report is sent from the
Access application via Automation, after creating an Excel report via
the following code :

Set oEXCEL_EXP = oExcel_App.Workbooks.Add(xlWBATWorksheet)

Is there a possibility that the data is truncated during the
communication process between Access and Excel ?

Thank you.

No. Whatever data you pass from Access to Excel will remain the same.
The problem is that the automation code is reading the data from an ODBC
table and the data is being truncated at the ODBC table.

One thing you could do to simplify this data transfer is to write the
same ADO code I posted in Excel in Excel's visual Basic Editor. Make
the same reference to

Microsoft ActiveX Data Objects 2.X Library

and add the code to a code module. This will be a Macro in Excel.

cmd.CommandText = "Select * from tblx"

the ADO command object will use the same connection string to your sql
server as the connection string in Access.

So Access is basically the middle man for this data transfer. You are
really transferring data from the Sql Server to Excel. It is much
simpler to bypass the middle man (Access). Even if your situation would
not be conducive to using Excel programming I would give that a try so
you can see how it works. Here is some sample code that you would use
in Excel:

Sub GetDataFromSqlServer()
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
Dim rng As Range, i As Integer
Dim strSql As String, WkBk As Workbook, sht As Worksheet

strSql = "SELECT * FROM yourTbl WHERE something = 'something'"

Set WkBk = ActiveWorkbook
Set sht = WkBk.ActiveSheet

Set rng = sht.Range("A1:BH1")

cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=yourDB;Trusted_Connecti on=Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = strSql
DoEvents
Set RS = cmd.Execute
sht.Range("A2").CopyFromRecordset RS

For i = 0 To RS.Fields.Count - 1
rng(1, i + 1) = RS(i).Name
Next
rng.Font.Bold = True
rng.Font.ColorIndex = 5
End Sub

The connection string here is based on Windows authentication. If this
connection string doesn't work then try using your UserID and Password
as follows

cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourServer;Database=yourDB;uid=steve;pwd=te st"



Rich

*** Sent via Developersdex http://www.developersdex.com ***

Rich P <rpng123 (AT) aol (DOT) com> wrote in
news:477d3471$0$505$815e3792 (AT) news (DOT) qwest.net:

Absolutely Bull$#it.
Microsoft acknowledge the problem and proveide some workarounds at
http://support.microsoft.com/kb/208801


Rich, go away. Don't go away mad, jut go away.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

nartla <bruno.barral (AT) altran (DOT) com> wrote in
news:0a098301-faf3-4ef8-be89-4e1f0dc0c2c4
@u10g2000prn.googlegroups.co
m:

See the Microsoft Knowledgebase article at
http://support.microsoft.com/kb/208801


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

On 4 jan, 00:51, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:
Thank you for your help.