• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 762
  • Last Modified:

Need Access VBA code to delete listings in open recent database

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
stephenlecomptejr
Asked:
stephenlecomptejr
  • 10
  • 4
  • 3
  • +6
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
darbid73Commented:
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
 
sathisemailCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
stephenlecomptejrAuthor Commented:
Access 2007
0
 
stephenlecomptejrAuthor Commented:
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
 
darbid73Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
darbid73Commented:
<  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
 
bobalobCommented:
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
 
stephenlecomptejrAuthor Commented:
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
 
stephenlecomptejrAuthor Commented:
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
 
John Mc HaleCommented:
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
 
John Mc HaleCommented:
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
 
Dale FyeCommented:
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
 
stephenlecomptejrAuthor Commented:
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
 
stephenlecomptejrAuthor Commented:
Please note image here.
msaccessdelete.png
0
 
darbid73Commented:

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
 
stephenlecomptejrAuthor Commented:
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
 
stephenlecomptejrAuthor Commented:
Here's the attachment of the access file of what I have.  I apologize do not have Access 2003.
0
 
stephenlecomptejrAuthor Commented:
Here is the file again.
eraseregistry.accdb
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
NorieCommented:
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
 
GRayLCommented:
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
 
Dale FyeCommented:
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
 
stephenlecomptejrAuthor Commented:
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
 
GRayLCommented:
Thanks, glad to help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 4
  • 3
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now