Solved

Need Access VBA code to delete listings in open recent database

Posted on 2010-09-02
28
722 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 20

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
SuperAntiSpyware Licenses Discounted by 25% !

Exclusive offer to Experts Exchange Members!
Buy SuperAntiSpyware License(s) from us and save 25% on the regular purchase price.
- Includes Full SuperAntiSpyware Vendor Support Entitlements
- Your Subscription does not begin until you activate your license
- Buy for your friends

 
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 20

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 20

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 20

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 34

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

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!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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…

739 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