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

[Jean]

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 epsom dot com dot au]

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)

 

[Roger Carlson]

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.

 

[Jean]

David,

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

Jean

 

[Jean]

Thanks, Roger. I will try it.

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

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 epsom dot com dot au]

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)

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

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

 

[Graham R Seach]

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/WileyTitle/productCd-0764559036.html

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)

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

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