Is it possible to include Begin And End Transaction code in VBA ?

Hi,

I took over a project and recenly converted the database from ACCESS 97/SQL
7 to MS ACCESS XP/SQL Sever 2000. The database object is use is DAO. The
users have been run into a lot of locks problem after the update. We
installed the SQL Server Standard version with default setting. I did some
research that the best way to handle the lock to have control over the
transaction. The previous developer uses Workspace. But as I step throught
the code, I don't see the locks been release after the CommitTrans fired and
user still hangs...
======================================
Function HandleNullMemID() As Boolean
On Error GoTo HandleErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean

Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
boolInTrans = True
'Append unmatched records to exceptions tbl
strSQL = "qryMemPayImpExceptions"
mDB.Execute strSQL

'Delete unmatched records from import tbl
strSQL = "qryMemPayImpExceptionsDelete"
mDB.Execute strSQL
mWS.CommitTrans

boolInTrans = False
HandleNullMemID = True

HandleExit:
If boolInTrans = True Then mWS.Rollback
Exit Function

HandleErr:
ErrMsgBox Err.Description, basName & ".HandleNullMemID", Err.Number
HandleNullMemID = False
Resume HandleExit

End Function
===========================================
Please advise

Thanks

Jean

the code, I don't see the locks been release after the CommitTrans fired

For us, this problem suddenly appeared last week and
just as suddenly went away again. We don't know what
to think. We haven't had any problems before.

(david)

"Jean" wrote in message
...
Hi,

I took over a project and recenly converted the database from ACCESS
97/SQL
7 to MS ACCESS XP/SQL Sever 2000. The database object is use is DAO. The
users have been run into a lot of locks problem after the update. We
installed the SQL Server Standard version with default setting. I did
some
research that the best way to handle the lock to have control over the
transaction. The previous developer uses Workspace. But as I step
throught
the code, I don't see the locks been release after the CommitTrans fired
and
user still hangs...
======================================
Function HandleNullMemID() As Boolean
On Error GoTo HandleErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean

Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
boolInTrans = True
'Append unmatched records to exceptions tbl
strSQL = "qryMemPayImpExceptions"
mDB.Execute strSQL

'Delete unmatched records from import tbl
strSQL = "qryMemPayImpExceptionsDelete"
mDB.Execute strSQL
mWS.CommitTrans

boolInTrans = False
HandleNullMemID = True

HandleExit:
If boolInTrans = True Then mWS.Rollback
Exit Function

HandleErr:
ErrMsgBox Err.Description, basName & ".HandleNullMemID", Err.Number
HandleNullMemID = False
Resume HandleExit

End Function
===========================================
Please advise

Thanks

Jean

I'm no expert in Workspaces, but it seems to me that by using CurrentDb to
create the recordset, you are not using the workspace defined. Whenever I
have seen workspaces used, it has been something like this:

Set mWS = DBEngine.Workspaces(0)
Set mDB = mWS.Databases![C:\the\path\to\MyDatabase.mdb]

Now it may well be that CurrentDb is using the default workspace as defined
in the row above, but it would be worth while to investigate.

If you don't want to hard-code the database name, this might work instead:
Set mDB = mWS.Databases(CurrentDb.Name)

Disclaimer: this is all speculation and untested. Hope it gives you a start
though.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Jean" wrote in message
...
Hi,

I took over a project and recenly converted the database from ACCESS
97/SQL
7 to MS ACCESS XP/SQL Sever 2000. The database object is use is DAO. The
users have been run into a lot of locks problem after the update. We
installed the SQL Server Standard version with default setting. I did
some
research that the best way to handle the lock to have control over the
transaction. The previous developer uses Workspace. But as I step
throught
the code, I don't see the locks been release after the CommitTrans fired
and
user still hangs...
======================================
Function HandleNullMemID() As Boolean
On Error GoTo HandleErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean

Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
boolInTrans = True
'Append unmatched records to exceptions tbl
strSQL = "qryMemPayImpExceptions"
mDB.Execute strSQL

