Problem with large amount of characters in a VBA variant

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.

Re: Problem with large amount of characters in a VBA variant

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_Connection=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 ***

Re: Problem with large amount of characters in a VBA variant

On 3 jan, 18:29, Rich P wrote:
> Greetings,
>
> The problem is with the ODBC connection. =A0With an ODBC connection to an
> Access MDB you will only get 255 chars per field max for Text (varchar)
> columns. =A0The rest of the characters get truncated. =A0The way to get
> around this limitation with ODBC is to retrieve the desired values using
> ADO (in VBA). =A0Go 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 =3D "Provider=3DSQLOLEDB; Data
> Source=3DyourServer;Database=3DyourDB;Trusted_Connection=3DY es"
> cmd.ActiveConnection.CursorLocation =3D adUseClient
> cmd.CommandTimeout =3D 600
> cmd.CommandType =3D adCmdText
> cmd.CommandText =3D "Select fldx from tblX Where something =3D 'something'=
"
> Set RS =3D cmd.Execute
> Debug.Print RS(0)
> cmd.ActiveConnection.Close
> End Sub
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

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 =3D oExcel_App.Workbooks.Add(xlWBATWorksheet)

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

Thank you.

Re: Problem with large amount of characters in a VBA variant

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_Connection=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=test"



Rich

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

Re: Problem with large amount of characters in a VBA variant

Rich P wrote in
news:477d3471$0$505$815e3792@news.qwest.net:

> 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.

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

Re: Problem with large amount of characters in a VBA variant

nartla wrote in
news:0a098301-faf3-4ef8-be89-4e1f0dc0c2c4
@u10g2000prn.googlegroups.co
m:

>
> 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.

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

Re: Problem with large amount of characters in a VBA variant

On 4 jan, 00:51, Bob Quintal wrote:
> nartla wrote in
> news:0a098301-faf3-4ef8-be89-4e1f0dc0c2c4
> @u10g2000prn.googlegroups.co
> m:
>
>
>
>
>
>
>
> > 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.
>
> See the Microsoft Knowledgebase article athttp://support.microsoft.com/kb/=
208801
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com- Masquer le t=
exte des messages pr=E9c=E9dents -
>
> - Afficher le texte des messages pr=E9c=E9dents -

Thank you for your help.

Re: Problem with large amount of characters in a VBA variant

Bob,

I have read several of your posts, and you do seem to posses proficiency
in the VBA world, but you have also stated that your proficiency is
limited to VBA. I am proficient in VBA and also quite proficient in
VB.Net and C#, and even though some of my suggestions may go from point
A to point D (versus A to B to C to D) they are valid suggestions. I
post in this newsgroup to stay current with VBA since most of my
projects consist of migrating VBA projects to the enterprise
environment.

Yes, it is a little perturbing when those of you with expertise limited
to one field have to be critical with those of us who have extensively
more experience and expertise in a number of fields, but I guess it goes
with the territory.

I have already trained myself to not offer .Net solutions in this NG
even though .Net significantly reduces the amount of sphaghetti code
required for solutions in VBA. In this particular post, the poster is
transferring data from Sql SErver to Excel via Access and is having
problems. I am suggesting to bypass Access altogether as an experiment.
Be realistic -- Access is a micro RDBMS with all the limitations that
come with being Micro (thus I have to migrate all sorts of VBA projects
to .Net).

Rich

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

Re: Problem with large amount of characters in a VBA variant

Rich P wrote in news:477e8793$0$10309$815e3792
@news.qwest.net:

> Bob,
>
> I have read several of your posts, and you do seem to posses proficiency
> in the VBA world, but you have also stated that your proficiency is
> limited to VBA. I am proficient in VBA and also quite proficient in
> VB.Net and C#, and even though some of my suggestions may go from point
> A to point D (versus A to B to C to D) they are valid suggestions. I
> post in this newsgroup to stay current with VBA since most of my
> projects consist of migrating VBA projects to the enterprise
> environment.
>
> Yes, it is a little perturbing when those of you with expertise limited
> to one field have to be critical with those of us who have extensively
> more experience and expertise in a number of fields, but I guess it goes
> with the territory.
>
> I have already trained myself to not offer .Net solutions in this NG
> even though .Net significantly reduces the amount of sphaghetti code
> required for solutions in VBA. In this particular post, the poster is
> transferring data from Sql SErver to Excel via Access and is having
> problems. I am suggesting to bypass Access altogether as an experiment.
> Be realistic -- Access is a micro RDBMS with all the limitations that
> come with being Micro (thus I have to migrate all sorts of VBA projects
> to .Net).

