ADO批量提交事务时发生错误。
Test数据库中的表test5是不存在的，可是批量提交事务后居然能够正确执行，貌似这种方法只能够检测SQL语法是否正确。怎样才能解决这种错误，请指点谢谢大家。
Public Cn As ADODB.ConnectionPublic Rs As ADODB.RecordsetPublic Function DbConnection() As Boolean On Error GoTo ErrorHandler Set Cn = New ADODB.Connection Cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Test;Data Source=." Cn.Open DbConnection = True Exit FunctionErrorHandler: DbConnection = FalseEnd FunctionPrivate Sub Command2_Click() On Error GoTo ErrorHandler Dim sSQL As String If DbConnection Then Cn.BeginTrans sSQL = "insert into test1(PID,UID,PS) Values(1,'1','1');" _ & "insert into test1(PID,UID,PS) Values(2,'1','1');" _ & "insert into test1(PID,UID,PS) Values(3,'1','1');" _ & "insert into test1(PID,UID,PS) Values(4,'1','1');" Cn.Execute sSQL sSQL = "insert into test1(PID,UID,PS) Values(5,'1','2');" _ & "insert into test1(PID,UID,PS) Values(6,'1','1');" _ & "insert into test5(PID,UID,PS) Values(7,'1','1');" Cn.Execute sSQL Cn.CommitTrans MsgBox "执行成功", vbInformation End If Exit SubErrorHandler: Cn.RollbackTrans MsgBox Err.Description, vbInformationEnd Sub
DROP TABLE [test1] CREATE TABLE [test1] ( [PID] [int] NOT NULL , [uID] [char] (10) NULL , [PS] [char] (10) NULL ) ALTER TABLE [test1] WITH NOCHECK ADD CONSTRAINT [PK_test1] PRIMARY KEY NONCLUSTERED ( [PID] )
sSQL = "set nocount on;" & _ "insert into test1(PID,UID,PS) Values(5,'1','2');" _ & "insert into test1(PID,UID,PS) Values(6,'1','1');" _ & "insert into test5(PID,UID,PS) Values(7,'1','1');"
