Solved

Need Access VBA code to delete listings in open recent database

Posted on 2010-09-02
28
669 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now