Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need Access VBA code to delete listings in open recent database

Posted on 2010-09-02
28
Medium Priority
?
756 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 58
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 58
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 48

Expert Comment

by:Dale Fye
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 58
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 35

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 2000 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 48

Expert Comment

by:Dale Fye
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

636 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