"Unspecified error" while opening an ADODB connection

Question

I am facing "Unspecified error" in only one PC of my users.

Run-time error '-2147467259 (80004005)':
Unspecified error

I am running the below VBA code to open a connection to Access database located in network shared drive. But the code throws the error whenever it is trying to run the .Open statement.

I thought it is a driver issue and installed Access 2007 Database engine in the user's PC and replaced Provider with "Microsoft.ACE.OLEDB.12.0" but it doesn't work.

Code:

 Dim cn As ADODB.Connection
 Dim rs As ADODB.Recordset
 Dim retVal As Boolean
 Dim strSQL As String
 retVal = False

    On Error GoTo CatchError
     Set cn = New ADODB.Connection

     With cn
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Properties("Jet OLEDB:Database Password") = Initialize.GetDBPwd 'returns pass
     .Open Initialize.GetDbConnectionString 'returns the network DB path
    End With

Is the "network share drive" mapped in Windows? Is the share a Windows / SAMBA share? Do you have the proper permissions on this share and these permissions were entered when you mapped the share? Did you try to open it whith the Access Database on the local machine (for example C:\tmp\)? — Ralph, Aug 12 '15 at 09:26
What happens when you manually try and open that file from the users PC? It could be that the windows user doesn't have access. Is the path a UNC or a drive letter? — Nick.Mc, Dec 22 '15 at 03:23
If it's Windows 7 there's a known issue with network drives where sometimes the drive is still refreshing and files are locked by "another user" as far as I know this still hasn't been fixed in 2015. — SierraOscar, Dec 22 '15 at 16:59
If the problem occurs only on 'one PC' then this PC itself will probably by the source of the problem. Have you looked into [Windows Event Log](https://www.youtube.com/watch?v=J6vUOyxmU1o) on that particulare PC to get more information about what went wrong? Is this one PC somehow specific? Howthis one PC difers from the other PC's where your code works? — Daniel Dušek, Dec 25 '15 at 20:37

ZygD · Answer 1 · 2015-12-22T02:34:17.253

I have the same issue while making an ADODB connection from a local Excel file to another local Excel file.

The only way how I solve it is re-opening the main Excel file from which I do the connection. Nothing else done, and the function works again.

My connection function:

Sub SetConReadOnly(ByRef con1 As Object, ByRef rst1 As Object, sFile As String)
    If con1 Is Nothing Then Set con1 = CreateObject("ADODB.Connection")
    If rst1 Is Nothing Then Set rst1 = CreateObject("ADODB.Recordset")

    con1.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
     "Data Source=" & sFile & ";" & _
     "Extended Properties=""Excel 12.0;HDR=No;IMEX=1;"""
End Sub

_{(I use Office 2013 32-bit, Windows 7 64-bit)}

score 1 · Answer 2 · edited Dec 22 '15 at 21:56

1

What is the version of your Excel?

I had that error, and the solution was changing the provider. Try changing to this:

On Error GoTo CatchError
 Set cn = New ADODB.Connection

 With cn
 .provider = "Microsoft.Mashup.OleDb.1"
 .Properties("Jet OLEDB:Database Password") = Initialize.GetDBPwd 'returns pass
 .Open Initialize.GetDbConnectionString 'returns the network DB path
End With

edited Dec 22 '15 at 21:56

ZygD

22,092
39
79
102

answered Dec 22 '15 at 14:14

Joana Brandão

171
1
7

score 0 · Answer 3 · answered Aug 18 '15 at 06:53

Still haven't found any solution to this issue but it seem the issue is limited to only one user.

I wrote some test macros, in the same file which has the original macros, that open DB connection to local DB file and remote DB file. The test is successful but for some reason that one particular macro still throws that "Unspecified Error" in that specific users machine.

We concluded it is most likely due to a office got corrupted. We will repair the office and hopefully that should fix the issue. Will let you know if this works.

Thanks.

Any results after repairing the Office? – ZygD Dec 23 '15 at 13:30 — ZygD, Dec 23 '15 at 13:30

Anu Abraham · Answer 4 · 2016-09-01T07:07:26.813

I have faced this issue and identified that this issue is specific to a Win7 64 Bit with a 32 Bit Office Installation. The solution I accidentally came across was that If I saved the file manually and rerun the code, it worked seamlessly post that. On trying to incorporate a save in the error handler and calling in the main function again did not work. However saving the file through Application.SendKeys "^s" and then running the file through another thread could replicate the outcome of the manual save.

On further investigation, I identified that the error was because of certain columns in the source being added at run-time before the query and it seems the sql engine queries from a different internal raw source which is not updated with the changes unless the save happens and that was the reason for error.

Insights invited from people who can decode the reason behind the behaviour — Anu Abraham, Aug 11 '16 at 01:21

score 0 · Answer 5 · answered Sep 01 '22 at 11:40

0

I got this error while using same variables in two different modules. It went away when I changed the variable names. It is strange but it worked!! Hope that solves your problem. Thanks

answered Sep 01 '22 at 11:40

Sarfraz

11
4

"Unspecified error" while opening an ADODB connection

5 Answers5