I'm with Bob. IMO you have little knowledge, and less skill, but you post
here frequently in an authoritative manner. As your posts are both verbose
and illogical, it often is too much trouble to refute them.
I think you should limit your posts to brief suggestions for specific
questions.

And it would be fine with me if you filed Developersdex in the appropriate
aperture.

Re: Problem with large amount of characters in a VBA variant

I think you guys are missing my point. I don't live off of this NG. I
post here to help people out. In return I stay current in my
proficiency. I have whatever proficiency I have. But in order to
maintain my proficiency in the various areas I have to do it. Meaning -
most of my work right now is at the enterprise level, and I am really
not working that much with VBA anymore even though I am migrating VBA
project after VBA project to .Net. So yes, I get a little rusty in some
Access stuff - mostly in the more fundamental items - stuff that is
native to Access only (or Microsoft Office only). Whatever I post, is
stuff that works for me. But it is possible that someone may have a
slightly different setup than me - say using a Mac or Linux or something
(I am a Windows PC guy). My appologies if some of my suggestions are
not consistent with the problem at hand. But they are just suggestions
- you know? Like in Free suggestions worth whatever you give them.
Free! I am not being paid. If it helps someone out, that is the goal,
if it doesn't help - then Oh well.

As for the authoritative manner, whatever I do in programming - I am
still doing it, so it must work because I have been doing it for a while
(more than 10 years). If it didn't work I would not be programming.
And 10+ years should be enough time for someone to figure out if what I
do is actually working.

P.S. I post in this NG because it is relaxing (I'm on my break right
now).



Rich

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

Re: Problem with large amount of characters in a VBA variant

Rich, being proficient would mean refraining from posting abso;ute
hogwash, as you did to trigger my response to your post.

You said " No. Whatever data you pass from Access to Excel will
remain the same." Microsoft themselves say otherwise. That's a lack
of proficiency on your part.

You said "the data is being truncated at the ODBC table." That is an
incorrect statement. As someone who deals with memo fields in tables
upsized to text in SQL Server I will attest to that.

What I ask is you stop posting stuff that misleads those people
looking for a solution to their problem.

Sincerely,

Bob Quintal.


Rich P wrote in
news:477e8793$0$10309$815e3792@news.qwest.net:

> Bob,
>
> I have read several of your posts, and you do seem to posses
> proficiency in the VBA world, but you have also stated that your
> proficiency is limited to VBA. I am proficient in VBA and also
> quite proficient in VB.Net and C#, and even though some of my
> suggestions may go from point A to point D (versus A to B to C to
> D) they are valid suggestions. I post in this newsgroup to stay
> current with VBA since most of my projects consist of migrating
> VBA projects to the enterprise environment.
>
> Yes, it is a little perturbing when those of you with expertise
> limited to one field have to be critical with those of us who have
> extensively more experience and expertise in a number of fields,
> but I guess it goes with the territory.
>
> I have already trained myself to not offer .Net solutions in this
> NG even though .Net significantly reduces the amount of sphaghetti
> code required for solutions in VBA. In this particular post, the
> poster is transferring data from Sql SErver to Excel via Access
> and is having problems. I am suggesting to bypass Access
> altogether as an experiment. Be realistic -- Access is a micro
> RDBMS with all the limitations that come with being Micro (thus I
> have to migrate all sorts of VBA projects to .Net).
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***



--
Bob Quintal

PA is y I've altered my email address.

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

Re: Problem with large amount of characters in a VBA variant

My experience (my personal experience limited only to me) with ODBC
tables and Access has consistently been that table fields with more than
255 chars get truncated in Access (not in the sql server table). So I
can either only read 255 chars at most for a given record that contains
more than 255 chars or I get an error for that record. That has been my
personal experience without researching any further. It sounded like
the individual posting here originally was having the same problem as I
had with ODBC. I offered the suggestion that I did.

