Disable warning: You copied a large amount of data onto the clipboard

Question

While debugging queries written in MS Access 2007 (problem was the same in all previous versions too), I'll run the query and then copy the results into Excel. Depending on results, I switch batch to Access to refine the results and go back into design mode of the query. At this point, I get an annoying warning: you copied a large amount of data onto the clipboard. ...Do you want to save this data on the clipboard? I have never wanted to do this.

The MS Office clipboard is disabled so this function is happening with the standard Windows clipboard. Is there a way to disable the warning and assume No as the default?

Solution 1

There is a very easy solution to this. The warning message only happens if there is a LARGE amount of stuff on the clipboard. Therefore - make sure there is only a small amount of stuff there before you close.

For example, in a macro I wrote I do this:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("iostatZd15.1").Activate
ActiveWindow.WindowState = xlNormal
ActiveSheet.Range("A1").Copy
ActiveWindow.Close

The second-last line (immediately before the close) is a "dummy" - a command to simply replace the current (BIG) clipboard with a very small amount of data. It works.

Solution 2

I think you may have to disable the MS clipboard. Try this:

1. Quit any programs that are running.
2. Click Start, and then click Run. Type regedit and click OK.
3. In the Registry Editor, click to select the following subkey (folder): HKey_CURRENT_USER\Software\Microsoft\Office\9.0\Common\General
4. On the Edit menu, point to New and click DWORD Value. With New Value #1 selected, type AcbControl, and then press ENTER.
5. On the Edit menu, click Modify. In the Edit DWORD Value dialog box, click Decimal under Base. Type 1 in the Value data box. Click OK and quit the Registry Editor.

NOTE: You cannot disable (or enable) the Office Clipboard for only a single Office program by modifying the registry.

Here's the MS KB article

Solution 3

In my experience, you only get this message when you close an application. Are you closing Excel before returning to Access? If so, don't close it and see if you no longer get the message.

EDIT after trying instructions for producing the error:

The only way to avoid the error message is to turn off notifications before entering design view, as in:

  DoCmd.SetWarnings False

And you'd want to turn it back on after you are done with your editing.

But there's no place to run this code, since you're just using the Access UI to edit a query.

I don't quite understand why this warning is considered a problem. Maybe you're pasting, going back to design view, changing criteria, running again, pasting again? If so, turning SetWarnings off might do the trick.

If you wanted it to happen automatically, you could conceivably use the Screen.ActiveDatasheet object to do this. What you'd want to do is write a function:

  Public Function ChangeWarnings(bolSetting As Boolean) As Boolean
    DoCmd.Setwarnings bolSetting
  End Function

...then when you open your query in datasheet view, in the Immediate window, type these two lines:

  Screen.ActiveDatasheet.OnActivate = "=ChangeWarnings(False)"
  Screen.ActiveDatasheet.OnDeactivate = "=ChangeWarnings(True)"

You could also certainly write code that sets this up for you.

One note -- it doesn't "stick" for the Screen.ActiveDatasheet object when opening or closing a different one. It applies only to the Datasheet that is active when you assign the event actions.

Solution 4

You could set the OnClose event of the form up to clear the clipboard.

Put the below code into a module in your database.

Private Declare Function apiOpenClipboard Lib "User32" Alias
"OpenClipboard" (ByVal hWnd As Long) As Long

Private Declare Function apiEmptyClipboard Lib "User32" Alias
"EmptyClipboard" () As Long

Private Declare Function apiCloseClipboard Lib "User32" Alias
"CloseClipboard" () As Long

Function EmptyClipboard()
  If apiOpenClipboard(0&) <> 0 Then
    Call apiEmptyClipboard
    Call apiCloseClipboard
  End If
End Function

Then in the Close event of your form use:

EmptyClipboard

View more solutions

Comments

[Knox]

Thanks for the info. The office clipboard is already disabled, since it's very rare for me to be copying multiple things arounds. I'll edit the question to make that clear.

[Knox]

Hi, David - I certainly respect your input - but no, I'm not closing Excel. Open Access, run query, copy results, open excel, paste results, select Access and select design view, get warning.

[Knox]

Thank you for the suggestion, but I'm running a query directly; there's no form to attach any events to. But maybe I could have a form that clears the clipboard whenever Access gets the focus. hmmmm.

[David-W-Fenton]

The message is coming from Access, so this would have no effect.

[Dour High Arch]

Be careful; this disables all clipboard copying; the questioner said he only wanted to do this “Depending on results”.