VBA变体中存在大量字符的问题 [英] Problem with large amount of characters in a VBA variant
问题描述
您好，
我使用Access / VBA开发的应用程序并与
SQL Server数据库进行通信通过ODBC。
我的问题是我必须管理一个大字符串，它可以是0到
1024个字符长。该字符串正确存储在Access和
SQL Server表中（Access的备注字段和SQL的varchar（1024）
服务器）。
我还需要在
应用程序创建的Excel报告中写入此字符串。为此，我将巨大的字符串存储在变量
VBA变量中，但是当使用调试器查看变量时，我发现它只有255个字符（当字符串为1024
个字符长）。在Excel报告中，我也只得到255个第一个
字符。
这是正常行为吗？我搜索了互联网，但我没有找到
关于可以存储在VBA的
变种中的最大字符串长度的信息？
感谢您的帮助。
问候，
问题在于ODBC连接。使用ODBC连接到
Access MDB，对于Text（varchar）
列，每个字段最多只能获得255个字符。其余的字符被截断。使用ODBC获得围绕此限制的方法是使用
ADO（在VBA中）检索所需的值。转到工具/参考，并参考
最高版本
" Microsoft ActiveX Data Objects 2.X Library"
然后，在标准代码模块（或表单代码模块）中
Sub GetLongString（）
Dim cmd As New ADODB .Command，RS As New ADODB.Recordset
cmd.ActiveConnection =" Provider = SQLOLEDB;数据
Source = yourServer; Database = yourDB; Trusted_Connecti on = Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText
cmd.CommandText ="从tblX中选择fldx其中某些东西=''某事''"
Set RS = cmd.Execute
Debug.Print RS（0）
cmd.ActiveConnection.Close
End Sub
Rich
***通过开发人员指南发送http://www.developersdex.com ***
3月1日，18：29，Rich P< rpng ... @ aol。 comwrote：
问候，
问题在于ODBC连接。 *使用ODBC连接到
访问MDB，对于Text（varchar）
列，每个字段最多只能获得255个字符。 *其余字符被截断。 *使用ODBC获取
围绕此限制的方法是使用
ADO（在VBA中）检索所需的值。 *转到工具/参考，并参考
最高版本
" Microsoft ActiveX Data Objects 2.X Library"
然后，在标准代码模块（或表单代码模块）中
Sub GetLongString（）
Dim cmd As New ADODB.Command，RS As New ADODB.Recordset
cmd.ActiveConnection =" Provider = SQLOLEDB;数据
Source = yourServer; Database = yourDB; Trusted_Connecti on = Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 600
cmd.CommandType = adCmdText
cmd.CommandText ="从tblX中选择fldx其中某些东西=''某事''"
Set RS = cmd.Execute
Debug.Print RS（0）
cmd.ActiveConnection.Close
End Sub
Rich
***通过Developersdexhttp：//www.developersdex.com***发送
你好，
谢谢你的回答。我不确定问题来自
ODBC连接，因为Excel报告中的数据是通过自动化从
访问应用程序通过自动化发送的
以下代码：
设置oEXCEL_EXP = oExcel_App.Workbooks.Add（xlWBATWorksheet）
有没有在Access和Excel之间的
通信过程中数据被截断的可能性？
谢谢。
否。从Access到Excel传递的数据将保持不变。
问题是自动化代码是从ODBC
表读取数据并且数据被截断在ODBC表上。
你可以做的一件事就是简化这个数据传输，就是把我在Excel中发布的相同的ADO代码写在Excel中''视觉基本编辑器。使
对
Microsoft ActiveX数据对象2.X库提供相同的参考
并将代码添加到代码模块。这将是Excel中的一个宏。
cmd.CommandText =" select * from tblx"
ADO命令对象将使用与您的sql相同的连接字符串
服务器作为Access中的连接字符串。
因此Access基本上是此数据传输的中间人。你是真的将数据从Sql Server传输到Excel。
。绕过中间人（Access）更简单.B
更简单。即使你的情况不会有利于使用Excel编程，我也会试一试。
你可以看到它是如何工作的。下面是一些示例代码，您将在Excel中使用
：
Sub GetDataFromSqlServer（）
Dim cmd作为新的ADODB。命令，RS作为新的ADODB.Recordset
Dim rng作为范围，我作为整数
Dim strSql As String，WkBk As Workbook，sht As Worksheet
strSql =" SELECT * FROM yourTbl WHERE something =''something''"
设置WkBk = ActiveWorkbook
设置sht = WkBk.ActiveSheet
设置rng = sht.Range（" A1：BH1"）
Source = yourServer; Database = yourDB; Trusted_Connecti on = Yes"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = strSql
DoEvents
设置RS = cmd.Execute
sht.Range（" A2）。CopyFromRecordset RS
对于i = 0到RS.Fields.Count - 1
rng（1，i + 1）= RS（i ）.Name
下一页
rng.Font.Bold = True
rng.Font.ColorIndex = 5
End Sub
此处的连接字符串基于Windows身份验证。如果这个
连接字符串不起作用，那么尝试使用你的用户ID和密码
如下
cmd.ActiveConnection = "提供商= SQLOLEDB;数据
Source = yourServer; Database = yourDB; uid = steve; pwd = te st"
Rich
***通过开发人员发送的指南 http://www.developersdex.com ***
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...@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.
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 ***
这篇关于VBA变体中存在大量字符的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