And yes, perhaps Access can tweak data that it passes to excel, but not
if you pass it with ADO. If you use ADO -- wysiwyg between Access data
and the same data in Excel. TransferSpreadsheet may tweak stuff. Thus,
I don't use TransferSpreadsheet. But since the issue at hand was the
source data resides in the Sql Server and needs to end up in Excel --
the suggestion I then offered was to bypass Access altogether since that
is where the problem existed. The goal here is to not re-invent the
wheel (with all kinds of sphaghetti code -- I ran out of tomato sauce
from doing that too many times) - and honestly, using ADO between Access
and Excel is more hassle than I care for anymore - although it is
reliable and good performance (now I just use a simple .Net app I put
together which tansfers data from any datasource to Excel very easily)

Actually, I wrote a custom .Net dll to perform this data transfer from
Access to Excel seamlessly, but Access would not allow a data read from
the .Net dll (although Excel does allow the same .Net dll to read the
data from Access). I was told that it was some security issue with
Access - my guess is that the data read just isn't supported in Access
at this time for the .Net dll (maybe it would work with Access 2007 - I
am using Acc2003). Anyway, I would have offered this suggestion, but
slightly out of scope for this NG.

Bottom line, I try to steer myself (and others) from having to re-invent
the wheel (since I have been there so many times in the past). Perhaps
the problem you note with me is not in misleading people, but in how I
convey my ideas. Apparently, we dont all think alike. But I will heed
your suggestion and try to focus more on keeping it simple (I will try.)


Rich

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

Re: Problem with large amount of characters in a VBA variant

Rich P wrote in
news:477ec13b$0$494$815e3792@news.qwest.net:

> My experience (my personal experience limited only to me) with
> ODBC tables and Access has consistently been that table fields
> with more than 255 chars get truncated in Access (not in the sql
> server table).

I suppose there is a first time for everything :-)
I've been writing software since 1970, and using Access VBA as my
primary tool since 1995, with SQL server back ends since 1999. I've
never heard of this before.

So I can either only read 255 chars at most for a
> given record that contains more than 255 chars or I get an error
> for that record.

You must be using some funky way of linking to the back end.

That has been my personal experience without
> researching any further. It sounded like the individual posting
> here originally was having the same problem as I had with ODBC. I
> offered the suggestion that I did.
>
> And yes, perhaps Access can tweak data that it passes to excel,
> but not if you pass it with ADO. If you use ADO -- wysiwyg

ADO? It works perfectly well with a DAO recordset as well.

> between Access data and the same data in Excel.
> TransferSpreadsheet may tweak stuff. Thus, I don't use
> TransferSpreadsheet. But since the issue at hand was the source
> data resides in the Sql Server and needs to end up in Excel -- the
> suggestion I then offered was to bypass Access altogether since
> that is where the problem existed. The goal here is to not
> re-invent the wheel (with all kinds of sphaghetti code -- I ran
> out of tomato sauce from doing that too many times) - and
> honestly, using ADO between Access and Excel is more hassle than I
> care for anymore - although it is reliable and good performance
> (now I just use a simple .Net app I put together which tansfers
> data from any datasource to Excel very easily)
>
> Actually, I wrote a custom .Net dll to perform this data transfer
> from Access to Excel seamlessly, but Access would not allow a data
> read from the .Net dll (although Excel does allow the same .Net
> dll to read the data from Access). I was told that it was some
> security issue with Access - my guess is that the data read just
> isn't supported in Access at this time for the .Net dll (maybe it
> would work with Access 2007 - I am using Acc2003). Anyway, I
> would have offered this suggestion, but slightly out of scope for
> this NG.
>
> Bottom line, I try to steer myself (and others) from having to
> re-invent the wheel (since I have been there so many times in the
> past). Perhaps the problem you note with me is not in misleading
> people, but in how I convey my ideas. Apparently, we dont all
> think alike. But I will heed your suggestion and try to focus
> more on keeping it simple (I will try.)
>
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
Rich, it's ok to steer others away from reinventing the wheel, But
when you try to help others by suggesting the square wheels you
invented are all that works, that's not really helping them, which
is the aim of this newsgroup.

Go in peace.


--
Bob Quintal

PA is y I've altered my email address.

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