'Delete unmatched records from import tbl
strSQL = "qryMemPayImpExceptionsDelete"
mDB.Execute strSQL
mWS.CommitTrans

boolInTrans = False
HandleNullMemID = True

HandleExit:
If boolInTrans = True Then mWS.Rollback
Exit Function

HandleErr:
ErrMsgBox Err.Description, basName & ".HandleNullMemID", Err.Number
HandleNullMemID = False
Resume HandleExit

End Function
===========================================
Please advise

Thanks

Jean

David,

Same situation here. We just can't reproduce the lock problem consistently,
it happens sometimes.

Jean


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
the code, I don't see the locks been release after the CommitTrans fired

For us, this problem suddenly appeared last week and
just as suddenly went away again. We don't know what
to think. We haven't had any problems before.

(david)

"Jean" wrote in message
...
Hi,

I took over a project and recenly converted the database from ACCESS
97/SQL
7 to MS ACCESS XP/SQL Sever 2000. The database object is use is DAO.
The
users have been run into a lot of locks problem after the update. We
installed the SQL Server Standard version with default setting. I did
some
research that the best way to handle the lock to have control over the
transaction. The previous developer uses Workspace. But as I step
throught
the code, I don't see the locks been release after the CommitTrans fired
and
user still hangs...
======================================
Function HandleNullMemID() As Boolean
On Error GoTo HandleErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean

Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
boolInTrans = True
'Append unmatched records to exceptions tbl
strSQL = "qryMemPayImpExceptions"
mDB.Execute strSQL

'Delete unmatched records from import tbl
strSQL = "qryMemPayImpExceptionsDelete"
mDB.Execute strSQL
mWS.CommitTrans

boolInTrans = False
HandleNullMemID = True

HandleExit:
If boolInTrans = True Then mWS.Rollback
Exit Function

HandleErr:
ErrMsgBox Err.Description, basName & ".HandleNullMemID", Err.Number
HandleNullMemID = False
Resume HandleExit

End Function
===========================================
Please advise

Thanks

Jean

Thanks, Roger. I will try it.


"Roger Carlson" wrote in message
...
I'm no expert in Workspaces, but it seems to me that by using CurrentDb
to
create the recordset, you are not using the workspace defined. Whenever I
have seen workspaces used, it has been something like this:

Set mWS = DBEngine.Workspaces(0)
Set mDB = mWS.Databases![C:\the\path\to\MyDatabase.mdb]

Now it may well be that CurrentDb is using the default workspace as
defined
in the row above, but it would be worth while to investigate.

If you don't want to hard-code the database name, this might work instead:
Set mDB = mWS.Databases(CurrentDb.Name)

Disclaimer: this is all speculation and untested. Hope it gives you a
start
though.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Jean" wrote in message
...
Hi,

I took over a project and recenly converted the database from ACCESS
97/SQL
7 to MS ACCESS XP/SQL Sever 2000. The database object is use is DAO.
The
users have been run into a lot of locks problem after the update. We
installed the SQL Server Standard version with default setting. I did
some
research that the best way to handle the lock to have control over the
transaction. The previous developer uses Workspace. But as I step
throught
the code, I don't see the locks been release after the CommitTrans fired
and
user still hangs...
======================================
Function HandleNullMemID() As Boolean
On Error GoTo HandleErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean

Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
boolInTrans = True
'Append unmatched records to exceptions tbl
strSQL = "qryMemPayImpExceptions"
mDB.Execute strSQL

'Delete unmatched records from import tbl
strSQL = "qryMemPayImpExceptionsDelete"
mDB.Execute strSQL
mWS.CommitTrans

boolInTrans = False
HandleNullMemID = True

HandleExit:
If boolInTrans = True Then mWS.Rollback
Exit Function

HandleErr:
ErrMsgBox Err.Description, basName & ".HandleNullMemID", Err.Number
HandleNullMemID = False
Resume HandleExit

End Function
===========================================
Please advise

Thanks

Jean

Normally, CurrentDB is in the first (0, default) workspace. I've
never found it to be anywhere else, but I remember that when A2K
come out, CurrentDB could sometimes be something other than
databases(0), so anything is possible.

