Office365 - MDB to ACCDB Issue (1 Viewer)

kevnaff

Member
Local time
Today, 12:14
Joined
Mar 25, 2021
Messages
70
Hi All.

So our company is upgrading for Office 2010 to Office 365. I have attempted to open the .MDE Front End that we currently use and get a message about the version not being compatible. My plan was to upgrade the Front End, Back End, and Design Copy that I use all to .ACCDB files.

I started by converting the Design Copy to .ACCDB and I currently get a Compile Error attached below.

365 2.PNG



The issue is with Dim db As Database. I am aware that the references in the database can cause some issues like this if the correct ones aren't selected, so below are the references currently selected.

365 3.PNG



Any assistance would be greatly appreciated.
 

Minty

AWF VIP
Local time
Today, 12:14
Joined
Jul 26, 2013
Messages
8,827
The below is from a current O365 install but you should be able to add the ver 14 of the missing one.

1631185694223.png
 

Ranman256

Well-known member
Local time
Today, 07:14
Joined
Apr 9, 2015
Messages
3,936
Add reference: Microsoft Office x.x Access Database Engine Object Library,
 

kevnaff

Member
Local time
Today, 12:14
Joined
Mar 25, 2021
Messages
70
Hi Both.

When I select Microsoft Office 16.0 Access Database Engine Object Library and select OK, I get an Error in loading DLL File message.

Is there a .dll file that I need to download do you know?

Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:14
Joined
May 7, 2009
Messages
14,153
what is your office x32 or x64.
you can browse for the dll.

C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL
 

kevnaff

Member
Local time
Today, 12:14
Joined
Mar 25, 2021
Messages
70
H
what is your office x32 or x64.
you can browse for the dll.

C:\Program Files\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL
Hi Arnel,

I don't have the .DLL file inside this folder on my PC. However on my colleague's PC which has been updated to the new OFFICE 365, I have selected Microsoft Office 16.0 Access Database Engine Object Library, and I don't get a .DLL error, so all is working OK on the new update.

However now that I can select Microsoft Office 16.0 Access Database Engine Object Library, I am now trying to create an .ACCDE file from this .ACCDB file and I am receiving the following error:

365 4.jpg


A message bar does not show up to allow me to trust the source of the database.

Does anybody know how to trust the .ACCDB file?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:14
Joined
May 7, 2009
Messages
14,153
close your db and reopen it.
if you see a macro warning (yellow bar), make sure you enable the macro.
create the accde.
 

kevnaff

Member
Local time
Today, 12:14
Joined
Mar 25, 2021
Messages
70
close your db and reopen it.
if you see a macro warning (yellow bar), make sure you enable the macro.
create the accde.

Hi Arnel,

Unfortunately I don't see a macro warning.

Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:14
Joined
May 7, 2009
Messages
14,153
go to Access Option->Trust Center->Trust Center Setting->Trusted Location
add the folder where your db is located.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:14
Joined
Feb 19, 2002
Messages
33,281
Is the .mdb using DAO or ADO?

ALL database object definitions should be disambiguated in case both DAO and ADO are used in the same application. To do this, Make a backup of the .mdb and zip it. Then change all DAO or ADO objects to include DAO or ADO as the library. For example

Dim Rs AS DAO.Recordset
Dim db AS DAO.Database
etc,

compile the app, test it and try converting again.
 

kevnaff

Member
Local time
Today, 12:14
Joined
Mar 25, 2021
Messages
70
Hi Both.

So first, the option to add a Trusted Location was greyed out and not available.

Secondly, I opened the .ACCDB inside of Office365 and everything opened fine, with all of the references working OK.

I've compiled the .ACCDB and no errors were picked up.

Thanks both for your help.



Now the next issue is that we currently use a .MDW file which are no longer available to use with .accde files.

So before updating all the .mdb and .mde .accdb or .accde files, my .mdb backend was in the 2002-2003 format, and my .mde front end was in the 2007 format. I am now wondering whether the fact my .mdb backend was in the 2002-2003 format, whether this was causing the original error message about it not being compatible.

Is there a way to save a 2002-2003 .mde or .mdb file as a 2007 .mde or .mdb?

This way we can keep our currenty .mdwfile which will allow user level security.

If not, is there a solution anybody knows of, to add user level security to a .mdb file?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:14
Joined
Feb 19, 2002
Messages
33,281
Is there a way to save a 2002-2003 .mde or .mdb file as a 2007 .mde or .mdb?
The .mde is a compiled version of the database and you cannot convert it to anything elxe since all the source code has been removed. To convert a database, you open the newer version of Access and navigate to the .mdb file and open it. Then you can do a save as to do the conversion.

Advice: Make sure that everything compiles in the .mdb. You don't want to have to try to suss out compile errors as part of the conversion. If all the modules do not already have "Option Explicit" in the header, make that change in the MDB. As always, make a backup and zip it so you always have a recovery point. Adding Option Explicit will force you to declare all your variables. Take the time to do it. When you don't declare your variables, you frequently don't fine errors until run time when the user calls to complain.
 

kevnaff

Member
Local time
Today, 12:14
Joined
Mar 25, 2021
Messages
70
you programatically implement it.
Thanks Arnel, I've followed the advice from the following link:

https://www.datanumen.com/blogs/how-to-protect-your-sensitive-data-with-a-login-form-in-access/

It allows me to add a login screen upon starting the database, which is perfect for what I need.

I currently have many textboxes which display the current user and the time on which a record is last edited. This keeps a trail of who and when the record was last edited. The current user was previously taken from the workgroup file.

As I can no longer use the workgroup file, is there a way to substitute the new username from the login screen, for the current user from the workgroup file?

Thanks again
 

kevnaff

Member
Local time
Today, 12:14
Joined
Mar 25, 2021
Messages
70
The .mde is a compiled version of the database and you cannot convert it to anything elxe since all the source code has been removed. To convert a database, you open the newer version of Access and navigate to the .mdb file and open it. Then you can do a save as to do the conversion.

Advice: Make sure that everything compiles in the .mdb. You don't want to have to try to suss out compile errors as part of the conversion. If all the modules do not already have "Option Explicit" in the header, make that change in the MDB. As always, make a backup and zip it so you always have a recovery point. Adding Option Explicit will force you to declare all your variables. Take the time to do it. When you don't declare your variables, you frequently don't fine errors until run time when the user calls to complain.

Thanks Pat, makes perfect sense. I have imported the .mdb in to a new blank database and I've compiled it, and there are no errors. I will definitely add option explicit to the modules and ensure all works perfectly before any users make the switch over.
 

Users who are viewing this thread

Top Bottom