If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Is it possible to include Begin And End Transaction code in VBA ?
Like I said, I've only seen this problem recently.
Maybe it was introduced with Jet SR8? However, Access 2K does have a more complex relationship to both Jet and SQL Server than A97 had. You've got OLEDB bound forms, seamless Identity Insert, and at least at first, multiple database connections from the IDE. So this may be a symptom of that greater complexity. I've very grateful to you for posting the information about how your problem was solved. (david) "Jean" wrote in message ... 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 |
|
Thread Tools | |
Display Modes | |
|
|