Excel VBA - Access accdb

[JRinderer]

I got some solid advice on setting up data connections and using late binding; I've had almost no issues since then. However I have one user who is unable to run a small section of code that basically INSERTS into a database. The error message is:

Run-time error '-2147024770 (8007007e'):

Metohd 'Open' of object '_Connection' failed.

The reference library is:

Visual Basic for Applications
Microsoft Excel 15.0 Object Library
OLE Automation
Microsoft Office 15.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft ActiveX Data Object 2.0 Library

The code that is erroring out is:

Dim accsApp as object
    Set accsApp = CreateObject("ADODB.Connection")


    accsApp.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
         "Data Source=\\some_Shared_Location_User_Has_Access_to.accdb

For the sake of covering all bases the user is able to run the code that SELECTS from Access, which looks like:

Set accsApp = CreateObject("Access.Application")
    accsApp.OpenCurrentDatabase path

userId = getDataRev("Users", userNme, "UserId", "UserName", accsApp) 'THIS FUNCTION IS BELOW IN THE NEXT CODE BLOCK

Public Function getDataRev(tble As String, idSrch As String, fldSrch As String, idFld As String, appAcc As Object) As Long
    Dim rs As Object
    
    sql = "SELECT [" & fldSrch & "] FROM " & tble & " WHERE " & idFld & "=" & Chr$(39) & idSrch & Chr$(39) & ""
    Set rs = appAcc.currentdb.openrecordset(sql)
    getDataRev = rs.Fields(0).Value
    
    rs.Close
    
End Function

This is the only person having this issue. To add some more confusion I've been converting some of our programs to Python, and this same user is unable to run the code there. It's the same program but in Python. I wrote a script to extract the drivers she has, and she has the necessary Access drivers.

So far from reading the information I've found on the web I can't verify any one solution. Drivers can't be the issue can they? The user is able to run the SELECT scripts without issue. I've added ActiveX 2.8 to the reference library.

Further information
User is on 32 Bit Office 2016
64 Bit OS Windows 7 Enterprise

Is it possible it's due to an Office update? The user was telling me they were skipping updates; since I'm not corporate IT I don't bother tracking down every person who doesn't update; but maybe Office is in some state of limbo since updates are being pushed off?

 

[mrmmickle1]

haven't used this code in a few years but in my project I remember having an issue with compatibility between different software versions. I think there have been other releases as well. I would start by changing the connection string based on application.version:

   If Application.Version < 14 Then
            gadoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & CStr(strDBPath) & ";Jet OLEDB:Database Password=" & strDBPass & ";"
   Else
            gadoConn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & CStr(strDBPath) & ";Jet OLEDB:Database Password=" & strDBPass & ";"
   End If

You may also want to check if the Employee has the driver installed:
[FONT=&quot]http://www.microsoft.com/en-us/download/details.aspx?id=13255

or potentially use: [/FONT]Microsoft.ACE.OLEDB.16.0

Not sure if these will work but might get you looking in the right direction. Hope this helps.

 

[RoryA]

Your SELECT queries are actually opening Access and running the queries in the application, whereas the INSERTs are using ADO. References should be irrelevant since you're late binding (I'm not really sure why you have set the references at all, given that)

I would suspect that you have a provider problem. You might see if you can run this on the problem machine: https://support.microsoft.com/en-gb...e-problems-and-reconfigure-mdac-installations

 

[JRinderer]

I'll test both of your suggestions next week. The users are only in this form once a week, and getting them to run through tests is like pulling teeth. I think RoryA is spot on, since the problem also exists in Python; and I know she has the drivers. Maybe the issue is the drivers weren't installed correctly.

I'll try mrmmickle's suggestions first and see if I can find the driver that way. Maybe it's a version thing? I'll update the thread with what happens next Thursday. Thank you both for the suggestions