access空间不足 access提示内存不足

转载

数据小筑 2025-08-15 10:55:22

文章标签 access空间不足 access function microsoft sql 文章分类 架构后端开发

ゴガギグゲザジズヅデドポベプビパヴボペブピバヂダゾゼ

当字段内包含了这26个日文字符任意一个多个时,就会导致在执行SQL语句中包含了
[字段] like '%aaaaa%' 或 inStr(1,[字段],'aaaaa',1)>0
这样的查询时,毫无道理的出现了
"Microsoft JET Database Engine 错误 '80040e14' 内存溢出" 的错误
其他Jet SQL函数命令未作测试，大概与字符搜索定位匹配相关的都可能出错
搜索相关资料得知被微软工程师证实是Access的bug

1.

一个简单的方法是：
把如下的语句：
Sql = "select * From [tbname] Where Title like '%"&keyword;&"%' Order By id Desc"
换成：
Sql = "select * from [tbname] where InStr(1,LCase(Title),LCase('"&keyword;&"'),0)
0 order by id desc"

写一段代码把含有26个片假名的条目搜索出来，或者老老实实查找

删除条目即可

3.Wodig2.0版本，存在的搜索日文造成内存溢出问题解决

修改文件：cls_clall.asp
文件位置：./wolib文件夹下
修改第79行：有此修改过此文件之后，有可能行数就不对了。

查找：可用查找功能，查找“like”能够快速定位到修改位置。

sql = sql & " and src_webname like '%"& s_havving &"%' or src_url like '%" & s_havving & "%' or src_desc like '%" & s_havving & "%'"

替换为：

sql = sql & " and instr(1,lcase(src_webname),lcase('"& s_havving &"'),0) 
 
0 or instr(1,lcase(src_url),lcase('"& s_havving &"'),0) 
 
0 or instr(1,lcase(src_desc),lcase('"& s_havving &"'),0) 
 
0"

如果对程序代码不熟悉，可在些下载本站修改好的cls_clall.asp文件，替换原文件即可。
修改好的cls_clall.asp文件请到e迷网下载！

一简单有效的解决办法:
对这26个字符进行编码和解码,可能效率感觉不理想,测试下来问题不大,速度影响不是太大

编码:

Function Jencode(byVal iStr) 
 
if isnull(iStr) or isEmpty(iStr) then 
 
Jencode="" 
 
Exit function 
 
end if 
 
dim F,i,E 
 
' F=array("ゴ","ガ","ギ","グ","ゲ","ザ","ジ","ズ","ヅ","デ",_ 
 
' "ド","ポ","ベ","プ","ビ","パ","ヴ","ボ","ペ","ブ","ピ","バ",_ 
 
' "ヂ","ダ","ゾ","ゼ") 
 
E=array("Jn0;","Jn1;","Jn2;","Jn3;","Jn4;","Jn5;","Jn6;","Jn7;","Jn8;","Jn9;","Jn10;","Jn11;","Jn12;","Jn13;","Jn14;","Jn15;","Jn16;","Jn17;","Jn18;","Jn19;","Jn20;","Jn21;","Jn22;","Jn23;","Jn24;","Jn25;") 
 
F=array(chr(-23116),chr(-23124),chr(-23122),chr(-23120),_ 
 
chr(-23118),chr(-23114),chr(-23112),chr(-23110),_ 
 
chr(-23099),chr(-23097),chr(-23095),chr(-23075),_ 
 
chr(-23079),chr(-23081),chr(-23085),chr(-23087),_ 
 
chr(-23052),chr(-23076),chr(-23078),chr(-23082),_ 
 
chr(-23084),chr(-23088),chr(-23102),chr(-23104),_ 
 
chr(-23106),chr(-23108)) 
 
Jencode=iStr 
 
for i=0 to 25 
 
Jencode=replace(Jencode,F(i),E(i)) 
 
next 
 
End Function

解码:

Function Juncode(byVal iStr) 
 
if isnull(iStr) or isEmpty(iStr) then 
 
Juncode="" 
 
Exit function 
 
end if 
 
dim F,i,E 
 
' F=array("ゴ","ガ","ギ","グ","ゲ","ザ","ジ","ズ","ヅ","デ",_ 
 
' "ド","ポ","ベ","プ","ビ","パ","ヴ","ボ","ペ","ブ","ピ","バ",_ 
 
' "ヂ","ダ","ゾ","ゼ") 
 
