Execute Query in Read-Only Mode

[dizzydangler]

Hi, all! After receiving a lot of help from ADezzi, Msquared, and NeoPa getting my ms access 2007 db functioning, I've run into another hitch that I haven't been able to solve. The database itself is saved on a MS Sharepoint server, and I would like users to be able to access it and execute the search query in read-only mode. In its current configuration, the query only "works" when the file is opened in "Edit" mode, and I've already had some problems with users inadvertently changing records returned by the user-defined query, which then changes the associated records in the source table.

The problem is, when a search is executed with the file opened in "read-only" mode, the following error message appears:

Error in SearchDB_Click( )

Cannot update. Database or object is read-only.

A simplified version of the VBA code linked to the "SearchDB" command button that builds the search query appears below:

Private Sub SearchDB_Click()
On Error Resume Next
Dim ctl As Control
Dim strSQL As String
Dim strSQL_2 As String
Dim qdf As DAO.QueryDef
Dim qdfDemo As DAO.QueryDef
Const conQUERY_NAME As String = "Search Results"

For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox And ctl.Tag <> "" Then
If ctl.Value Then
strSQL = strSQL & ctl.Tag & ", "
End If
End If

Next

If strSQL = "" Then Exit Sub
CurrentDb.QueryDefs.Delete conQUERY_NAME
On Error GoTo Err_SearchDB_Click
strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM [Table1];"

Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)

DoCmd.OpenQuery conQUERY_NAME

Exit_SearchDB_Click:
Exit Sub

Err_SearchDB_Click:
MsgBox Err.Description, vbExclamation, "Error in SearchDB_Click()"
Resume Exit_SearchDB_Click

End Sub

This code does not include any of the criteria I've built in the "WHERE" statement, but I don't think the problem is here because all of the controls referenced are unbound (Control Source is blank), and I get the same error message whether I run the query with or without the criteria lines.

I don't want this query to update the database, just display selected records. The one thing it does do is allow the user to select which columns to display in the output table--could this be why the program "thinks" that the query is trying to update the database? Any help would be appreciated!

[ChipR]

CurrentDb.Query Defs.Delete conQUERY_NAME
Would this not alter the database?

[dizzydangler]

You're right--it didn't occur to me, but that line deletes the previous query in order to replace it with the new query with user-defined fields. I tried running it without that line and got the error message: Query "Search Database" already exists.

Looks like if I want to give users the ability to select which fields of the record the query displays, they are going to have to open the file in "edit" mode.

[NeoPa]

As a last ditch effort, try looking into Temporary Queries (QueryDefs). You can create them in the code and they don't get assigned a name, yet they are usable. You may be lucky and have this treated as not updating the database as such.

BTW Good spot Chip.

[MMcCarthy]

Joe

If you first create an accde version of your database and put that on sharepoint instead you shouldn't have the vulnerabilities associated with using the database in edit mode. You can set the startup options to hide the database window etc. before creating the accde. Always remember to keep a "clean" copy of the accdb file though as you cannot update or change an accde file.

Mary

[dizzydangler]

NeoPa--I'll look into the QueryDef option. In the meantime, I'll try Mary's idea of saving as an accde. I've already hidden the navigation bar in the version I hang on the server--honestly, the people who use this database know even less about access than I do, so I doubt anyone's going to mess with the VBA code. I'm more concerned about them fooling around with the records already created, but I've tried hard (with all you guys' help) to give this thing as user-friendly an interface as possible, so hopefully the temptation to open the table and start hand-jamming it will be minimal. If everyone just sticks to the form interface, I think we'll be good.

[MMcCarthy]

I'm more concerned about them fooling around with the records already created

The only way I can think of around this is to lock each control individually on the forms. Time consuming but can't think of anything else.

Mary

[NeoPa]

I don't think the form interface is what Joe's mainly worried about Mary. It's those who go directly to the source (the data) via the database window.

[dizzydangler]

Mary, NeoPa's correct--as I think I posted in another thread, the users are all former excel junkies who are more comfortable manipulating a spreadsheet. This is why I've been trying to migrate the data and maintain it in access. We had about 12 different copies of a the old spreadsheet floating around, all with slightly different format, based on whatever the users felt like using when they were updating it. Creating the data entry and search forms has allowed me to control how and where data is entered, and provide users with a way of quickly extracting the data they need without deleting content from or otherwise changing the existing database. It's working well, so far, thanks to all the help I've gotten from you guys--just trying to refine and tweak to make it as robust as possible.

[NeoPa]

It may be helpful then, to ensure the database window is not generally visible to allow them to tweak at that level. Experienced users can get around almost anything you do, but even they can be blocked if you close the door tight enough. NB. Whatever restrictions you apply to them are nearly always set for you too so having a way in for yourself is critical.

On to the details...
In Tools \ Startup there are all sorts of options to disallow the user from gaining access to the raw data. Have fun experimenting, but take a backup first in case you lock yourself out.