(david)

"Roger Carlson" wrote in message
...
I'm no expert in Workspaces, but it seems to me that by using CurrentDb
to
create the recordset, you are not using the workspace defined. Whenever I
have seen workspaces used, it has been something like this:

Set mWS = DBEngine.Workspaces(0)
Set mDB = mWS.Databases![C:\the\path\to\MyDatabase.mdb]

Now it may well be that CurrentDb is using the default workspace as
defined
in the row above, but it would be worth while to investigate.

If you don't want to hard-code the database name, this might work instead:
Set mDB = mWS.Databases(CurrentDb.Name)

Disclaimer: this is all speculation and untested. Hope it gives you a
start
though.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Jean" wrote in message
...
Hi,

I took over a project and recenly converted the database from ACCESS
97/SQL
7 to MS ACCESS XP/SQL Sever 2000. The database object is use is DAO.
The
users have been run into a lot of locks problem after the update. We
installed the SQL Server Standard version with default setting. I did
some
research that the best way to handle the lock to have control over the
transaction. The previous developer uses Workspace. But as I step
throught
the code, I don't see the locks been release after the CommitTrans fired
and
user still hangs...
======================================
Function HandleNullMemID() As Boolean
On Error GoTo HandleErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean

Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
boolInTrans = True
'Append unmatched records to exceptions tbl
strSQL = "qryMemPayImpExceptions"
mDB.Execute strSQL

'Delete unmatched records from import tbl
strSQL = "qryMemPayImpExceptionsDelete"
mDB.Execute strSQL
mWS.CommitTrans

boolInTrans = False
HandleNullMemID = True

HandleExit:
If boolInTrans = True Then mWS.Rollback
Exit Function

HandleErr:
ErrMsgBox Err.Description, basName & ".HandleNullMemID", Err.Number
HandleNullMemID = False
Resume HandleExit

End Function
===========================================
Please advise

Thanks

Jean

Jean: Try explicitly destroying mWS when the procedure ends.

David: It's not CurrentDb that can be wrong - it's DBEngine(0)(0)!

DBEngine(0)(0) is not always up to date, because it's not refreshed
immediately when the object hierarchy changes. The chance of this occurring
in normal databases is negligible, but to ensure that you are working with
the current database, you need to execute the Refresh method to rebuild the
collection, and place the current database in the first position in the
Databases collection.

DBEngine(0).Databases.Refresh

Of course, you get a big performance hit when you do that.

The solution that Microsoft came up with was to provide CurrentDb().
CurrentDb is not an object; it's a built-in function that returns a
reference to the current user's default database. Although CurrentDb and
DBEngine(0)(0) do refer to the same database, they are not the same object
internally. Access maintains a single permanent reference to the current
database, but CurrentDb temporarily creates a new internal object; one in
which the collections are guaranteed to be up-to-date. CurrentDb is not
without its own performance hit (my tests say it's about 60 times slower
than DBEngine(0)(0) ).

The interesting fact is that immediately after CurrentDb executes and
returns a pointer, the internal object is destroyed.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
Normally, CurrentDB is in the first (0, default) workspace. I've
never found it to be anywhere else, but I remember that when A2K
come out, CurrentDB could sometimes be something other than
databases(0), so anything is possible.

(david)

"Roger Carlson" wrote in message
...
I'm no expert in Workspaces, but it seems to me that by using CurrentDb
to
create the recordset, you are not using the workspace defined. Whenever
I
have seen workspaces used, it has been something like this:

Set mWS = DBEngine.Workspaces(0)
Set mDB = mWS.Databases![C:\the\path\to\MyDatabase.mdb]

Now it may well be that CurrentDb is using the default workspace as
defined
in the row above, but it would be worth while to investigate.

If you don't want to hard-code the database name, this might work
instead:
Set mDB = mWS.Databases(CurrentDb.Name)

Disclaimer: this is all speculation and untested. Hope it gives you a
start
though.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Jean" wrote in message
...
Hi,

