"Unspecified error" when opening an ADODB connection

Question

I encountered an "Uncertain Error" in only one of my users' PCs .

Runtime Error '-2147467259 (80004005)':
Undefined error

I am running the following VBA code to open a connection to an Access database located on a network shared drive. But the code throws an error whenever it tries to run the .Open .

I thought that this was a problem with the driver and the Access 2007 database access mechanism was installed on the user's PC and replaced the provider " Microsoft.ACE.OLEDB.12.0 ", but it does not work.

The 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 

Answer 1

I have the same problem when connecting ADODB from a local Excel file to another local Excel file.

The only way I solve this is to reopen the main Excel file from which I connect. Nothing more is 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 am using 32-bit version of Office 2013, 64-bit Windows 7)}

Answer 2

What is the version of your excel?

I had this error and the solution changed the provider. Try changing 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 

Answer 3

Still no solution to this problem has been found, but it seems that the problem is limited to only one user.

I wrote several test macros in the same file that has the source macros that open the database connection with the local database file and the remote database file. The test is successful, but for some reason, one specific macro still throws this "Unspecified error" on this particular user's computer.

We came to the conclusion that this is most likely due to the office being damaged. We will repair the office and hopefully this should solve the problem. Notify if this works.

Thanks.

Answer 4

I ran into this problem and determined that this problem is specific to 64-bit Win7 with 32-bit Office installation. The solution that I accidentally ran into was that if I saved the file manually and re-run the code, it would work smoothly. When I tried to enable saving in the error handler and the call to the main function did not work again. However, saving the file through Application.SendKeys "^ s" and then running the file through another stream may replicate the result of the manual save.

In the course of further research, I found that the error was due to the fact that some columns in the source are added at runtime before the query, and it seems that SQL queries are launched from another internal source that is not updated with changes, if only saving occurs , and this caused the error.