?
Solved

lock docmd.Runsql

Posted on 2003-03-05
8
Medium Priority
?
728 Views
Last Modified: 2012-08-13
id like to make it so that docmd.Runsql cant execute

technically, it is for an Access Data Project, and im trapping for certain SQL Statements that can't be run.

0
Comment
Question by:__Holly__
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8074072
An example being ?
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8074371
uh. i want to trap sql statements.

i do a LOT of conversion from MDB->ADP

i want to be able to lock down a sql statement until its been approved.

so i want to replace the RunSql funciton-- like you can do in word VBA.

in other words-- within Access vba--

if i paste a new function inside my project for 'replace' then it will use that function instead of the built in function.

id just like to be able to replace/encapsultate the runSql statement such that whenever vba tries to Docmd.Runsql it goes to my RunSQL sub in order for logging/validation; etc.

my main goals are this:
1. log all sql statements fired to the db.
2. have a custom proc that will allow me to validate sql statements (so that once i go into production, i can block all attempted TRUNCATE statements for example)
3. make this such that i can send a different developer this project, and he doesnt know that anything is up-- he just has to type Docmd.RunSQL and my new function will be inherited.

Like-- in word--

if you dont like the print functionality, you can make a new print sub/function and whenever someone pushes the print button it will call your code.

i just want to know how to do the same thing within the Access object model.

there HAS to be SOME way to do this.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8074442
The RunSQL method is a method of the DoCmd object - it is not a function, so cannot simply be replaced by placing a function with the same name in the application. It may be possible to add a method or replace existing methods of the built-in DoCmd object, but how you do that is beyond my knowledge.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 26

Expert Comment

by:Alan Warren
ID: 8076560
__Holly__


I restrict all access to tables, the only modification allowed is by executing sprocs. I have a standard Insert, Update, Delete, View Stored procedure for each table.
Under these conditions runsql fails.

Alan
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8084582
i think that was i was looking for was close to noexec, but this wont really work either.

shane hit the nail on the head when he said 'it may be possible to replace existing methods'

because thats all that we do within winword that is so cool..

i just wish that someone knew how to do this.

perhaps i should look at maknig the ADP connection properties readonly or something and then have a seperate connection the only runs updates.

i dunno.
ill keep on looking.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 800 total points
ID: 8084701
I have been told that this isn't possible. The best option would probably be (as I originally thought it might) to wrap any calls in a wrapper function but that will obviously not be completely transparent.

I was not aware that you can replace *methods* in the winword object model, but then, I rarely delve into Word except for OLE automation. You can certainly replace functions, however.
0
 
LVL 1

Author Comment

by:__Holly__
ID: 8092042
i gotta give this a little more time.

this really really rock my world, in order to be able to call some API Call that would even just DISABLE a certain function.

if i could shut off entirely docmd.runsql, and then have my wrapper function (runsql) so this, that would rock.

maybe i could just try to lock down ALL updates, inserts, etc-- and then just give everyone the ability to run a sp named RunSql that would then do everything that i needed.



0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8092801
Good plan
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question