I took over a project and recenly converted the database from ACCESS
97/SQL
7 to MS ACCESS XP/SQL Sever 2000. The database object is use is DAO.
The
users have been run into a lot of locks problem after the update. We
installed the SQL Server Standard version with default setting. I did
some
research that the best way to handle the lock to have control over the
transaction. The previous developer uses Workspace. But as I step
throught
the code, I don't see the locks been release after the CommitTrans
fired
and
user still hangs...
======================================
Function HandleNullMemID() As Boolean
On Error GoTo HandleErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean

Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
boolInTrans = True
'Append unmatched records to exceptions tbl
strSQL = "qryMemPayImpExceptions"
mDB.Execute strSQL

'Delete unmatched records from import tbl
strSQL = "qryMemPayImpExceptionsDelete"
mDB.Execute strSQL
mWS.CommitTrans

boolInTrans = False
HandleNullMemID = True

HandleExit:
If boolInTrans = True Then mWS.Rollback
Exit Function

HandleErr:
ErrMsgBox Err.Description, basName & ".HandleNullMemID",
Err.Number
HandleNullMemID = False
Resume HandleExit

End Function
===========================================
Please advise

Thanks

Jean

Hi,

Thanks for the suggestion. I added "mWS.Close" or "set mWS = Nothing" try to
explicitly destroying mWS when the procedure ends, but still can't
close/committ the transaction on SQL Server. After the execute statement, I
did a sp_lock on SQL Server, they are just locks on the table where I try to
insert. I have to exit from MS ACCESS completely in order the clear the
locks and all the append record were rollback too, none inserted. Any other
suggestion or comment ?

Thanks

Jean

The DBCC Opentrans still shows

-----------------------------------------------------------------------
transaction information for database 'YourDB'.

Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : 1
Name : implicit_transaction
LSN : (242:86219:1)
Start time : Jun 28 2004 11:49:50:600PM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
----------------------------------------------------------------------------
-------------------------

My Code......................................Below

Function AddPayments() As Boolean
On Error GoTo AddPaymentsErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean


Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
'boolInTrans = True
'Append summary record to payment batch tbl
strSQL = "qryMemPayImpUpdateSumm"
mDB.Execute strSQL, dbSeeChanges

mWS.CommitTrans

boolInTrans = False
Call EmptyTable_TSB("", tblName)
AddPayments = True

AddPaymentsExit:
If boolInTrans = True Then mWS.Rollback
mWS.Close

Exit Function

AddPaymentsErr:
ErrMsgBox Err.Description, basName & ".AddPayments", Err.Number
AddPayments = False
Resume AddPaymentsExit

End Function


----- Original Message -----
From: "Graham R Seach"
Newsgroups: microsoft.public.access.gettingstarted
Sent: Thursday, June 17, 2004 6:34 PM
Subject: Is it possible to include Begin And End Transaction code in
VBA
?


Jean: Try explicitly destroying mWS when the procedure ends.

David: It's not CurrentDb that can be wrong - it's DBEngine(0)(0)!

DBEngine(0)(0) is not always up to date, because it's not refreshed
immediately when the object hierarchy changes. The chance of this
occurring
in normal databases is negligible, but to ensure that you are working
with
the current database, you need to execute the Refresh method to rebuild
the
collection, and place the current database in the first position in the
Databases collection.

DBEngine(0).Databases.Refresh

Of course, you get a big performance hit when you do that.

The solution that Microsoft came up with was to provide CurrentDb().
CurrentDb is not an object; it's a built-in function that returns a
reference to the current user's default database. Although CurrentDb and
DBEngine(0)(0) do refer to the same database, they are not the same
object
internally. Access maintains a single permanent reference to the current
database, but CurrentDb temporarily creates a new internal object; one
in
which the collections are guaranteed to be up-to-date. CurrentDb is not
without its own performance hit (my tests say it's about 60 times slower
than DBEngine(0)(0) ).

The interesting fact is that immediately after CurrentDb executes and
returns a pointer, the internal object is destroyed.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
Normally, CurrentDB is in the first (0, default) workspace. I've
never found it to be anywhere else, but I remember that when A2K
come out, CurrentDB could sometimes be something other than
databases(0), so anything is possible.

