Link to home
Start Free TrialLog in
Avatar of Gareth_Pointon
Gareth_Pointon

asked on

DSN or DSN-Less Read only connection

We have a loced access DB thats been used by other programs on the network.

I have a ASP scrip that just needs to read the information in and there and thats it.

I need to force the ASP script to be Read Only on the ASP as I'm getting the following error:

The Microsoft Jet database engine cannot open the file '\\fileserver\ET_Database\jobdatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

I have set the lock types to Read only but this is failing on the connection.

This is the connection code:

MM_ET_DB_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\fileserver\ET_Database\jobdatabase.mdb;adModeRead"

Open in new window


Is there a way we can force this to open the DB in Read only and ignore the access DB lock file?

Thanks
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Is the message correct?

In particular, has Access' built-in user level security been applied to the database?
If so, and you are not set up as a user then you cannot get in.  You have to supply a valid username and password.

If the database were opened exclusively, I would have expected you to automatically get read-only access; although I don't really use ADO for Access databases in my environment so I can't be totally sure about that.
Just to eliminate this possibility first, please verify your  permissions to folder \\fileserver\ET_Database.
Avatar of Gareth_Pointon
Gareth_Pointon

ASKER

Hi,
We can view the DB from the server OK as there is Everyone (Full Access) on the Folder and File.

There is no Access User level security on there.

Thanks
Sorry Peter, I hadn't seen your post when I sent mine....
Try manually opening the database (i.e. navigate to the folder and double-click it to open it)
We cannot as we dont have access installed on the server.
Thought that might be the case. Do you any other computers nearby where you could try that?
Also, do you by any chance see a file named 'jobdatabase.ldb' in that same folder?
Yes we do.
Try deleting jobdatabase.ldb. If you can't, it means that most probably someone else has jobdatabase.mdb open right now.
Yes I know they have it open ans thats why I want to just open it in ready only.

Thanks
The problem is, if they have the database open with an *Exclusive lock on it, you won't be able to open the mdb even for readonly.
I have a routine that can determine who it is that has the database open, but it has to be run from a computer that has Access installed.
We have loads with Access installed ..
Give me a moment, I'll retrieve it.
If there is a .ldb file present then the database is NOT opened exclusively. The ldb file enables sharing and is not created if the db is opened in Exclusive mode.

I'm not an asp person so it will be of no use to me, but I think we need to see more of your code and how you are using the connection.
Yes, it's true, an ldb is not automatically created if a person opens the database exclusively and there is no ldb already present in the folder. But, if an ldb is already there (and orphanned ldb's are known to occur), and the person signs on exclusively, they will lock it.
OK, if someone, in fact, does have an exclusive lock on the file, you probably won't be able to actually use this routine, but that, in itself, could be a good clue.
Anyway, this is a useful routine to have around.
I apologize for the time taken to dig this up. I am working in Access 2010 on a Windows 7 box so I cannot easily replicate your circumstances. I've got another PC running Access 2003 over Windows XP, but it's buried somewhere.
Anyway, give this routine a shot and tell us what you get.
Thanks

Public Sub ShowLoggedOnUsers(Optional strPath As String)
      Dim cnn As ADODB.Connection
      Dim rst As New ADODB.Recordset
10    On Error GoTo ErrorHandler

      ' Outputs list of logged on users to the immediate window
      ' If no path is passed into sub, the current db is used.

20    If Len(strPath) = 0 Then
30       Set cnn = CurrentProject.Connection
40    Else
50       Set cnn = New ADODB.Connection
60    End If

70    With cnn
80       If Not Len(strPath) = 0 Then
90          .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
100      End If
         
110      Set rst = .OpenSchema(adSchemaProviderSpecific, , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
         
         ' The user roster is exposed as a provider-specific
         ' schema rowset in the Jet 4 OLE DB provider.  You have to use
         ' a GUID to reference the schema, as provider-specific schemas
         ' are not listed in ADO's type library for schema rowsets

120      With rst
130
         'Output list of all users in the database to the immediate window

140         Debug.Print .Fields(0).Name, "", .Fields(1).Name, _
               "", .Fields(2).Name, .Fields(3).Name

150         While Not .EOF
160            Debug.Print .Fields(0), .Fields(1), _
               .Fields(2), .Fields(3)
170            .MoveNext
180         Wend
190      End With
200   End With

ExitSub:
210   On Error Resume Next
220   rst.Close
230   Set rst = Nothing
240   Set cnn = Nothing
250   Err.Clear
260   Exit Sub

ErrorHandler:
270   MsgBox "Error: " & Err.Description & " (" & Err & ") at line " & Erl
280   Resume ExitSub
End Sub

Sub TestShowLoggedOnUsers()
      Dim strPath As String
10    On Error Resume Next
20    strPath = "\\fileserver\ET_Database\jobdatabase.mdb"
30    ShowLoggedOnUsers
End Sub

Open in new window

Just wondering if you ever arrived at a resolution for this problem...
ASKER CERTIFIED SOLUTION
Avatar of Gareth_Pointon
Gareth_Pointon

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
there was no solution to accessing an open file.