MS Access 2003 Error in loading DLL when performing .Net Interop

Kyle,

 This is out of my wheel house, but what I think I'd try is running as the user from the other machine (or another user than the one have a problem) on this machine (aka a fresh profile), to see if it's related to the users profile.

 I'd also do a "run as admin" to see if it's related to security (directory or registry).

 Last, I'd check the windows event logs to see if anything is being logged.

Jim.   

In general: Error in loading DLL (Error 48).

I would check the dependencies of that DLL.

BUT:

[..] when performing an interop operation from .Net to an access 2003 database.

So you're trying to access an Access database from .NET? What bitness has your .NET application? Does it match the installed Office version? When not, you need to install the appropriate ACE components.

Hi Guys,

So the user has local admin on the computer in question.

The .Net app is compiled for any CPU but runs in 64 bit as that's our processor model (thankfully we have standardized hardware).

However this does work some of the time, and is only showing problems on a few machines which is why I think it's more of a software problem then anything to do with the interop in general.  We are using the office interop from https://netoffice.io/.

Running the program as admin had 0 effect (eg: same issue occurred).

Different (new profile) also didn't have any effect.  Also confirmed that the new profile does work on a different computer.

Nothing of note in the event viewer (Application, System) not yielding anything.

I'm trying procmon but the number of calls this application is doing is making this a nightmare.

All good ideas, and definitely in the weeds with this one.  Been already at it for the last day and just scratching my head so if there are any other suggestions I'm all ears.

The .Net app is compiled for any CPU but runs in 64 bit as that's our processor model (thankfully we have standardized hardware).

As I already tried to point out, this implies that ACE (Access) must be also 64 bit. Did you check this?

E.g. create a 64 bit DSN in ODBCAD32 (64 bit) to verify that you can connect to your database file.

From my understanding The .Net application will pick the right version:  https://netoffice.io/documentation/

If it were truly a bit issue why would it work on some machines and not others given they all have 64 bit processors with 32 bit office?

We are running this successfully on most computers.  But are seeing this for the first time on a small subset of computers.

But it must be installed. If you do a standard Office installation, you'll only have 32 bit ACE. Then your 64 bit application has nothing to connect with.

This is about access interop . . . eg controlling the actual access software / forms from .Net.  We aren't querying access for data.  

Regardless, is there a link to the ACE that you would like me to install?  I could only find the 2010 version.

This is about access interop .

Maybe you should start telling us, what you're actually doing. Post a concise and complete example of what fails.

in .Net:

				_msAccessApp = new AccessApp.Application();
				_msAccessApp.OpenCurrentDatabase(FileLocation);  //filelocation = location of access MDB.
	                        _msAccessApp.Run("ClearForm");
				_msAccessApp.Run("SetLaunchSource", LaunchSource);  //launchsource is the calling .Net application.

	                        var form = _msAccessApp.Forms["frmMain"];
				var ctrl = (AccessApp.TextBox)form.Controls[controlName];  //controlName = the textbox name.
				ctrl.SetFocus();
				ctrl.Text = lookup.ToString();  //some value.
                                 _msAccessApp.Run("OpenCustomerRecord");

Select allOpen in new window

in an access module:

Public Sub ClearLookupForm()
    Form_frmMain.ClearLookupForm
End Sub

Public Sub SetLaunchSource(ByVal Source As String)
    Form_frmMain.LaunchSource = Source
End Sub


Public Sub OpenCustomerRecord()  ' -1 if not there.
    Form_frmMain.OpenCustomerForm
End Sub

Select allOpen in new window

in FrmMain:

Public Sub OpenCustomerForm()
  SearchDatabase
  Forms!frmMain!frmListNew.Form.doDetailClick assignmentId  'ERROR occurs here.  assignmentID is set in searchDB
End Sub

Select allOpen in new window

in frmListNew:

dim assignmentId  as Long

Public Sub doDetailClick(Optional theassignmentID As Long)
assignmentId = theassignmentID
btnDetail_Click
End Sub

Public Sub btnDetail_Click()
  
    If DLookup("CustomerTypeId", "dbo_tblCustomer", "CustomerId = " & Me.CustomerId) = 7 Then
        If CurrentProject.AllForms("frmOldCust").IsLoaded Then
            DoCmd.Close AcObjectType.acForm, "frmOldCust", acSaveNo
        End If
        
        DoCmd.OpenForm "frmOldCust", acNormal, , "CustomerId = " & Me.CustomerId, , , assignmentId
    Else
        If CurrentProject.AllForms("frmCustomers").IsLoaded Then
            DoCmd.Close AcObjectType.acForm, "frmCustomers", acSaveNo
        End If
             
        DoCmd.OpenForm "frmCustomers", acNormal, , "CustomerId = " & Me.CustomerId, , , assignmentId
        
    End If
    
    DoEvents
     
