vba MS Access：如何绕过/抑制错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/277340/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MS Access: How to bypass/suppress an error?
提问by tksy
I'm executing a query like this
我正在执行这样的查询
select field from table;
In that query, there is a loop running on many tables. So, if the field is not present in a table I get a
在该查询中，有一个循环在许多表上运行。因此，如果该字段不存在于表中，我会得到一个
Runtime Error 3061
运行时错误 3061
How can I by pass this error such as that on this error flow should go to another point?
我怎样才能绕过这个错误，比如这个错误流应该转到另一个点？
This is the code I have recently after going through this forum.
这是我最近通过这个论坛后得到的代码。
Option Explicit
Private Sub UpdateNulls()
Dim rs2 As DAO.Recordset
Dim tdf As DAO.TableDef
Dim db As Database
Dim varii As Variant, strField As String
Dim strsql As String, strsql2 As String, strsql3 As String
Dim astrFields As Variant
Dim intIx As Integer
Dim field As Variant
Dim astrvalidcodes As Variant
Dim found As Boolean
Dim v As Variant
Open "C:\Documents and Settings\Desktop\testfile.txt" For Input As #1
varii = ""
Do While Not EOF(1)
Line Input #1, strField
varii = varii & "," & strField
Loop
Close #1
astrFields = Split(varii, ",") 'Element 0 empty
For intIx = 1 To UBound(astrFields)
'Function ListFieldDescriptions()
Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
Dim rs As ADODB.Recordset, rs3 As ADODB.Recordset
Dim connString As String
Dim SelectFieldName
Set cn = CurrentProject.Connection
SelectFieldName = astrFields(intIx)
Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, Empty, SelectFieldName))
'Show the tables that have been selected '
While Not rs.EOF
'Exclude MS system tables '
If Left(rs!Table_Name, 4) <> "MSys" Then
strsql = "Select t.* From [" & rs!Table_Name & "] t Inner Join 01UMWELT On t.fall = [01UMWELT].fall Where [01UMWELT].Status = 4"
End If
Set rs3 = CurrentDb.OpenRecordset(strsql)
'End Function
strsql2 = "SELECT label.validcode FROM variablen s INNER JOIN label ON s.id=label.variablenid WHERE varname='" & astrFields(intIx) & "'"
Set db = OpenDatabase("C:\Documents and Settings\Desktop\Codebook.mdb")
Set rs2 = db.OpenRecordset(strsql2)
With rs2
.MoveLast
.MoveFirst
astrvalidcodes = rs2.GetRows(.RecordCount)
.Close '
End With
With rs3
.MoveFirst
While Not rs3.EOF
found = False
For Each v In astrvalidcodes
If v = .Fields(0) Then
found = True
Debug.Print .Fields(0)
Debug.Print .Fields(1)
Exit For
End If
Next
If Not found Then
msgbox "xxxxxxxxxxxxxxxx"
End If
End If
.MoveNext
Wend
End With
On Error GoTo 0 'End of special handling
Wend
Next intIx
End Sub
I'm getting a
我得到一个
Type Mismatch Runtime Error
类型不匹配运行时错误
in Set rs3 = CurrentDb.OpenRecordset(strsql)
在 Set rs3 = CurrentDb.OpenRecordset(strsql)
I guess I'm mixing up adoand daobut I'm not certainly sure where it is.
我想我搞混了ado，dao但我不确定它在哪里。
回答by Tomalak
Use the On Errorstatement that VBA supplies:
使用On ErrorVBA 提供的语句：
Sub TableTest
On Error Goto TableTest_Error
' ...code that can fail... '
Exit Sub
:TableTest_Error
If Err.Number = 3061 Then
Err.Clear()
DoSomething()
Else
MsgBox Err.Description ' or whatever you find appropriate '
End If
End Sub
Alternatively, you can switch off automatic error handling (e.g. breaking execution and displaying an error message) on a line-by-line basis:
或者，您可以逐行关闭自动错误处理（例如中断执行并显示错误消息）：
Sub TableTest
' ... fail-safe code ... '
On Error Resume Next
' ...code that can fail... '
If Err.Number = 3061 Then
Err.Clear()
DoSomething()
Else
MsgBox Err.Description
End If
On Error Goto 0
' ...mode fail-safe code... '
End Sub
There are these statements available:
有这些语句可用：
On Error Resume Nextswitches off VBA-integrated error handling (message box etc.) completely, execution simply resumes on the next line. Be sure to check for an error very early after you've used that, as a dangling error can disrupt the normal execution flow. Clear the error as soon as you caught it to prevent that.On Error Goto <Jump Label>resumes execution at a given label, primarily used for per-function error handlers that catch all sorts of errors.On Error Goto <Line Number>resumes at a given line number. Stay away from that, it's not useful, even dangerous.On Error Goto 0it's close cousin. Reinstates the VBA integrated error management (message box etc.)
On Error Resume Next完全关闭 VBA 集成的错误处理（消息框等），只是在下一行继续执行。使用后一定要尽早检查错误，因为悬空错误可能会破坏正常的执行流程。发现错误后立即清除错误以防止发生这种情况。On Error Goto <Jump Label>在给定的标签处恢复执行，主要用于捕获各种错误的每个函数的错误处理程序。On Error Goto <Line Number>在给定的行号处恢复。远离它，它没有用，甚至是危险的。On Error Goto 0它是近亲。恢复 VBA 集成错误管理（消息框等）
EDIT
编辑
From the edited qestion, this is my proposal to solve your problem.
从编辑过的问题中，这是我解决您问题的建议。
For Each FieldName In FieldNames ' assuming you have some looping construct here '
strsql3 = "SELECT " & FieldName & " FROM table"
On Error Resume Next
Set rs3 = CurrentDb.OpenRecordset(strsql3)
If Err.Number = 3061 Then
' Do nothing. We dont care about this error '
Err.Clear
Else
MsgBox "Uncaught error number " & Err.Number & " (" & Err.Description & ")"
Err.Clear
End If
On Error GoTo 0
Next FieldName
Be sure to clear the error in any casebefore you go on with a loop in the same Sub or Function. As I said, a dangling error causes code flow to become unexpected!
在继续在同一个 Sub 或 Function 中进行循环之前，请务必在任何情况下清除错误。正如我所说，悬空错误会导致代码流变得意外！
回答by Fionnuala
Rather than trapping the error, why not use the TableDefs to check for the field or use a mixture of ADO and DAO? ADO Schemas can provide a list of tables that contain the required field:
与其捕获错误，为什么不使用 TableDefs 来检查字段或混合使用 ADO 和 DAO？ADO Schemas 可以提供包含必填字段的表列表：
Function ListTablesContainingField()
Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
Dim rs As ADODB.Recordset, rs2 As ADODB.Recordset
Dim connString As String
Dim SelectFieldName
Set cn = CurrentProject.Connection
SelectFieldName = "Fall" 'For tksy '
'Get names of all tables that have a column called 'ID' '
Set rs = cn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, Empty, SelectFieldName))
'Show the tables that have been selected '
While Not rs.EOF
'Exclude MS system tables '
If Left(rs!Table_Name, 4) <> "MSys" Then
' Edit for tksy, who is using more than one forum '
If tdf.Name = "01UMWELT" Then
strSQL = "Select * From 01UMWELT Where Status = 5"
Else
strSQL = "Select a.* From [" & rs!Table_Name _
& "] a Inner Join 01UMWELT On a.fall = 01UMWELT.fall " _
& "Where 01UMWELT.Status = 5"
End If
Set rs2 = CurrentDb.OpenRecordset(strSQL)
Do While Not rs2.EOF
For i = 0 To rs2.Fields.Count - 1
If IsNull(rs2.Fields(i)) Then
rs2.Edit
rs2.Fields(i) = 111111
rs2.Update
End If
Next
rs2.MoveNext
Loop
End If
rs.MoveNext
Wend
rs.Close
Set cn = Nothing
End Function
回答by JTeagle
Try this:
尝试这个：
On Error Resume Next ' If an error occurs, move to next statement.
On Error Resume Next ' 如果发生错误，移动到下一个语句。
...statement that tries the select...
...尝试选择的语句...
If (Err <> 0) Then
如果 (Err <> 0) 那么
...act on error, or simply ignore if necessary...
End If
万一
On Error Goto 0 ' Reset error handling to previous state.
On Error Goto 0 ' 将错误处理重置为之前的状态。