(david)

"Roger Carlson" wrote in message
...
I'm no expert in Workspaces, but it seems to me that by using
CurrentDb
to
create the recordset, you are not using the workspace defined.
Whenever
I
have seen workspaces used, it has been something like this:

Set mWS = DBEngine.Workspaces(0)
Set mDB = mWS.Databases![C:\the\path\to\MyDatabase.mdb]

Now it may well be that CurrentDb is using the default workspace as
defined
in the row above, but it would be worth while to investigate.

If you don't want to hard-code the database name, this might work
instead:
Set mDB = mWS.Databases(CurrentDb.Name)

Disclaimer: this is all speculation and untested. Hope it gives you
a
start
though.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Jean" wrote in message
...
Hi,

I took over a project and recenly converted the database from
ACCESS
97/SQL
7 to MS ACCESS XP/SQL Sever 2000. The database object is use is
DAO.
The
users have been run into a lot of locks problem after the update.
We
installed the SQL Server Standard version with default setting. I
did
some
research that the best way to handle the lock to have control over
the
transaction. The previous developer uses Workspace. But as I step
throught
the code, I don't see the locks been release after the CommitTrans
fired

comments:

I saw a kind of suggestion by somebody that he thought
that background data activity on bound controls (form,
cbo) could be blocking the transaction.

Regarding closing Access, I expect that if you created
a new workspace, (using Application.dbEngine.CreateWorkspace)
instead of using the default workspace, then although
you would still have the same problem with hanging
transactions, you would be able to close the workspace
without closing Access. (If you create a new dbEngine
object, you have even more separation from the Access
Application)

Set mWS = DBEngine.Workspaces(0)
Closing mWs might not work, and if it does, it probably
doesn't do what you expect. DBEngine.Workspaces(0) is
normally the default workspace used by Access. This is
the workspace that contains the transactions used for
adding and deleting records in form view or table view,
and for running queries from the database window.


I see the description 'Implicit Transaction' I would have
thought that your explicit transaction would have gone
through as an explicit transaction??? So is this something
else???? I don't know enough about SQL Server to do any
more than wonder.

(david)

"Jean" wrote in message
...
Hi,

Thanks for the suggestion. I added "mWS.Close" or "set mWS = Nothing" try
to
explicitly destroying mWS when the procedure ends, but still can't
close/committ the transaction on SQL Server. After the execute statement,
I
did a sp_lock on SQL Server, they are just locks on the table where I try
to
insert. I have to exit from MS ACCESS completely in order the clear the
locks and all the append record were rollback too, none inserted. Any
other
suggestion or comment ?

Thanks

Jean

The DBCC Opentrans still shows

-----------------------------------------------------------------------
transaction information for database 'YourDB'.

Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : 1
Name : implicit_transaction
LSN : (242:86219:1)
Start time : Jun 28 2004 11:49:50:600PM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--------------------------------------------------------------------------
--
-------------------------

My Code......................................Below

Function AddPayments() As Boolean
On Error GoTo AddPaymentsErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean


Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
'boolInTrans = True
'Append summary record to payment batch tbl
strSQL = "qryMemPayImpUpdateSumm"
mDB.Execute strSQL, dbSeeChanges

mWS.CommitTrans

boolInTrans = False
Call EmptyTable_TSB("", tblName)
AddPayments = True

AddPaymentsExit:
If boolInTrans = True Then mWS.Rollback
mWS.Close

Exit Function

AddPaymentsErr:
ErrMsgBox Err.Description, basName & ".AddPayments", Err.Number
AddPayments = False
Resume AddPaymentsExit

End Function


----- Original Message -----
From: "Graham R Seach"
Newsgroups: microsoft.public.access.gettingstarted
Sent: Thursday, June 17, 2004 6:34 PM
Subject: Is it possible to include Begin And End Transaction code in
VBA
?


Jean: Try explicitly destroying mWS when the procedure ends.

David: It's not CurrentDb that can be wrong - it's DBEngine(0)(0)!

