要求:能够把指定数据库的表内所有NULL替换为空!
能够把指定一定数量的数据库的表里的NULL替换为空!
我想到的方法是在表里查找NULL,然后UPDATA空值,但是循环结束条件不知道如何写,大虾教下吧!这样做只能一个字段一个字段的找,ACC里面是一条记录一条记录的找,这个又要如何做呢?
另外,如果我想一次性操作一定数量的数据库?
Option Explicit
Public DBCON As New ADODB.Connection
Public RsChange As New ADODB.Recordset
Public RsCount As New ADODB.Recordset
'----------------------------------------------------------------------------------------
Private Sub CountNum() '//获取记录数
DBCON.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\" & txtDBName.Text & ".mdb;Persist Security Info=False"
DBCON.Open
If RsCount.State = adStateClosed Then '//打开记录集
RsCount.Open "" & txtDBName.Text & "", DBCON, adOpenKeyset, adLockPessimistic, adCmdTable
End If
Dim count1 As Integer
count1 = RsCount.RecordCount
RsCount.Close
txtCount.Text = count1
DBCON.Close
End Sub
Private Sub DBopen()
DBCON.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\" & txtDBName.Text & ".mdb;Persist Security Info=False"
DBCON.Open
End Sub
'----------------------------------------------------------------------------------------
Public Function e1() '//phone
Dim i As Integer
Dim num As Integer
num = txtCount.Text
For i = 1 To num
Call Change
Next
End Function
Public Function e2() '//remark
Dim i As Integer
Dim num As Integer
num = txtCount.Text
For i = 1 To num
Call Change2
Next
End Function
Public Function e3() '//name
Dim i As Integer
Dim num As Integer
num = txtCount.Text
For i = 1 To num
Call Change3
Next
End Function
Public Function e4() '//address
Dim i As Integer
Dim num As Integer
num = txtCount.Text
For i = 1 To num
Call Change4
Next
End Function
'----------------------------------------------------------------------------------------
Private Sub cmdChange_Click()
Call CountNum
Call DBopen
Call e1
DBCON.Close
Call DBopen
Call e2
DBCON.Close
Call DBopen
Call e3
DBCON.Close
Call DBopen
Call e4
DBCON.Close
MsgBox "OK!"
End Sub
'----------------------------------------------------------------------------------------
Public Function Change() '//phone
On Error GoTo over
Dim StrSQL As String
StrSQL = "select * from " & txtDBName.Text & " where phone= '" & txtFind.Text & "'"
If RsChange.State = adStateClosed Then
RsChange.Open StrSQL, DBCON, adOpenDynamic, adLockOptimistic, adCmdText
End If
RsChange!phone = Empty
RsChange.Update
RsChange.MoveNext
over:
If Err.Number = 3021 Then
End If
End Function
Public Function Change2() '//remark
On Error GoTo over
Dim StrSQL As String
StrSQL = "select * from " & txtDBName.Text & " where remark= '" & txtFind.Text & "'"
If RsChange.State = adStateClosed Then
RsChange.Open StrSQL, DBCON, adOpenDynamic, adLockOptimistic, adCmdText
End If
RsChange!remark = Empty
RsChange.Update
RsChange.MoveNext
over:
If Err.Number = 3021 Then
End If
End Function
Public Function Change3() '//name
On Error GoTo over
Dim StrSQL As String
StrSQL = "select * from " & txtDBName.Text & " where name= '" & txtFind.Text & "'"
If RsChange.State = adStateClosed Then
RsChange.Open StrSQL, DBCON, adOpenDynamic, adLockOptimistic, adCmdText
End If
RsChange!Name = Empty
RsChange.Update
RsChange.MoveNext
over:
If Err.Number = 3021 Then
End If
End Function
Public Function Change4() '//address
On Error GoTo over
Dim StrSQL As String
StrSQL = "select * from " & txtDBName.Text & " where address= '" & txtFind.Text & "'"
If RsChange.State = adStateClosed Then
RsChange.Open StrSQL, DBCON, adOpenDynamic, adLockOptimistic, adCmdText
End If
RsChange!address = Empty
RsChange.Update
RsChange.MoveNext
over:
If Err.Number = 3021 Then
End If
End Function
我做的,不要笑啊,谁能教我更好的方法?