Article ID: 116034
Article Last Modified on 10/29/2003
APPLIES TO
- Microsoft Visual Basic 3.0 Professional Edition
- Microsoft Visual Basic 3.0 Professional Edition
This article was previously published under Q116034
SYMPTOMS
If you have the Microsoft Jet 2.0/Visual Basic 3.0 Compatibility Layer installed on your computer, and you perform a transaction in which you open multiple tables, dynasets, or snapshots, you may encounter error 3014:
This occurs after your transaction opens approximately three hundred tables, dynasets, or snapshots. The error occurs even if you explicitly close the tables, dynasets, or snapshots at some other point during the transaction.
CAUSE
This behavior is the result of a change to the Microsoft Access (Jet) database engine. In the Jet version 1.1 database engine, tables could be closed during a transaction. This resulted in unexpected behavior where closed tables would either be omitted from the transaction or cause an implicit RollBack.
This problem was resolved in the Jet version 2.0 database engine by keeping table references open until the transaction finishes, so tables closed during the transaction can be maintained as part of the transaction without causing a RollBack. However, because table references are maintained, it is possible to exhaust the database engine's workspace if you open a large number of tables during the transaction.
RESOLUTION
Open frequently used tables, dynasets, or snapshots outside the transaction.
STATUS
This behavior is by design.
MORE INFORMATION
Steps to Reproduce Behavior
- Start a new project in Visual Basic. Form1 is created by default.
- Add a command button (Command1) and a Label (Label1) to Form1.
Place the following code in the Command1 Click event:
Sub Command1_Click () Dim i As Integer Dim db As database Dim ss As snapshot On Error GoTo Command1_ClickError Set db = OpenDatabase("c:\vb\biblio.mdb") BeginTrans For i = 1 To 500 Label1.Caption = i Label1.Refresh Set ss = db.CreateSnapshot("SELECT * FROM Authors") ' ' Code to manipulate the Snapshot records would be here ... ' ss.Close Set ss = Nothing Next i Rollback db.Close Label1.Caption = Trim(Label1.Caption) & " Done!" Label1.Refresh Exit Sub Command1_ClickError: If Err <> 0 Then ' Enter the following two lines as one, single line: MsgBox "Error: " & Trim(CStr(Err)) & Chr$(10) & Chr$(13) & "Error: " & Trim(Error$) Label1.Caption = Trim(Label1.Caption) & " FAIL!" Label1.Refresh Rollback End If Exit Sub End Sub
- Run the program. The application will run through 332 iterations of creating and closing the snapshot before generating the error.
Code to Avoid the Error
To avoid the error, rewrite the code for the loop as:
Set ss = db.CreateSnapshot("SELECT * FROM Authors") For i = 1 To 500 Label1.Caption = i Label1.Refresh ' ' Code to manipulate the Snapshot records would be here ... ' Next i ss.Close Set ss = Nothing
Because the snapshot is opened outside the loop, only one reference to the underlying tables is created, so the "Can't open any more tables" error does not occur.
Additional query words: 3.00 errmsg
Keywords: kbprb KB116034