DBEngine(0)(0) is not always up to date, because it's not refreshed
immediately when the object hierarchy changes. The chance of this
occurring
in normal databases is negligible, but to ensure that you are working
with
the current database, you need to execute the Refresh method to
rebuild
the
collection, and place the current database in the first position in
the
Databases collection.

DBEngine(0).Databases.Refresh

Of course, you get a big performance hit when you do that.

The solution that Microsoft came up with was to provide CurrentDb().
CurrentDb is not an object; it's a built-in function that returns a
reference to the current user's default database. Although CurrentDb
and
DBEngine(0)(0) do refer to the same database, they are not the same
object
internally. Access maintains a single permanent reference to the
current
database, but CurrentDb temporarily creates a new internal object; one
in
which the collections are guaranteed to be up-to-date. CurrentDb is
not
without its own performance hit (my tests say it's about 60 times
slower
than DBEngine(0)(0) ).

The interesting fact is that immediately after CurrentDb executes and
returns a pointer, the internal object is destroyed.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
Normally, CurrentDB is in the first (0, default) workspace. I've
never found it to be anywhere else, but I remember that when A2K
come out, CurrentDB could sometimes be something other than
databases(0), so anything is possible.

(david)

"Roger Carlson" wrote in message
...
I'm no expert in Workspaces, but it seems to me that by using
CurrentDb
to
create the recordset, you are not using the workspace defined.
Whenever
I
have seen workspaces used, it has been something like this:

Set mWS = DBEngine.Workspaces(0)
Set mDB = mWS.Databases![C:\the\path\to\MyDatabase.mdb]

Now it may well be that CurrentDb is using the default workspace
as
defined
in the row above, but it would be worth while to investigate.

If you don't want to hard-code the database name, this might work
instead:
Set mDB = mWS.Databases(CurrentDb.Name)

Disclaimer: this is all speculation and untested. Hope it gives
you
a
start
though.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Jean" wrote in message
...
Hi,

I took over a project and recenly converted the database from
ACCESS
97/SQL
7 to MS ACCESS XP/SQL Sever 2000. The database object is use
is
DAO.
The
users have been run into a lot of locks problem after the
update.
We
installed the SQL Server Standard version with default setting.
I
did
some
research that the best way to handle the lock to have control
over
the
transaction. The previous developer uses Workspace. But as I
step
throught
the code, I don't see the locks been release after the
CommitTrans
fired

David and all,

You know what, we do have a form that's always open as long as the
application is running. And I took your suggestion to close it before I ran
the code. All the locks were gone and transaction were committed.

Why ? This wasn't an issue before in ACCESS 97 ? Is there anyway to keep
window open and also do the transaction ?

Thanks

Jean


"david epsom dot com dot au" david@epsomdotcomdotau wrote in message
...
comments:

I saw a kind of suggestion by somebody that he thought
that background data activity on bound controls (form,
cbo) could be blocking the transaction.

Regarding closing Access, I expect that if you created
a new workspace, (using Application.dbEngine.CreateWorkspace)
instead of using the default workspace, then although
you would still have the same problem with hanging
transactions, you would be able to close the workspace
without closing Access. (If you create a new dbEngine
object, you have even more separation from the Access
Application)

Set mWS = DBEngine.Workspaces(0)
Closing mWs might not work, and if it does, it probably
doesn't do what you expect. DBEngine.Workspaces(0) is
normally the default workspace used by Access. This is
the workspace that contains the transactions used for
adding and deleting records in form view or table view,
and for running queries from the database window.


I see the description 'Implicit Transaction' I would have
thought that your explicit transaction would have gone
through as an explicit transaction??? So is this something
else???? I don't know enough about SQL Server to do any
more than wonder.

(david)

"Jean" wrote in message
...
Hi,

Thanks for the suggestion. I added "mWS.Close" or "set mWS = Nothing"
try
to
explicitly destroying mWS when the procedure ends, but still can't
close/committ the transaction on SQL Server. After the execute
statement,
I
did a sp_lock on SQL Server, they are just locks on the table where I
try
to
insert. I have to exit from MS ACCESS completely in order the clear the
locks and all the append record were rollback too, none inserted. Any
other
suggestion or comment ?

