Ctrl Break corrupting accde

[aytee111]

I have created a database with most of the restrictions set:
- accde
- allow bypass key, no
- show db window, no
- allow full menus, no
- allow built in toolbars, no
- allow toolbar changes, no
- allow special keys, no
- allow shortcut menus, no
- custom ribbon - basic stuff, print, exit, sort

There is a long query and I hit control/break to stop it, which it did, but now I am getting that "OnClick" error which shows corruption. Easy enough to fix, that is not the problem, but it seems to happen every time (3 out of 3 so far).

Anyone know how to prevent this? Or a way around it?

(Access 2010, Windows 7, 64 bit)

[CJ_London]

perhaps the code suggested in this link will enable you to ignore ctrl/break or make an orderly exit

http://www.databison.com/prevent-use...ing-execution/

[aytee111]

Thanks for your response. It is not code, unfortunately, it is a query that is running. Not only that, but it is a make-table query using a pass-thru to SQL Server. I tried the same thing on an Access query and everything was fine. They refuse to allow me to split the database so I foresee great trials ahead!

[Micron]

I am getting that "OnClick" error which shows corruption

Don't know what that means. What is it you want to happen? Trap and ignore the Ctrl/Esc key combination? Trap the error message? Something else?

[aytee111]

The OnClick error says something about the procedure tied to the OnClick event isn't working. It is the usual error message when the VBA becomes corrupted.

What I would like to happen is that the ctrl/break works like usual and the query stops executing. I won't be able to trap the error message, it is an Access one and happens from everywhere from that point on, any time there is VBA code being run. I don't really want to stop them using the break key but I suppose I will have to do that if there is no other option. Do you know how to do that?

[Micron]

I'm sketchy on this one. AFAIK, control is given over to the process that's executing the query, thus it might not be code that you need to interrupt. I also think that link is specific to Excel (xlErrorHandler). Is this form driven? The form has keypress events, but is it active and/or has the focus when this query is running so that you can use one of those events? I'm hoping you'll let us know. I'd start by getting code to recognize the esc keypress when a form is the active window, then see if the same code runs when the query is running. If it does, you will have to initiate the query from a form.
P.S. I hope you left yourself a back door in case something goes wrong with this db and you can't get in...

Private Sub Form_KeyPress(KeyAscii As Integer)
If KeyAscii = 27 Then 'I think this is the correct key value
 msgbox "Esc key pressed."
End Sub

[aytee111]

I don't know where the control is when I hit ctrl/break to stop the query running. It is a basic command button that, when clicked, says DoCmd.OpenQuery - nothing fancy there at all. There is error handling but it doesn't get there.

So would your suggestion work? Remember, this is a make-table query from a pass-thru. I would guess that SQL Server has all the control at that moment and that Access is taking a rest? I will add your code and hold my breath!

And yes, this is a throw-away database - fortunately! The accdb remains untouched by all of this.

[aytee111]

Here is that error message, btw.

I must be doing something wrong. I wrote some code that goes around and around and displayed the KeyAscii when I hit ctrl/break - nothing! I put Debug.Print KeyAscii in the KeyUp, KeyDown and KeyPress events. None of them did anything. Not that it is code that I am interrupting but just as a test to see if I knew what I was doing. Obviously not!

[Micron]

I put Debug.Print KeyAscii in the KeyUp, KeyDown and KeyPress events.

Of what? The form?
If that message is related to the query that runs on (which is what I thought you might be saying) it could be the root of the problem. I don't see how it relates to the key press event you're trying to capture.

Anyway, the key press issue might be the key combination, so here's a list of shift, alt and ctrl key combinations
0 None
1 Shift
2 Ctrl
3 Shift + Ctrl
4 Alt
5 Shift + Alt
6 Ctrl + Alt
7 Shift + Ctrl + Alt
You might need to try
If Shift = 2 and KeyAscii = 27...
Don't forget, I said I think 27 is for the esc key. You should check that or use the constants you can find in the Object browser under "keycode". Esc is vbKeyEscape.

[aytee111]

1 - that error message happens from now on - til forever. It is Access's way of saying that the database has been corrupted, every time it tries to go to VBA the message appears. It has nothing to do with pressing that one button to run that one query. It could be "OnLoad" or "OnClose" or the name of any routine.

2 - yes, those OnKey... events were on the form. I don't know where Access is hanging out once I click on the command button and the query starts.

3 - thanks for that list of key values, but I can't seem to find out where to put the code, none of the OnKey... events for the form intercept the ctrl/break

[John_G]

I did a test in a form using the Keydown event (which allows you to see the keycode and shift values, and remember to set Keypreview = Yes for the form), and it worked fine EXCEPT for Ctrl-Break, in which case Access produced the code-interrupted error before the procedure to check the key could be run (the highlighted line was the Sub Form_Keydown... line.

My guess is that the Ctrl-Break press is trapped by the Operating System, before control is returned to Access with the error status, and you cannot trap it in MS Access. (Excel does provide the ability to trap it). This makes sense - if you could trap and ignore Ctrl-Break, then you could not stop a bit of runaway code without having to kill Access with Task Manager.

For example (which most of us have done!), you want to step though a recordset but forget to include the rst.movenext line; theres no way out if Ctrl-Break doesn't work.

[Micron]

Good points. Whether or not one should pursue the goal might depend on how much of that could come in to play. If doable, it's conceivable that one could forget it was in there, and it might come back to bite when the db includes processes that would cause problems. If you're right about what's handling the keypress combination, a Windows API is probably the only way. Come to think of it, I never really asked why not either let the 'long query' run, or figure out why it's taking too long if that's the real issue.

[aytee111]

One does the ctrl/break without thinking, tho, it is just a matter of course. And once the database has become corrupted the user is in trouble - due to the fact that it isn't split. While testing I can recreate the accde easily enough, but once it goes into production no longer an option. Big companies are a pain to work for, too hard to change them.

The query isn't the problem, anything can happen while it is running, network issues or power failures or whatever, and some queries just take long. It doesn't seem to happen on regular Access queries, only this pass-thru (and others presumably).

Thanks to all of you for this discussion, it has been a huge learning experience.

[CJ_London]

A couple of thoughts

Have you tried running as .accdb to reproduce the error? - you mentioned in your first thread

I have created a database with most of the restrictions set:
- accde

I'm just conjecturing here but with a make table query the new table is not actually 'registered' in the table collection until the query has finished running, so ctrl/break cancels the make table and perhaps there is some other code which is trying to reference the table once it is completed?


suggest try create the table as an empty table then run an append query - and ctrl/break on that to see what happens