Avatar of Natchiket
Natchiket
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Customised Access ribbon leaves application in a vulnerable state

My Access (Office 365) development has a customised application ribbon for switching between forms.  All well and good.  Thought I had everything tied down, hiding the navigation bar and switching off full menus, disabling the bypass key etc.
What worries me is the 'Tell me what you want to do' box which sits obstinately to the right of the main application tab.  Yesterday I typed 'query' in the aforementioned box and guess what, the query designer popped up listing all the underlying tables, inviting me (or any user) to go ahead and mess around with the data without going through the UI.
So, apparently, unless I ditch the ribbon and go back to the time-honoured switchboard menu, the application will always be in a vulnerable state.

Are there any good solutions for this without having to ditch the ribbon (which I spent quite a long time working on)?
Microsoft Access

Avatar of undefined
Last Comment
Anders Ebro (Microsoft MVP)

8/22/2022 - Mon
Gustav Brock

Couldn't you create an accde-version of the application? Remember to backup first.
It will have no design features for the user to access.
Natchiket

ASKER
Thanks Gustav, that does seem like a good solution, although the accde still has the pesky  'Tell me what you want to do' box and does still enable me to bring up the query designer, I can't actually switch to datasheet view, although having said that I just realiased that I cant do that with the accdb either (duh).  OK .. crisis averted, but it will still be nice to get rid of the thing because otherwise users are going to be tempted to try stuff out with it ...
John Tsioumpris

Your Ribbon XML should start
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
 <ribbon startFromScratch="true">

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Gustav Brock

the accde still has the pesky  'Tell me what you want to do'

That's right. An accde is for code protection only.
Daniel Pineault

Short answer, from what I know, is no, there is nothing you can do.  You've taken all the steps you can, but Access remains an insecure tool and anyone serious about breaking into your application can and will do so regardless of the steps you take.
John Tsioumpris

@Daniel can you elaborate on the "insecure"
If i make a custom Ribbon no search
Clipboard01.jpgIf i make a VBA protected ..unless someone knows the trick no access
If i lock the special keys it gets harder also
Also i could obfuscate the code
Finally .accde is pretty much indestructible in order to reverse engineering it.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Natchiket

ASKER
Hi, John, see below


tellmewhatyouwanttodo.jpg
Natchiket

ASKER
I appreciate that Access is not a secure tool, but that box might suggest to the user, that they are somehow going to get help about the application if they type a question there, whereas in fact it gives them help about Access, i.e. creating queries, tables etc
John Tsioumpris

Have you seen my screenshot..no box...the key is the start from scratch = true
Your help has saved me hundreds of hours of internet surfing.
fblack61
Natchiket

ASKER
Yes I have seen your screenshot, John.  Do you seriously think that I don't have have start from scratch = true ?
Where do you think the native Create, External Data, Database Tools, & Help tabs have gone in my screenshot?
Gustav Brock

So how did you manage to get "Tell me what you want to do" back?
John Tsioumpris

You haven't shared some sample of your XML and my crystall ball is out for service...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Natchiket

ASKER
here is the XML .. the  "Tell me what you want to do"  is there despite the XML

<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="onRibbonLoad">
<ribbon startFromScratch="true">
<tabs>
	<tab id="Fusion" label="Fusion">
		<group id="FusionGroup" label="Time and Attendance">
			<button id="Appicon" getImage="GetImageCallBack" size="large"
			tag="frmTAA" onAction="ribOpenTagForm"/>
			<labelControl id="UserId" getLabel="GetUserNameRibbon"/>
			<labelControl id="AppVersion" getLabel="GeAppVersionRibbon"/>
			<labelControl id="DataSouce" getLabel="GeDataSourceRibbon"/>
		</group>
		<group id="Rota" label="Rota">
		
			<button id="WeeklyRota" label="Weekly Rota"
			size="large" supertip="Input/Edit Weekly Rota" imageMso="EditForm"
			tag="frmRotaSQL" onAction="ribOpenTagForm" getEnabled="ControlEnabled"/>
			
			<button id="SEI" label="Self Employed Instructors"
			size="large" supertip="Input/Edit Self Employed Instructors" imageMso="ContactPictureMenu"
			tag="frmRotaSEI" onAction="ribOpenTagForm" getEnabled="ControlEnabled"/>
			
			<button id="StaffOverview" label="Staff Overview"
			size="large" supertip = "View month staff summary" imageMso="SideBySide"
			tag="frmSO" onAction="ribOpenTagForm" getEnabled="ControlEnabled"/>
			
			<button id="ActivityOverview" label="Activity Overview"
			size="large" supertip = "View month activity summary" imageMso="SideBySide"
			tag="frmAO" onAction="ribOpenTagForm" getEnabled="ControlEnabled"/>
			
			<button id="RotaSummary" label="Rota Summary"
			size="large" supertip = "View month rota summary" imageMso="SideBySide"
			tag="frmSummary" onAction="ribOpenTagForm" getEnabled="ControlEnabled"/>
			
			<button id="RotaClock" label="Rota vs Clock"
			size="large" supertip = "View weekly clocking comparison" imageMso="ChangeWorkingTime"
			tag="frmClocking" onAction="ribOpenTagForm" getEnabled="ControlEnabled"/>
			
		</group>
		<group id="Admin" label="Admin" getVisible="GetVisibleCallBack">
		
			<button id="Permissions" label="Permissions" tag="frmUsers"
			size="normal" supertip= "User Permissions" onAction="ribOpenTagForm"/>
			
			<button id="Messaging" label="Messaging"
			size="normal" supertip="Check incoming messages from users"/>
			
			<button id="SysSettings" label="System Settings" tag="frmAttributes"
			size="normal" supertip="View/Change system settings" onAction="ribOpenTagForm"/>
			
			<button id="XLImport" label="Excel Import" tag="frmRotaStaging"
			size="normal" supertip="Import from Excel Rota" onAction="ribOpenTagForm"/>
			
			<button id="JobFamilies" label="Job Families" tag="frmJFA"
			size="normal" supertip="Assign Job Families to Activities" onAction="ribOpenTagForm"/>
			
			<button id="SiteLocks" label="Site Locks" tag="frmUnlockSites"
			size="normal" supertip="Unlock completed Rotas" onAction="ribOpenTagForm"/>
		</group>
	</tab>
</tabs>
</ribbon>
</customUI> 

Open in new window

John Tsioumpris

Great ...when I can i will give it a spin
ASKER CERTIFIED SOLUTION
John Tsioumpris

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Anders Ebro (Microsoft MVP)

I'm not sure I understand what is going on. I've tried your (Natchiket) XML, and I do not see the Tell Me box, so seems weird that you do.

Which version of Access are you running? EDIT: Nvm, I see that you posted 365 earlier.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Anders Ebro (Microsoft MVP)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Natchiket

ASKER
Hi Anders, yes I tried <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

but the "Tell me what you want do" box remains stubbonly fixed
Natchiket

ASKER
Maybe there is some kind of setting elsewhere in the Office suite which affects it?
Natchiket

ASKER
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Anders Ebro (Microsoft MVP)

I don't know. I mean the Tell Me Box is visible for me if I don't load a custom ribbon. Loading the ribbon you posted, I don't see a Tell Me Box. This goes for Office 2019 as well as 365 Insiders Fast.

So maybe your on a different tier of Office 365 than me, which might explain it. Or maybe you are using an add-in? There seems to be something about your install that is different than mine, and others whom have posted here. If you go File->Account, what build of 365 are you using?
Natchiket

ASKER
Hi Anders .. see attached files showing build and other bits and bobs
about_access_2.jpgAbout_Access.jpg
Natchiket

ASKER
add-ins page
add_ins.jpg
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Natchiket

ASKER
Interestingly, John, when I open your database there's no "Tell me .."   box either, intriguing!!
Anders Ebro (Microsoft MVP)

So it sounds like it could be database specific? Could you make a copy of your file, strip out all objects, but the usysRibbon table, and upload that?
John Tsioumpris

Transfer your Ribbon XML to my database ...copy the part after the
<ribbon startFromScratch="true">

Open in new window

of my Ribbon XML
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Natchiket

ASKER
Ok guys I seem to have a solution ... this is very very weird and I don't undersand it but here goes...

In the UsysRibbons table I had two records one to hold the XML for the User ribbon (with startFromScratch="True")  and one to hold the development ribbon (with startFromScratch="False")

The database had some code to set the startup ribbon so that it can be switched between the two modes (development or production)

After I tried out John's DB it did open without the "Tell me what you want to do .." box, so I knew it had to be possible

It seems to be the case that if there is more than one record in the UsysRibbons table (or at least one record with startFromScratch="False") then the database puts in the "Tell me what to do .." box

If there is only one record in UsysRibbons and the XML is (startFromScratch="True") the  "Tell me what to do .." box does not load

Solution:
Create another table which stores the XML and copy the XML into the UsysRibbons table

The code below seems to work (touch wood):

Private Sub optMode_AfterUpdate()

Dim db As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset
Dim strXML As String

On Error GoTo proc_err

Set db = CurrentDb

If Me.optMode Then
    strSQL = "UPDATE tblSystem SET Mode='Dev'"
Else
    strSQL = "Update tblSystem Set Mode='Prod'"
End If

db.Execute strSQL

ChangeProperty "StartupShowDBWindow", dbBoolean, Me.optMode
'ChangeProperty "AllowBuiltinToolbars", dbBoolean, Me.opgMode
ChangeProperty "AllowFullMenus", dbBoolean, Me.optMode
ChangeProperty "AllowBypassKey", dbBoolean, Me.optMode
ChangeProperty "AllowShortcutMenus", dbBoolean, Me.optMode
ChangeProperty "AllowSpecialKeys", dbBoolean, Me.optMode

'Get the XML we want for main ribbon
If IsDev() Then
    strSQL = "Select RibbonXML from tblRibbons where RibbonName='Dev'"
Else
    strSQL = "Select RibbonXML from tblRibbons where RibbonName='Users'"
End If

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

strXML = rst(0)
rst.Close: Set rst = Nothing

'Put the XML in USysRibbons
strSQL = "Select * from USysRibbons where RibbonName='Baba'"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.Edit
rst!RibbonXML = strXML
rst.Update

rst.Close: Set rst = Nothing

MsgBox "System startup set to " & IIf(IsDev(), "Developer", "Production"), vbInformation, fusion_dialogue_title

proc_exit:

Exit Sub

proc_exit_false:

GoTo proc_exit

'
proc_err:
Select Case ErrHand()
Case ErrAbort
    Resume proc_exit_false
Case ErrRetry
    Resume
Case ErrIgnore
    Resume Next
End Select

End Sub

Open in new window



Access SQL doesn't seem to like using LongText fields so I had to use a recordset
John Tsioumpris

Strange scenario ...i am thinking....
Do you start with the Dev XML Ribbon and then with some e.g. base on some entry (checking username ?) you switch to Prod ?
If this is the case you need to Invalidate the Ribbon to reload the XML ( i think ...i haven't tried it)
Natchiket

ASKER
I used to switch between the two Ribbons by setting the start up Ribbon name in code.  The Production ribbon was working because the normal Access tabs (i.e. Create, External Data, Database Tools etc)  were switched off when it was invoked, (and only the FIle, Home and Fusion tabs were shown) so I absolutely knew it was working.  The only problem was the dreaded "Tell me what to do " box was always displayed.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Natchiket

ASKER
Ok time to wrap this up.  Thanks for everyone's help.  I am dividing the points between John and Anders because betweem them I was able to devise a solution.
Anders Ebro (Microsoft MVP)

Glad you got it figured out