Thanks

Jean

The DBCC Opentrans still shows

-----------------------------------------------------------------------
transaction information for database 'YourDB'.

Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : 1
Name : implicit_transaction
LSN : (242:86219:1)
Start time : Jun 28 2004 11:49:50:600PM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

--------------------------------------------------------------------------
--
-------------------------

My Code......................................Below

Function AddPayments() As Boolean
On Error GoTo AddPaymentsErr

Dim mWS As Workspace
Dim strSQL As String
Dim boolInTrans As Boolean


Set mWS = DBEngine.Workspaces(0)
Set mDB = CurrentDb()

mWS.BeginTrans
'boolInTrans = True
'Append summary record to payment batch tbl
strSQL = "qryMemPayImpUpdateSumm"
mDB.Execute strSQL, dbSeeChanges

mWS.CommitTrans

boolInTrans = False
Call EmptyTable_TSB("", tblName)
AddPayments = True

AddPaymentsExit:
If boolInTrans = True Then mWS.Rollback
mWS.Close

Exit Function

AddPaymentsErr:
ErrMsgBox Err.Description, basName & ".AddPayments", Err.Number
AddPayments = False
Resume AddPaymentsExit

End Function


----- Original Message -----
From: "Graham R Seach"
Newsgroups: microsoft.public.access.gettingstarted
Sent: Thursday, June 17, 2004 6:34 PM
Subject: Is it possible to include Begin And End Transaction code
in
VBA
?


Jean: Try explicitly destroying mWS when the procedure ends.

David: It's not CurrentDb that can be wrong - it's DBEngine(0)(0)!

DBEngine(0)(0) is not always up to date, because it's not refreshed
immediately when the object hierarchy changes. The chance of this
occurring
in normal databases is negligible, but to ensure that you are
working
with
the current database, you need to execute the Refresh method to
rebuild
the
collection, and place the current database in the first position in
the
Databases collection.

DBEngine(0).Databases.Refresh

Of course, you get a big performance hit when you do that.

The solution that Microsoft came up with was to provide CurrentDb().
CurrentDb is not an object; it's a built-in function that returns a
reference to the current user's default database. Although CurrentDb
and
DBEngine(0)(0) do refer to the same database, they are not the same
object
internally. Access maintains a single permanent reference to the
current
database, but CurrentDb temporarily creates a new internal object;
one
in
which the collections are guaranteed to be up-to-date. CurrentDb is
not
without its own performance hit (my tests say it's about 60 times
slower
than DBEngine(0)(0) ).

The interesting fact is that immediately after CurrentDb executes
and
returns a pointer, the internal object is destroyed.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"david epsom dot com dot au" david@epsomdotcomdotau wrote in
message
...
Normally, CurrentDB is in the first (0, default) workspace. I've
never found it to be anywhere else, but I remember that when A2K
come out, CurrentDB could sometimes be something other than
databases(0), so anything is possible.

(david)

"Roger Carlson" wrote in message
...
I'm no expert in Workspaces, but it seems to me that by using
CurrentDb
to
create the recordset, you are not using the workspace defined.
Whenever
I
have seen workspaces used, it has been something like this:

Set mWS = DBEngine.Workspaces(0)
Set mDB = mWS.Databases![C:\the\path\to\MyDatabase.mdb]

Now it may well be that CurrentDb is using the default workspace
as
defined
in the row above, but it would be worth while to investigate.

If you don't want to hard-code the database name, this might
work
instead:
Set mDB = mWS.Databases(CurrentDb.Name)

Disclaimer: this is all speculation and untested. Hope it gives
you
a
start
though.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Jean" wrote in message
...
Hi,

I took over a project and recenly converted the database from
ACCESS
97/SQL
7 to MS ACCESS XP/SQL Sever 2000. The database object is use
is
DAO.
The
users have been run into a lot of locks problem after the
update.
We
installed the SQL Server Standard version with default
setting.
I
did
some
research that the best way to handle the lock to have control
over
the
transaction. The previous developer uses Workspace. But as I
step
throught
the code, I don't see the locks been release after the
CommitTrans
fired