Solved

Need Access VBA code to delete listings in open recent database

Posted on 2010-09-02
28
689 Views
Last Modified: 2013-11-28
All,

Is there VBA code that can access the registry and delete all the listings in under your Open Recent Database (right side of your Access application)?  If so, may I please have a snippet so that I may copy and paste.

I would like to try to run upon starting up a form so I will put it under the Form_Open Event.

Thank you sincerely for the code.
0
Comment
Question by:stephenlecomptejr
  • 10
  • 4
  • 3
  • +6
28 Comments
 
LVL 57
ID: 33585918
The MRU list is stored in the registry from Access 97 and up.  However the key is version specific, so you'll need to know the version that's running.  Here's the key for Access 2000:
HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Access\Settings

It's the 9.0 that would need to change for the version.  Under that key, you'll find MRU1 - MRU9, which need to be deleted.
 I've got the code for that somewhere and will need to hunt it up or generate it if I can't find it.
In the meantime, someone else may jump in with the code.
JimD.
0
 
LVL 19

Expert Comment

by:darbid73
ID: 33585991
The code you are looking for is here

http://vba-corner.livejournal.com/3054.html

specifically deleting a key is below

Deleting a key from the Registry:

'deletes i_RegKey from the registry
'returns True if the deletion was successful,
'and False if not (the key couldn't be found)
Function RegKeyDelete(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'delete registry key
  myWS.RegDelete i_RegKey
  'deletion was successful
  RegKeyDelete = True
  Exit Function

ErrorHandler:
  'deletion wasn't successful
  RegKeyDelete = False
End Function

Open in new window

0
 
LVL 3

Expert Comment

by:sathisemail
ID: 33586008
Hi,
  Which version of Access are you using and what operating system?.

In windows XP, Access 2007, this is present as shortcut in "C:\Documents and Settings\<user>\Application Data\Microsoft\Office\Recent" folder.

In case from registry, it would be from
Software\Microsoft\Office\12.0\Common\Open Find\Microsoft Access\Settings\Open\File Name MRU
Software\Microsoft\Office\12.0\Common\Open Find\Microsoft Access\Settings\File New Database\File Name MRU

For other versions of Access, the version number in the registry path will change. (12.0 is the version for 2007 office).

The code for deleting from registry is
'deletes i_RegKey from the registry
'returns True if the deletion was successful,
'and False if not (the key couldn't be found)
Function RegKeyDelete(i_RegKey As String) As Boolean
Dim myWS As Object

  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'delete registry key
  myWS.RegDelete i_RegKey
  'deletion was successful
  RegKeyDelete = True
  Exit Function

ErrorHandler:
  'deletion wasn't successful
  RegKeyDelete = False
End Function


0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33586025
Access 2007
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33586058
Yeah but there will be multiple values - correct?

I wouldn't be able just to send a i_RegKey to it - if someone has opened multiple Access databases - correct?
0
 
LVL 19

Expert Comment

by:darbid73
ID: 33586068
To be safe it might be better to check at runtime the version being used.

The version of Acces is shown in the version property for example

Application.version

http://msdn.microsoft.com/en-gb/library/aa173479%28office.11%29.aspx
0
 
LVL 57
ID: 33586097
Well I'll be darned if I can find that code.  A quick hunt turned up the code which you can use:
http://www.vb-helper.com/howto_delete_registry_keys.html
 pretty much as is.  You'll just want to avoid deleting the key itself.  Other then that, it should be a drop in.
 You can check the Access version by using the function:

Function GetVersion() As String
' Determine the version of Microsoft Access
' used to create this application.
 GetVersion = SysCmd(acSysCmdAccessVer)
End Function
This will enable you to know which key you need to clear out the MRU entires for.
Let me know if you need anything else
JimD.
0
 
LVL 19

Expert Comment

by:darbid73
ID: 33586098
<  Yeah but there will be multiple values - correct?

I wouldn't be able just to send a i_RegKey to it - if someone has opened multiple Access databases - correct?>

The list is read when access starts.  Thus if you change the registry it will not appear for any existing access windows.
0
 
LVL 3

Expert Comment

by:bobalob
ID: 33586183
I think the MRU is stored in memory when Access loads, then written back when it closes, so even if you cleaer the MRUs out during Form runtime, they will be written back anyway.

Here's some code to point you in the right direction; you may wish to look at temporarily disabling MRU completely in order to prevent Access from writing the entries back.