E=array("Jn0;","Jn1;","Jn2;","Jn3;","Jn4;","Jn5;","Jn6;","Jn7;","Jn8;","Jn9;","Jn10;","Jn11;","Jn12;","Jn13;","Jn14;","Jn15;","Jn16;","Jn17;","Jn18;","Jn19;","Jn20;","Jn21;","Jn22;","Jn23;","Jn24;","Jn25;") 
 
F=array(chr(-23116),chr(-23124),chr(-23122),chr(-23120),_ 
 
chr(-23118),chr(-23114),chr(-23112),chr(-23110),_ 
 
chr(-23099),chr(-23097),chr(-23095),chr(-23075),_ 
 
chr(-23079),chr(-23081),chr(-23085),chr(-23087),_ 
 
chr(-23052),chr(-23076),chr(-23078),chr(-23082),_ 
 
chr(-23084),chr(-23088),chr(-23102),chr(-23104),_ 
 
chr(-23106),chr(-23108)) 
 
Juncode=iStr 
 
for i=0 to 25 
 
Juncode=replace(Juncode,E(i),F(i))'□ 
 
next 
 
End Function

注意,如果直接使用字符不方便(windows还没装日文支持),注释掉的部分提供有 chr(-23804) ..这样的定义

这样
1.
表单输入保存时,使用Jencode()将这26个字符先编码再保存(为什么是这26个字符,经过全部测试87个平假名89个片假名最终认定的)
如
ゴ即 chr(-23116) 编码为 Jn1;
2.
显示时,则使用 Juncode() 函数进行解码,还原日文片假名显示
3.
搜索关键字,也要使用 Jencode() 进行编码后再放入 like里
where [Topic] like '%Jencode(kewwords)%' 使用
才能保证搜索的值和编码过的数据库字段内容匹配

==================================
PS:
也可以使用正则表达式来改写上面的两个函数,或许效率还要更高些
再就是如果压根不使用日文,也不需要搜索日文,则解码部分可以不用,保存数据实直接把这26个片假名字符替换为空字符或任一字符,比如"□"

附:
----------------------------
平假名87个 asc值
-23391 --> -23316
unicode 3040-309F

ぁあぃいぅうぇえぉお
かがきぎくぐけげこご
さざしじすずせぜそぞ
ただちぢっつづてでと
どなにぬねのはばぱひ
びぴふぶぷへべぺほぼ
ぽまみむめもゃやゅゆ
ょよらりるれろゎわゐ
ゑをん゛゜ゝゞ
------------------------------
片假名89个 asc值
-23135 -> -23059
unicode 30A0-30FF

ァアィイゥウェエォオ
カガキギクグケゲコゴ
サザシジスズセゼソゾ
タダチヂッツヅテデト
ドナニヌネノハバパヒ
ビピフブプヘベペホボ
ポマミムメモャヤュユ
ョヨラリルレロヮワヰ
ヱヲンヴヵヶーヽヾ

5 .

今天上班的时候，同事晓霞在查看论坛的时候结果搜索数据库时出现“内存溢出”错误。开始俺也没有一个明确的答案，于是乎Kiven开始上网搜索寻求答案未果。最后才发现这就是传说中的“日文 26 个片假名（ゴガギアゲザジズヅデドポベプビパヴボペブピバヂダゾゼ）导致 Access 搜索出现内存溢出（Microsoft JET Database Engine 错误 '80040e14' 内存溢出）”的 Bug，研究了半天，最后终于还算是解决了。撇开针对 LBS^2 的 http://www.ronnier.com/article.asp?id=31，再看看归纳出罪魁祸首为 26 个日文片假名的 http://www.blueidea.com/bbs/NewsDetail.asp?id=1768538，再借用 Loveyuki 的图：http://www.loveyuki.com/blogview.asp?logID=621&cateID=4：

最后，本人决定用最简单的笨方法：

Function EncodeJP(StrHTML) 
 
If Not StrHTML="" Then 
 
StrHTML=Replace(StrHTML,"ガ","ガ") 
 
StrHTML=Replace(StrHTML,"ギ","ギ") 
 
StrHTML=Replace(StrHTML,"ア","ア") 
 
StrHTML=Replace(StrHTML,"ゲ","ゲ") 
 
StrHTML=Replace(StrHTML,"ゴ","ゴ") 
 
StrHTML=Replace(StrHTML,"ザ","ザ") 
 
