By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Problem with large amount of characters in a VBA variant

P: n/a

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.
Jan 3 '08 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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 ***
Jan 3 '08 #2

P: n/a
On 3 jan, 18:29, Rich P <rpng...@aol.comwrote:
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 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 = oExcel_App.Workbooks.Add(xlWBATWorksheet)

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

Thank you.

Jan 3 '08 #3

P: n/a
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 ***
Jan 3 '08 #4

P: n/a
Rich P <rp*****@aol.comwrote in
news:47*********************@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

Jan 4 '08 #5

P: n/a
nartla <br**********@altran.comwrote 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

Jan 4 '08 #6

P: n/a
On 4 jan, 00:51, Bob Quintal <rquin...@sPAmpatico.cawrote:
nartla <bruno.bar...@altran.comwrote 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 texte des messages précédents -

- Afficher le texte des messages précédents -
Thank you for your help.

Jan 4 '08 #7

P: n/a
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 ***
Jan 4 '08 #8

P: n/a
Rich P <rp*****@aol.comwrote 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.
Jan 4 '08 #9

P: n/a
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 <rp*****@aol.comwrote in
news:47***********************@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

Jan 4 '08 #10

P: n/a
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 ***
Jan 4 '08 #11

P: n/a
Rich P <rp*****@aol.comwrote in
news:47*********************@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

Jan 5 '08 #12

This discussion thread is closed

Replies have been disabled for this discussion.