Access locking and transactions

[Joseph S.]

Hi all,

I'm using the following setup:
VB.Net (.NET framework 1.1)
Access2000
SharpDevelop 1.1 for development (it is very similar to Visual Studio
..NET)

using ODBCConnection, ODBCCommand, etc.

The ODBC.NET and Access2000 combination supports transactions. So I've
put it into the application.

I have to print a report from Access.
I have the following code:
(wherever attention is needed, I have marked it as "NOTE")
(dbo,glob,errmgr are instances of classes I have made for handling the
database, global values and errors, respectively)

Private Sub BtnPrintAllClick(sender As System.Object, e As
System.EventArgs)
Try
Dim amcobj As New CAMC(glob, dbo, errmgr)
Dim printobj As New CPrint(glob, dbo, errmgr)
Dim i, j As Integer, dbrow As DataRow
dbo.Begin() 'ID7 <--- NOTE: This issues a
ODBCConnection.BeginTransaction()
printobj.ClearTable()
For i = 0 To Results.Rows.Count - 1
'amcobj.Fetch(Results.Rows(i)("AMCNo"), 0)
dbrow = Results.Rows(i)
printobj.AMCNo = dbrow("AMCNo")
printobj.CustID = dbrow("CustID")
printobj.VisitCount = dbrow("VisitCount")
printobj.AMCStart = dbrow("AMCStart")
printobj.AMCEnd = dbrow("AMCEnd")
printobj.Amount = dbrow("Amount")
printobj.Name = dbrow("Name")
printobj.Area = dbrow("Area")
printobj.Tel = dbrow("Tel")
printobj.Add()
Next
dbo.Commit() 'ID7 <---NOTE: this issues a ODBCTransaction.Commit()
dbo.CloseConn() 'ID7 <---NOTE: this issues a ODBCConnection.Close()
Dim acobj As New Access.Application
acobj.OpenCurrentDatabase(glob.DBPath, False) <---NOTE: This fails
strangely giving error [1]
acobj.Visible = True
acobj.DoCmd.OpenReport("PrintAMCs", Access.AcView.acViewPreview,
Nothing, Nothing )
'acobj.Quit(Access.AcQuitOption.acQuitSaveNone)
'acobj = Nothing
dbo.OpenConn() 'ID7
Catch ex As System.Exception
dbo.Rollback() 'ID7
errmgr.LogMsg(Me.GetType().FullName, ex)
errmgr.ShowError(errmgr.EInternal,MsgBoxStyle.Crit ical)
End Try

End Sub

[1] the error is ->
System.Runtime.InteropServices.COMException (0x800A1EBA): Microsoft
Access can't open the database because it is missing, or opened
exclusively by another user.
at Access.ApplicationClass.OpenCurrentDatabase(String filepath,
Boolean Exclusive)
at NSAMC.MainForm.BtnPrintAllClick(Object sender, EventArgs e) in
C:\data\projects\SharpDevelop\AMC\MainForm.vb:line 979

The interesting part is that when I run the .exe from inside the IDE
(SharpDevelop) it does not
give an error - an Access window pops up and neatly shows me the
report. However, if I simply copy the entire set of files (exe, pdb,
manifest, mdb, dll) in the Debug folder into an independent location
then I get this error. I do not think it is to do with SharpDevelop
because the executables output into the Debug/Release deirectory are
pretty much standard - .exe, .dll,.pdb, .manifest etc.

Any clues?
TIA,
JS

[Paul Clement]

On 17 May 2006 01:16:45 -0700, "Joseph S." <js****@rediffmail.com> wrote:

¤ Hi all,
¤
¤ I'm using the following setup:
¤ VB.Net (.NET framework 1.1)
¤ Access2000
¤ SharpDevelop 1.1 for development (it is very similar to Visual Studio
¤ .NET)
¤
¤ using ODBCConnection, ODBCCommand, etc.
¤
¤ The ODBC.NET and Access2000 combination supports transactions. So I've
¤ put it into the application.
¤
¤ I have to print a report from Access.
¤ I have the following code:
¤ (wherever attention is needed, I have marked it as "NOTE")
¤ (dbo,glob,errmgr are instances of classes I have made for handling the
¤ database, global values and errors, respectively)
¤
¤ Private Sub BtnPrintAllClick(sender As System.Object, e As
¤ System.EventArgs)
¤ Try
¤ Dim amcobj As New CAMC(glob, dbo, errmgr)
¤ Dim printobj As New CPrint(glob, dbo, errmgr)
¤ Dim i, j As Integer, dbrow As DataRow
¤ dbo.Begin() 'ID7 <--- NOTE: This issues a
¤ ODBCConnection.BeginTransaction()
¤ printobj.ClearTable()
¤ For i = 0 To Results.Rows.Count - 1
¤ 'amcobj.Fetch(Results.Rows(i)("AMCNo"), 0)
¤ dbrow = Results.Rows(i)
¤ printobj.AMCNo = dbrow("AMCNo")
¤ printobj.CustID = dbrow("CustID")
¤ printobj.VisitCount = dbrow("VisitCount")
¤ printobj.AMCStart = dbrow("AMCStart")
¤ printobj.AMCEnd = dbrow("AMCEnd")
¤ printobj.Amount = dbrow("Amount")
¤ printobj.Name = dbrow("Name")
¤ printobj.Area = dbrow("Area")
¤ printobj.Tel = dbrow("Tel")
¤ printobj.Add()
¤ Next
¤ dbo.Commit() 'ID7 <---NOTE: this issues a ODBCTransaction.Commit()
¤ dbo.CloseConn() 'ID7 <---NOTE: this issues a ODBCConnection.Close()
¤ Dim acobj As New Access.Application
¤ acobj.OpenCurrentDatabase(glob.DBPath, False) <---NOTE: This fails
¤ strangely giving error [1]
¤ acobj.Visible = True
¤ acobj.DoCmd.OpenReport("PrintAMCs", Access.AcView.acViewPreview,
¤ Nothing, Nothing )
¤ 'acobj.Quit(Access.AcQuitOption.acQuitSaveNone)
¤ 'acobj = Nothing
¤ dbo.OpenConn() 'ID7
¤ Catch ex As System.Exception
¤ dbo.Rollback() 'ID7
¤ errmgr.LogMsg(Me.GetType().FullName, ex)
¤ errmgr.ShowError(errmgr.EInternal,MsgBoxStyle.Crit ical)
¤ End Try
¤
¤ End Sub
¤
¤ [1] the error is ->
¤ System.Runtime.InteropServices.COMException (0x800A1EBA): Microsoft
¤ Access can't open the database because it is missing, or opened
¤ exclusively by another user.
¤ at Access.ApplicationClass.OpenCurrentDatabase(String filepath,
¤ Boolean Exclusive)
¤ at NSAMC.MainForm.BtnPrintAllClick(Object sender, EventArgs e) in
¤ C:\data\projects\SharpDevelop\AMC\MainForm.vb:line 979
¤
¤ The interesting part is that when I run the .exe from inside the IDE
¤ (SharpDevelop) it does not
¤ give an error - an Access window pops up and neatly shows me the
¤ report. However, if I simply copy the entire set of files (exe, pdb,
¤ manifest, mdb, dll) in the Debug folder into an independent location
¤ then I get this error. I do not think it is to do with SharpDevelop
¤ because the executables output into the Debug/Release deirectory are
¤ pretty much standard - .exe, .dll,.pdb, .manifest etc.
¤

First, you should not use the Microsoft Access ODBC driver. The Microsoft Jet OLEDB provider is more
stable and offers much better support with respect to database features.

Second, in your OpenCurrentDatabase statement if you try to open the database for exclusive access
when the database is already open, the open will fail.
Paul
~~~~
Microsoft MVP (Visual Basic)

[Joseph S.]

Hi,
thanks for the suggestions.
Paul Clement wrote:

First, you should not use the Microsoft Access ODBC driver. The Microsoft Jet OLEDB provider is more
stable and offers much better support with respect to database features.

Second, in your OpenCurrentDatabase statement if you try to open the database for exclusive access
when the database is already open, the open will fail.

As it turns out, the error was due to wrong data - the wrong database
path was being picked from a text file - it was fixed by simply
changing the data in the text file.

Second, I'll start out with Jet OLEDB.

Thanks,
JS