End Sub

Select allOpen in new window

hmm, I'm pretty sure, that this approach also requires that your .NET application runs with the same bitness as your Office installation.

From my understanding The .Net application will pick the right version:

No, it only ensures that AnyCPU has always the correct Office interop loaded. It does not mean that you can run 64-bit application with 32-bit Office.

Again, we already have this working on most PCs.

If it were a bitness issue, I would expect an all or none type of situation.

Did you check it on the failing machine?
Did you check you've deployed the necessary dependencies for that library?

There are no missing references in the VBA project.

Again, if the same access database is loaded manually it works as expected.

Kyle,

<<There are no missing references in the VBA project. >>

  Would you:

1. With Access, check any un-checked reference in the app (doesn't matter which).   Compile, save, and close the DB.   Open back up and un-check the reference you just checked (it will be the last), then compile and save.    Make sure the DB operates normally.   Now try from .Net.

  What this little procedure does is refresh the reference list in VBA.  Sometimes, there will be a problem with reference, but it won't show.

2. Post a screen shot of the references.

 The other thing I'm wonder about is if multiple versions of Office/Access were installed on the machine at any point, or has it always been Office/Access 2003.

Jim.

The error means that a dependency dll is missing or not loading. See the link I've posted.

Hi Jim,

I'm working remotely this week and my team has been putting out a few fires.  I'm waiting on feedback or to get back into the office to check myself.  

Didn't know about adding the reference - one of the many reasons why I'm trying to get off of access, too many corruptions going on.

<<Didn't know about adding the reference - one of the many reasons why I'm trying to get off of access, too many corruptions going on. >>

  Yes, that is Access/VBA's achilles heel; very sensitive to it's runtime environment.

Jim.

<<Didn't know about adding the reference - one of the many reasons why I'm trying to get off of access, too many corruptions going on. >>

I've run somer larger company-wide deployments and only run into this problem, when Office rollouts where  not proberly communicated. So, while being sometimes an issue, it was always a trivial one.

Installs font files and other dependencies

This should be a once in a life time step.

The rest don't need admin rights. Just install it as user application.

Otherwise use a proper installer which can manage this. Using batches requires imho unnecessary risk cause I'm not aware of doing this without handling credentials.

This should be a once in a life time step.

It's not.  If we ever choose to load another font / add dependencies then it breaks our launch process.

Every user has local admin to the PC they run on.  So elevating is not an issue.  But from an elevated command prompt it seems we just need to launch under the current user context.  And actually it's not a dot net issue, but an access issue.  So is there some switch in access that we can use to start under the current user?

Every user has local admin to the PC they run on.

No comment.

So is there some switch in access that we can use to start under the current user?

No.

But it means, that you have installed Offices in way, which is either an unsupported scenario or you need to reinstall it properly. When it is a plain DLL loading error, then some NTFS permissions are maybe messed up.

<<Every user has local admin to the PC they run on. >>

  That's living dangerously.

 There is a process by which you can let users run as non-admins, but still allow them to have admin privs to install an app.

<< But from an elevated command prompt it seems we just need to launch under the current user context.>>

  You cannot.  That has nothing to do with Access, and everything to do with the OS.

Jim.

That's living dangerously.

Pre-2018 we didn't even have AD - all PCs had static IPs and had to run around like a mad man.  Slowly moving things in the proper choice but users are used to "owning" their desktop.  Can't just correct it all at once or there would be too much push back.

To the problem at hand:  closing this for now.  We can probably re-work the update process to only elevate the dependency portion and then fall back to the main calling process.  

Problem had nothing to do with the .net Interop but more to do with how the programs were being launched.

Appreciate the help.

Slowly moving things in the proper choice but users are used to "owning" their desktop.

The correct approach is using two accounts. One normal and one elevated. And teach them to use the elvated only when needed.

<<Can just correct it all at once or there would be too much push back. >>

 Understood.

<<To the problem at hand:  closing this for now. >>

 Somewhere, I do have the documentation on how to create a set of batch files where a user can be a non-admin, but install software as if an admin by use of a batch file.  It was a bit convoluted, but it works great.

 Jim.

FWIW, this is a link to doing a deployment like that:

https://blog.kloud.com.au/2014/12/17/deploying-office-pro-plus-without-admin-rights-2/

Jim.