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!
9 8051 ChipR 1,287
Recognized Expert Top Contributor
CurrentDb.QueryDefs.Delete conQUERY_NAME
Would this not alter the database?
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 32,556
Recognized Expert Moderator MVP
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 14,534
Recognized Expert Moderator MVP
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
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 14,534
Recognized Expert Moderator MVP @dizzydangler
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 32,556
Recognized Expert Moderator MVP
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.
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 32,556
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: William Gill |
last post by:
I have been trying to pass parameters as indicated in the api.
when I use:
sql= 'select * from %s where cusid = %s ' % name,recID)
Cursor.execute(sql)
it works fine, but when I try :
sql=...
|
by: John Moore |
last post by:
Hi,
I must be missing something obvious.
I cannot get this function to run the update query on Line 6, although
the call to run the query evaluates true, and both update_cat_total
functions...
|
by: Norman Fritag |
last post by:
Hi there
The below code executes some queries.
As newbie I was wondering weather you are better of using connection execute
or command execute to execute queries?
I am asking as...
|
by: Robert kempers |
last post by:
Dear readers,
The next code will change the sql command in a query. My question is
how to execute the new query. I have no idea because I have tried a
lot of things.
Please help.
Dim...
|
by: Jack Bauer |
last post by:
How can I execute a query which prompt the user for input?
Something like
SELECT
FROM Table
WHERE Name=
In Oracle PL/SQL, you use & before variable names to have the SQL*Plus
ask for input....
| |
by: Crash |
last post by:
Hi,
..NET v1.x SP1
VS 2003
SQL Server 2000 SP3
Server 2000, XP, Server 2003
I would like to programmatically execute {possibly many} SQL Server
batch scripts. Aka I have many scripts that...
|
by: Shapper |
last post by:
Hello,
I have the tables content and content_localized in a Microsoft Access
database.
I created a Query in my Microsoft Access database named
"content_SELECT":
SELECT...
|
by: alingsjtu |
last post by:
Hello, every body.
When execute dynamic generated multiple OPENQUERY statements (which
linkes to DB2) in SQLServer, I always got SQL1040N The maximum number
of applications is already connected...
|
by: gmccammon |
last post by:
I'm having problems with my Prepare and Execute statements.
I get a syntax error onmy prepare and of course the execute won't run on an undefined value.
I don't know what I am overlooking.
...
|
by: gmazza |
last post by:
Hi there,
I am trying to insert 4 fields into a table grabbing the 4 fields from a form,
here is my code:
Private Sub LastPaymentAmount_AfterUpdate()
Dim CurrentProject As DAO.Database
Set...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |