Upgrading from Access 2010 to Access 2019

rltomalin
rltomalin used Ask the Experts™
on
I currently have 2 or 3 MS Access applications running on networked PC's.
All PC's are running Windows 7 and Access 2010.

I am now upgrading all PC's to Windows 10 and Access 2019.

My question - are there any problems that I can expect running the database with the new application software?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<My question - are there any problems that I can expect running the database with the new application software?>>

 Depends on the app and how it was developed.   It can range anywhere from "no problem" to having to make fairly significant changes (i.e. moving from 32 bit to 64 bit or using ADP's, which were depreciated starting with Access 2013).

Jim.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Generally, the migration will be quite simple if you upgrade to the 32 bit version of Office.  Keep in mind that the default installation of Office is now 64 bit, so you must explicitly select to install 32 bit Office.  You probably do not need the 64 bit version of Office unless you have some massive spreadsheets, so sticking with 32 bit Office would be highly recommended and would result in the least number of changes to your applications (although if your applications don't use any API calls or ActiveX controls, then migrating to 64 bit Office should not create any major headaches).

HTH
Dale

Author

Commented:
Thank you for the heads up.  I have just tried to run one of my applications and it comes up with a reference to needing updates if using 64bit, so I will uninstall and install 32bit.  (I assume that is OK on a 64bit system?) - Maybe I should have asked here first!

My immediate problem is that it doesn't suppress the ribbon on the new system.  The application currently uses what I think is an old technique using a macro (which appears to be empty - I don't know it's before my time) and referenced in the Current Database menu.
I found this reference - https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/how-to-hide-the-ribbon-when-access-starts

I followed these steps but did not seem to work.  I went in to check the table I had created but I can't figure out how to view it now it is created - so I think I will need a bit of help there please.

Any further advice please.

Regards
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
(I assume that is OK on a 64bit system?)

Yes.  You can run either 32 or 64 bit edition of Office under a 64 bit OS.   However note I said "Office", which means everything; Excel, Word, Outlook, and Access.   You can't mix within an Office install.  Everything must either be 32 bit or 64 bit.

I followed these steps but did not seem to work.  I went in to check the table I had created but I can't figure out how to view it now it is created - so I think I will need a bit of help there please.

On the Navigation Pane, right click on its Title bar to show the context menu. From the context menu, click the Navigation Options choice.   On the bottom left of the Navigation Options dialog, check either the “Show System Objects” or “Show Hidden Objects” or both options to show those objects in the Navigation Pane.  Then click OK.

All the Msys* and Usys* tables will now be shown.

Jim.

Author

Commented:
Thanks for the feedback...

(I assume that is OK on a 64bit system?)

Yes.  You can run either 32 or 64 bit edition of Office under a 64 bit OS.   However note I said "Office", which means everything; Excel, Word, Outlook, and Access.   You can't mix within an Office install.  Everything must either be 32 bit or 64 bit.

Actually Access is separate from the other Office products now - we have completely separate licences for 2019.  I assume that there are shared stuff in there so even though they are separate installs they have to be all 32 or 64 bit - is that the case?

What I was considering doing was maybe sticking with the Office 2019 products but installing on older version of Access.  Would they run together?

As to getting at the system tables - I tried exactly that process you describe and nothing was shown.  I tried that in Access 2019 and 2010 - nothing.
Could I be missing something?

Also - is the link that I sent describing the accepted way of suppressing the ribbon?

Thank you
Richard
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Actually Access is separate from the other Office products now - we have completely separate licences for 2019.  I assume that there are shared stuff in there so even though they are separate installs they have to be all 32 or 64 bit - is that the case?

 Yes.   Even worse, from Access 2013 and up, a lot is shared even across versions, which is your other question:

What I was considering doing was maybe sticking with the Office 2019 products but installing on older version of Access.  Would they run together?

  If it was A2010 and prior, it would.   Access 2010 was the last version that you could run in one bitness along with another version.   From A2013 and up, a lot of the DLL's are shared between versions.   With Office 2016 and up it's even worse; the version number is always 16.   Only the build numbers change.

As to getting at the system tables - I tried exactly that process you describe and nothing was shown.  I tried that in Access 2019 and 2010 - nothing.
Could I be missing something?

 Do you see the Msys* tables?  they definitely should be there.  You can't have an Access DB without them.

<<Also - is the link that I sent describing the accepted way of suppressing the ribbon?>>

  If you want to hide it entirely. issue the command:

DoCmd.ShowToolbar "Ribbon", acToolbarNo

  which should do the job.

Jim.

Author

Commented:
Thanks,
So to confirm, if we install Office 2019, it has to be either Access 2019 or Access 2010.?

Richard
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
If you install Office 2019, yes, you'd need to go back to Office 2010 to install it on the same machine.  Even with that, you can only have one install of Outlook (that's always been true no matter what).

 At this point, it's really recommended to put a single Office install on a machine and if you need more, run them in VM's (Virtual Machines).

Jim.

Author

Commented:
OK.  This morning I have uninstalled the 64bit versions of Office and Access and installed the 32bit versions (so using Office 2019 for both)
On an initial run through all seems to be working, which gives me the confidence to choose that configuration.  I am sure there will be other issues but will be fixable once I get to some detailed testing.

However - I still have the problem with hiding the ribbon.
I use a Switchboard, so I entered the command...
DoCmd.ShowToolbar "Ribbon", acToolbarNo
in the On Open event of the Switchboard

This does not hide the ribbon.  However, I notice that if I open the application in development mode (hold down Shift) and then manually open the Switchboard form the ribbon does get hidden.
So I'm pretty stuck now I'm afraid.

Also - this does raise another issue in that if this was working as expected it would hide the ribbon if the Switchboard was opened - which I would not want.  So is there a way to have the ribbon hiding code in some location that gets executed when opening the application normally but not when opening with Shift.

This must be a thing most Access users do, so surely won't be complicated.

I also assume that the previous solution (calling a blank macro) is no longer supported - at least that doesn't work on 2019.

Hope you can give me pointers to help.

Regards
Richard

PS - I did manage to view the System tables (raised further up this dialogue) - finger trouble.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<< So is there a way to have the ribbon hiding code in some location that gets executed when opening the application normally but not when opening with Shift.>>

 AutoExec Macro is still a thing.

 Save what you want in a macro, call it AutoExec, and it will get executed when the DB opens unless you hold down the shift key.

Jim.

Author

Commented:
Hi Jim
I've been trying that without success!!.
I have created a new module with the following code...
Option Compare Database

Public Function SuppressRibbon()
DoCmd.ShowToolbar "Ribbon", acToolbarNo
End Function

I've saved that with the name of SuppressRibbon

Then created a new Macro...


But this fails to run - says "The expression that you entered has a function name that [dbname] can't find"

I've double checked all the spelling.
When I create the Macro it finds the module.

Richard
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I rarely use macros,

If you change the function name (in the macro) from "SuppressRibbon()" to "SuppressRibbon", what happens.

Dale
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
make sure the function is Public and in a standard module.

Jim.

Author

Commented:
Dale - I tried removing the brackets in the macro and when you try to run it it gives an error
Access-error.jpg
Hi Jim
Well I just used create module function and the code in there says Public Function.  Is there anything else I've missed?

Regards
Richard
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
In the VBA editor, press Ctrl/G.

In the debug window (immediate window), type:

? SuppressRibbon()

Does it work, or do you have some type of error?   Note that the module name cannot be the same as any procedure name.

Jim.

Author

Commented:
Hi Dale and Jim

Thanks for your continuing feedback. I am leaving the office now until next Monday and will pickup then.


Regards
Richard

Author

Commented:
Back in the office and taking another look.
To confirm, I have.....
A module named - SuppressTheRibbon
Its contents is...
Option Compare Database

Public Function SuppressRibbon()
DoCmd.ShowToolbar "Ribbon", acToolbarNo
End Function

I have a Macro named - AutoExec
Its contents is...
macro.jpg
If I enter - ? SuppressRibbon()
in the Immediate window of the VBA editor
it DOES remove the ribbon

If I right click > Run on the AutoExec macro
it DOES remove the ribbon

But if I just open the application normally
the Ribbon is displayed.

I cannot see anywhere where the macro needs to be defined to be run on opening.
I understood it gets run by virtue of calling it - AutoExec.

I see this behaviour on both Access 2010 and Access 2019.

Hope you can suggest something.

Regards
Richard
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
I cannot see anywhere where the macro needs to be defined to be run on opening.
I understood it gets run by virtue of calling it - AutoExec.

 It does not get defined.  This is something built into Access itself.   As long as there is a macro called 'AutoExec', it gets executed when you open the DB with Access unless you are holding the shift key down, which is why I'm puzzled.

 Everything you posted looks fine....only thing I can think of is that it's hidden.

 In the nav pane, select All objects at the top.   Make sure it's listed under macros.   Right click on it, select properties, and make sure the hidden box is not checked.

 Can't think of anything else....never had anyone having a problem getting it to run.

Jim.

Author

Commented:
Hi Jim

I have checked and the AutoExec macro does seem to run.  I added a MessageBox step and that displayed fine when the application opened.
But the SuppressRibbon procedure does not seem to execute.

I then added a MsgBox step into the SuppressRibbon procedure - and that executes and displays when opening the application.

So it seems as though it's just the - DoCmd.ShowToolbar "Ribbon", acToolbarNo - step that isn't executed when called on opening the application.

Can you think of any other things that I can try to diagnose this problem

Richard
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I generally hide the ribbons as well, but have not run into this as an issue.

Have you checked your form(s) to make sure there is no "Ribbon Name" provided on the Other tab of the Property Sheet?

Author

Commented:
Hi Dale
No - the application opens the Switchboard form.  I just checked and Ribbon name is blank.  Plus the routine executes OK from within the application.  It's only when executed on opening that it fails to hide the ribbon.

Richard
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
I can't think of anything else, so let's try it another way:

1. Create a simple form.

2. In it's OnOpen event, do a call to SuppressRibbon()

3. Specify the form as your starting for in options.

4. Delete the Autoexec.

Jim.

Author

Commented:
Hi Jim and Dale

I figured it out - or rather found it in one of the many other sites I googled.

Access Options > Current Database > Ribbon and Toolbar Options > Allow Built-in Toolbars

As soon as I ticked that it worked.

Thanks for your help guys.

Author

Commented:
Great support - thanks.  You did answer my initial question and that got me going.
The AutoExec thing - your support was helpful.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial