Solved: Question about Access DB being exclusively locked by VB app

[Jacob Hilderbrand]

Hi

I have a visual studio vb.net app that I am using. Data is stored in an Access database on a shared folder. It is currently an open share and everyone has full access.

When a user saves or retrieves data I us a connection string to pull the data from the database, or save it.

So far it is working fine, and we have multiple users with no issues.

Well today I noticed that there is an LDB file being saved. The thing is, this file stays there until the user completely closes the vb app. I would think that it should close when the Record Set was closed, but maybe I am mistaken.

The only issue I have noticed so far is I tried to open the database to compact and repair in and got an error that the file was opened exclusively by someone else. The db has a password so noone could open it directly.

Here is the code I use to retrieve data:


Dim i As Integer
Dim n As Integer
Dim Fld As Object

Const adOpenForwardOnly As Integer = 0
Const adLockReadOnly As Integer = 1
Const adCmdText As Integer = 1

RS = CreateObject("ADODB.Recordset")
RS.Open(StrSQL, Connect, adOpenForwardOnly, adLockReadOnly, adCmdText)
ReDim DBArray(0 To 0)
If Not RS.EOF Then
DBArray = RS.GetRows
Rows = UBound(DBArray, 2) + 1
Else
Rows = 0
End If

RS.Close()

RS = Nothing


Any ideas on what I am missing?

Thanks

[OBP]

The LDB file is open for as long as any person is logged in to the database, so if the database is open so is the LDB.
You can see who is in the database by opening the LDB with Word.
Sorry I can't help you with your code as I have never used AODB Connection Strings.

[Jacob Hilderbrand]

I can see that different PCs have it open. So, do you know how I can force them to log out of the DB. After I close the record set they have no need to be logged in.

Thanks

[OBP]

Jake, no, sorry, I don't know of any way to force them out.
I always had the same problem when I was at work, people even closed down their computers while still logged in to the database on the Server, or they would just leave their computer on all the time, even through the night with it logged on.

[OBP]

You could look at building in an "Inactivity" macro in the Front End that quits Access after a given period without any Mouse or Key presses.

[Jacob Hilderbrand]

I checked and Access is not running as far as I can tell. When I actually open access there is a msaccess.exe process running. But with the vb app that process is not running even tho I or any other user appears to be logged in to the access database.

??

[Tommy]

It looks to me like there is no connection. You are creating the recordset with an implied connection which will not get released until you close the app that created it.

http://msdn2.microsoft.com/en-us/library/aa141406(office.10).aspx contains some documentation on ADODB in vb.net

[Jacob Hilderbrand]

Ahh, that was the problem. Once I made an explicit connection object and closed it, everything is working the way I wanted. I will post the code here in case it could help anyone else later.


Dim RS As Object
Dim Conn As Object

Conn = CreateObject("ADODB.Connection")
Conn.ConnectionString = Connect
Conn.Open()

RS = CreateObject("ADODB.Recordset")
RS = Conn.Execute(StrSQL)

'
'
'
'
'

RS.Close()
Conn.Close()

RS = Nothing
Conn = Nothing


The Connect variable is defined elsewhere as:


Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _
DBPath & ";Jet OLEDB:Database Password=" & DBPassword & ";"


Thanks :)

[omocaig]

I'm glad to see you got your issue sorted. I just wanted to share this code for kicking out users because the question came up. I haven't actually tested it myself, but I've seen the authors work and I know his code is solid.

apply the boot (http://www.mrexcel.com/board2/viewtopic.php?t=251154)

hth,
Giacomo

[OBP]

omocaig, thanks for that, it is similar to my suggestion in post #5, but much easier to implement and more controllable for the Administrators. I wish I had known of this 7 years ago when Access 2000 came out.