StrHTML=Replace(StrHTML,"ジ","ジ") 
 
StrHTML=Replace(StrHTML,"ズ","ズ") 
 
StrHTML=Replace(StrHTML,"ゼ","ゼ") 
 
StrHTML=Replace(StrHTML,"ゾ","ゾ") 
 
StrHTML=Replace(StrHTML,"ダ","ダ") 
 
StrHTML=Replace(StrHTML,"ヂ","ヂ") 
 
StrHTML=Replace(StrHTML,"ヅ","ヅ") 
 
StrHTML=Replace(StrHTML,"デ","デ") 
 
StrHTML=Replace(StrHTML,"ド","ド") 
 
StrHTML=Replace(StrHTML,"バ","バ") 
 
StrHTML=Replace(StrHTML,"パ","パ") 
 
StrHTML=Replace(StrHTML,"ビ","ビ") 
 
StrHTML=Replace(StrHTML,"ピ","ピ") 
 
StrHTML=Replace(StrHTML,"ブ","ブ") 
 
StrHTML=Replace(StrHTML,"ブ","ブ") 
 
StrHTML=Replace(StrHTML,"プ","プ") 
 
StrHTML=Replace(StrHTML,"ベ","ベ") 
 
StrHTML=Replace(StrHTML,"ペ","ペ") 
 
StrHTML=Replace(StrHTML,"ボ","ボ") 
 
StrHTML=Replace(StrHTML,"ポ","ポ") 
 
StrHTML=Replace(StrHTML,"ヴ","ヴ") 
 
End If 
 
EncodeJP=StrHTML 
 
End Function

经测试——居然OK了！
===========================================
---------------------------------------------------
ガギグゲゴザジズゼゾダヂヅデドバビブベボパピプペポヴ
---------------------------------------------------

上面的日文会导致access在搜索时出错，就是内存溢出，在sql下不会有这种问题，要在access下解决的话在储存和读取时用下面的函数

储存

Function jp2code(CodeStr) 
 
jp2code=Replace(Replace(Replace(Replace(Replace(CodeStr,"ガ","j30AC"),"ギ","j30AE"),"グ","j30B0"),"ゲ","j30B2"),"ゴ","j30B4") 
 
jp2code=Replace(Replace(Replace(Replace(Replace(jp2code,"ザ","j30B6"),"ジ","j30B8"),"ズ","j30BA"),"ゼ","j30BC"),"ゾ","j30BE") 
 
jp2code=Replace(Replace(Replace(Replace(Replace(jp2code,"ダ","j30C0"),"ヂ","j30C2"),"ヅ","j30C5"),"デ","j30C7"),"ド","j30C9") 
 
jp2code=Replace(Replace(Replace(Replace(Replace(jp2code,"バ","j30D0"),"ビ","j30D3"),"ブ","j30D6"),"ベ","j30D9"),"ボ","j30DC") 
 
jp2code=Replace(Replace(Replace(Replace(Replace(jp2code,"パ","j30D1"),"ピ","j30D4"),"プ","j30D7"),"ペ","j30DA"),"ポ","j30DD") 
 
jp2code=Replace(jp2code,"ヴ","j30F4") 
 
End Function

读取

[code] 
 
Function code2jp(CodeStr) 
 
code2jp=Replace(Replace(Replace(Replace(Replace(CodeStr,"j30AC","ガ"),"j30AE","ギ"),"j30B0","グ"),"j30B2","ゲ"),"j30B4","ゴ") 
 
code2jp=Replace(Replace(Replace(Replace(Replace(code2jp,"j30B6","ザ"),"j30B8","ジ"),"j30BA","ズ"),"j30BC","ゼ"),"j30BE","ゾ") 
 
code2jp=Replace(Replace(Replace(Replace(Replace(code2jp,"j30C0","ダ"),"j30C2","ヂ"),"j30C5","ヅ"),"j30C7","デ"),"j30C9","ド") 
 
code2jp=Replace(Replace(Replace(Replace(Replace(code2jp,"j30D0","バ"),"j30D3","ビ"),"j30D6","ブ"),"j30D9","ベ"),"j30DC","ボ") 
 
code2jp=Replace(Replace(Replace(Replace(Replace(code2jp,"j30D1","パ"),"j30D4","ピ"),"j30D7","プ"),"j30DA","ペ"),"j30DD","ポ") 
 
code2jp=Replace(code2jp,"j30F4","ヴ") 
 
End Function

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。