删除access数据

ccchot 2011-11-20 01:33:08

access 数据库BSS中的字节“序号”为文本
数据内容: 第1行 wwwww
第2行 00000
第3行 111
第4行
第5行 321

下列两个方法都是删除非正常数值数据，方法判断val（序号）=0，但都出错
方法一：
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111\bss.mdb; Persist Security Info=False"
conn.Open
rs.Open "select * from aaa where val(序号)=0", conn, adOpenKeyset, adLockOptimistic
rs.Delete
rs.Update
MsgBox "删除成功！"

rs.Close
conn.Close

方法二：
Dim adocn As New ADODB.Connection

adocn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111\bss.mdb; Persist Security Info=False"
adocn.Open

adocn.Execute "delete from aaa where val(序号)=0 "
adocn.Close
Set adocn = Nothing

...全文

63 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

生命密码 2011-11-20

打赏
举报

delete from aaa where not isnumeric(序号)

或delete from aaa where isnumeric(序号)=false

worldy 2011-11-20

打赏
举报

[Quote=引用 4 楼 ccchot 的回复:]
adocn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111\bss.mdb; Persist Security Info=False"
adocn.Open
adocn.Execute "delete aaa.* from from aaa where not isnumeric(序号)"

提……
[/Quote]

晕，多了一个from
==>
delete 表2.* from 表2 where (not isnumeric(序号))

ccchot 2011-11-20

打赏
举报

Dim conn As ADODB.Connection

  Dim rs As ADODB.Recordset



Set conn = New ADODB.Connection

  Set rs = New ADODB.Recordset

  conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111\bss.mdb; Persist Security Info=False"

  conn.Open

  rs.Open "select * from aaa where isnumeric(序号)=false", conn, adOpenKeyset, adLockOptimistic

  rs.Delete

  rs.Update

  rs.Close

  conn.Close

运行正常，但数据无任何变化

ccchot 2011-11-20

打赏
举报

adocn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111\bss.mdb; Persist Security Info=False"
adocn.Open
adocn.Execute "delete aaa.* from from aaa where not isnumeric(序号)"

提示“from子句语法错误”