Public Sub ClearMRUs()

Const HKEY_CURRENT_USER = &H80000001

Dim strComputer: strComputer = "."
Dim objReg: Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
Dim strKeyPath: strKeyPath = "SOFTWARE\Microsoft\Office"
Dim arrSubKeys

objReg.EnumKey HKEY_CURRENT_USER, strKeyPath, arrSubKeys
 
For Each subKey In arrSubKeys
    If Right(subKey, 2) = ".0" Then
        objReg.EnumValues HKEY_CURRENT_USER, strKeyPath & "\" & subKey & "\Access\Settings", arrChildKeys
        If IsArray(arrChildKeys) Then
            For Each childKey In arrChildKeys
                If Left(childKey, 3) = "MRU" Then
                    objReg.DeleteValue HKEY_CURRENT_USER, strKeyPath & "\" & subKey & "\Access\Settings", childKey
                End If
            Next
        End If
    End If
Next
Set objReg = Nothing

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33614679
bobalob,

I tried your code in a module and ran the Call ClearMRUs from a blank database
and it did not delete any entries shown as the last databases opened.

btw - had to add the following to make it work without any errors -

Dim subKey As Variant
Dim arrChildKeys As Variant
Dim childKey As Variant
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33614698
darbid73,

I tried your code, by putting it in a module and calling it as:

Call RegKeyDelete("HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Settings\MRU1")
Call RegKeyDelete("HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Settings\MRU2")
Call RegKeyDelete("HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Settings\MRU3")

but it fails to delete the registry key vaules and doesn't give off an error.

0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 33710791
stephenlecomptejr,

I found bobalob's code to work "out of the box". Are you viewing the registry to see if the MRU entries have been deleted? If so, the registry needs to be refreshed after the code has run, by pressing the F5 key.

Regards.
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 33710799
stephenlecomptier,

also, you may have got the errors in bobalob's code if you had "Option Explicit" at the top of your module, which is good practice IMHO.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33710992
JD,

Since we are discussing the recent file list.  Any idea why MS chose to limit the number of items in the list to 10 for Access, but to allow you to size the list in Excel, Word and PowerPoint?
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33713662
fredthered,

I'm not trying to make these non-visible to the registry.  I'm trying to make it no longer visible in the Microsoft Access window - shown in the image below.

You said this code workks out of the box?  Did you copy and paste and use it as it was and not get any errors?  How come I did?
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33713664
Please note image here.
msaccessdelete.png
0
 
LVL 19

Expert Comment

by:darbid73
ID: 33713994

Hi Stephen,  

I have just read your question again.  Please note my answer no.33586098 (where I said that access only reads the registry once)



You cannot.  Access reads the registry when it opens, thus if you want to do this in a form the above code has no effect on Access at that stage.  If you close access and reopen it or open a new instance of access then this list will be cleared in the new instance.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33715144
No when I close out and reopen the Access it still shows the list.  The list occurs even when I reboot the machine as well after running the code.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33715195
Here's the attachment of the access file of what I have.  I apologize do not have Access 2003.
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 33715203
Here is the file again.
eraseregistry.accdb
0
 
LVL 57
ID: 33715669
<<JD,

Since we are discussing the recent file list.  Any idea why MS chose to limit the number of items in the list to 10 for Access, but to allow you to size the list in Excel, Word and PowerPoint?>>
 You can size the list in Access.  It's set in database options (general tab I think).
JimD.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33717375
Isn't there an option to not show a recent file list and/or set how many files it shows?

There is in earlier versions of Access.

Seems a bit like overkill to be writing code to change the registery for something like this.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 33719551
Don't forget the Tools - Options - General - Recently Used Files - list has to be unchecked and/or  set to 0 to stop Access from recording any future files once the MRU has been deleted.  That is the case for Access 2003 and I imagine it is much the same for Access 2007.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33719870
JD,

Yes, you can set the number, but the system only allows up to 9 entries.  

What is interesting, when you look at the Registry, is that Word, Excel, and Powerpoint all have a File MRU folder immediately under the application folder.  But Access has no such folder.
0
 
LVL 1

Author Closing Comment

by:stephenlecomptejr
ID: 33742381
Thank you GRayL, this is the best comment that helps my problem.

I can set such to 0 instead of having to manipulate via the registry.  Did not know that!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 33747431
Thanks, glad to help.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 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