I am doing several tables update and I am using Begin and Commit Trans method. After the CommitTrans, I try to requery one of the tables (table3), I can get all expected records. But if I try to open it, I get runtime error "3021" saying "Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record."

Can anyone help me to understand this?

Thanks

We might need a bit more detail to pinpoint this.

What version and service pack is this? There is an known issue with error
3021 on Access 2002 SP3.

We may also need to know whether you BeginTrans on the dbEngine object or
the Workspace object (DAO), or if you are using ADO transactions.

With DAO, it is important not to close the default workspace. Example of
this and related issues at:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter" <(E-Mail Removed)> wrote in message
news:400CA913-EB4E-4D3A-8BF0-(E-Mail Removed)...
> I am doing several tables update and I am using Begin and Commit Trans
method. After the CommitTrans, I try to requery one of the tables (table3),
I can get all expected records. But if I try to open it, I get runtime
error "3021" saying "Either BOF or EOF is true, or the current record has
been deleted. Requested operation requires a current record."
>
> Can anyone help me to understand this?
>
> Thanks

Hi Peter.

I don't use ADO, but you opened rst1 inside the transaction, so try closing
it, setting it to Nothing, then committing, then opening it again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter" <(E-Mail Removed)> wrote in message
news:C96E7C3A-74EE-4165-9D84-(E-Mail Removed)...
> I am using Access 2002 SP3. I am using ADO. My code looks like the
below.
>
> Dim cnn1 as New ADODB.Connection
> Dim rst1 as New ADODB.Recordsets
>
> Set cnn1 = CurrentProject.Connection
> Set rst1 = New ADODB.Recordsets
>
> cnn1.BeginTrans
>
> rst1.Open "Select * From tblTable" .....
>
> <logic lines here>
>
> rst1.Update
>
> cnn1.CommitTrans
>
> rst1.Open "Select * From tblTable" ..... (runtime error 3021 popup here)
>
>
> rst1.Close
> cnn1.Close
> Set rst1 = Nothing
> Set cnn1 = Nothing
>
>
>
>
>
>
>
> "Allen Browne" wrote:
>
> > We might need a bit more detail to pinpoint this.
> >
> > What version and service pack is this? There is an known issue with
error
> > 3021 on Access 2002 SP3.
> >
> > We may also need to know whether you BeginTrans on the dbEngine object
or
> > the Workspace object (DAO), or if you are using ADO transactions.
> >
> > With DAO, it is important not to close the default workspace. Example of
> > this and related issues at:
> > http://allenbrowne.com/ser-37.html
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Peter" <(E-Mail Removed)> wrote in message
> > news:400CA913-EB4E-4D3A-8BF0-(E-Mail Removed)...
> > > I am doing several tables update and I am using Begin and Commit Trans
> > method. After the CommitTrans, I try to requery one of the tables
(table3),
> > I can get all expected records. But if I try to open it, I get runtime
> > error "3021" saying "Either BOF or EOF is true, or the current record
has
> > been deleted. Requested operation requires a current record."
> > >
> > > Can anyone help me to understand this?
> > >
> > > Thanks

I did try closing rst1 after transaction commit. I will try your way. I am using rst2 to open it again.

Allen, do you know any books or reference talking deeply inside ADO?

Thanks

"Allen Browne" wrote:

> Hi Peter.
>
> I don't use ADO, but you opened rst1 inside the transaction, so try closing
> it, setting it to Nothing, then committing, then opening it again.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Peter" <(E-Mail Removed)> wrote in message
> news:C96E7C3A-74EE-4165-9D84-(E-Mail Removed)...
> > I am using Access 2002 SP3. I am using ADO. My code looks like the
> below.
> >
> > Dim cnn1 as New ADODB.Connection
> > Dim rst1 as New ADODB.Recordsets
> >
> > Set cnn1 = CurrentProject.Connection
> > Set rst1 = New ADODB.Recordsets
> >
> > cnn1.BeginTrans
> >
> > rst1.Open "Select * From tblTable" .....
> >
> > <logic lines here>
> >
> > rst1.Update
> >
> > cnn1.CommitTrans
> >
> > rst1.Open "Select * From tblTable" ..... (runtime error 3021 popup here)
> >
> >
> > rst1.Close
> > cnn1.Close
> > Set rst1 = Nothing
> > Set cnn1 = Nothing
> >
> >
> >
> >
> >
> >
> >
> > "Allen Browne" wrote:
> >
> > > We might need a bit more detail to pinpoint this.
> > >
> > > What version and service pack is this? There is an known issue with
> error
> > > 3021 on Access 2002 SP3.
> > >
> > > We may also need to know whether you BeginTrans on the dbEngine object
> or
> > > the Workspace object (DAO), or if you are using ADO transactions.
> > >
> > > With DAO, it is important not to close the default workspace. Example of
> > > this and related issues at:
> > > http://allenbrowne.com/ser-37.html
> > >
> > > --
> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > Tips for Access users - http://allenbrowne.com/tips.html
> > > Reply to group, rather than allenbrowne at mvps dot org.
> > >
> > > "Peter" <(E-Mail Removed)> wrote in message
> > > news:400CA913-EB4E-4D3A-8BF0-(E-Mail Removed)...
> > > > I am doing several tables update and I am using Begin and Commit Trans
> > > method. After the CommitTrans, I try to requery one of the tables
> (table3),
> > > I can get all expected records. But if I try to open it, I get runtime
> > > error "3021" saying "Either BOF or EOF is true, or the current record
> has
> > > been deleted. Requested operation requires a current record."
> > > >
> > > > Can anyone help me to understand this?
> > > >
> > > > Thanks
>
>
>