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
ASPMicrosoft Access

Avatar of undefined
Last Comment
Gareth_Pointon

8/22/2022 - Mon
peter57r

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.
shambalad

Just to eliminate this possibility first, please verify your  permissions to folder \\fileserver\ET_Database.
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
shambalad

Sorry Peter, I hadn't seen your post when I sent mine....
shambalad

Try manually opening the database (i.e. navigate to the folder and double-click it to open it)
Gareth_Pointon

ASKER
We cannot as we dont have access installed on the server.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shambalad

Thought that might be the case. Do you any other computers nearby where you could try that?
shambalad

Also, do you by any chance see a file named 'jobdatabase.ldb' in that same folder?
Gareth_Pointon

ASKER
Yes we do.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
shambalad

Try deleting jobdatabase.ldb. If you can't, it means that most probably someone else has jobdatabase.mdb open right now.
Gareth_Pointon

ASKER
Yes I know they have it open ans thats why I want to just open it in ready only.

Thanks
shambalad

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shambalad

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.
Gareth_Pointon

ASKER
We have loads with Access installed ..
shambalad

Give me a moment, I'll retrieve it.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
peter57r

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.
shambalad

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.
shambalad

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shambalad

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gareth_Pointon

ASKER
there was no solution to accessing an open file.