DLL error? Can't connect to Access accdb

[Air_Cooled_Nut]

I'm not sure if this belongs in the Access forum but the issue is occurring within Excel.

Snippet of Excel code:
Dim moConn As ADODB.Connection, moRecordset As ADODB.Recordset
Set moConn = New ADODB.Connection
Set moRecordset = New ADODB.Recordset
moConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sSource & ";Extended Properties=Excel 8.0;Persist Security Info=False"

I'm getting the following error when the moConn.Open line is hit:
-2147220999 Method 'Open' of object '_Connection' failed

The error description for the number is "Error in the DLL".

I'm running Windows XP SP3, Excel 2003, Access 2007, MDAC 2.8 SP1. References used (in order):
Visual Basic For Applications
Mircorsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine Object Library
Microsoft OLE DB provider for OLAP Services connection dialog 8.0
Microsoft ActiveX Data Objects 2.8 Library

There's no security around the database (no login/password).

What is wrong and how do I fix it? I've searched the web with many dead-ends & no solutions/fixes so I'm hoping this has happened with someone else and they found a solution...or at least a reason.

 

[Richard Schollar]

Hi Toby

Your extended properties need to be enclosed in quotes:

moConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sSource & ";Extended Properties=""Excel 8.0"";"

I also removed the persist security info bit.

 

[Air_Cooled_Nut]

Thanks for that bit, I made the correction, however the error still persists. I even used
moConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sSource & ";"
but I still got the same error. I get the same error when trying to connect in a VB Script. Uhg.

If this is a DLL issue, which one is it? I'll try re-installing it. I did repair Office and Access 2007, each to no avail.

The file path is valid and I have full connectivity:
\\CCReportingDev\test\issueresolved.accdb
Odd thing is, I have no issues running this on my other system and they're set up with the same software and versions. Are there files I should version compare?

 

[Richard Schollar]

What's the value of sSource? Can you post all of the code? What I will do is, using your data, mock up a test environment with excel workbook and check to see if I can recreate the error.

 

[Air_Cooled_Nut]

I believe this is all the pertinent info necessary as it's the connection that fails. This code has been working fine for the last several months. The last updating that I did was for Oracle 9i ODBC drivers but I can't be positive that the failures occurred directly after that update or not.

Option Explicit
Option Private Module
Private moConn As ADODB.Connection
Private moRecordset As ADODB.Recordset
Private Const RESOLVE_DB = "\\celio\share\test\csat pilot\issueresolved.accdb"

Private Sub InsertIssueResolved()
'------------------------------------------------------------------------------
' inserts the Issue Resolved data into the table.
' \\celio\share\test\CSAT Pilot\data\Issue Resolved & Support Issue Was Not Resolved
'------------------------------------------------------------------------------
Dim oRange As Range

ActiveSheet.Rows(2).Delete
Set oRange = ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1)

'Call EraseUnwanted(oRange)
Call InsertIntoDB(RESOLVE_DB, oRange, "IssueResolved_NotResolved", True)
ActiveWindow.Close False
Set oRange = Nothing

Application.StatusBar = "Issue Resolved completed."
Beep
End Sub

Sub InsertIntoDB(sSource As String, oRange As Range, sTable As String, Optional bDelDups As Boolean = False)
'------------------------------------------------------------------------------
' Sub InsertIntoDB
'
' Inserts Excel data into the given database.
'------------------------------------------------------------------------------
Dim i As Integer, x As Integer
Dim iRowCount As Integer, iColCount As Integer
Dim sSQL As String, bSkip As Boolean
Dim sValues As String, sWHERE As String, sSELECT As String

On Error GoTo errInsertIntoDB

Set moConn = New ADODB.Connection
Set moRecordset = New ADODB.Recordset
'Set moRecordset = CreateObject("ADODB.Recordset")

moConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sSource & ";"

moConn.BeginTrans

iRowCount = oRange.Rows.Count
iColCount = oRange.Columns.Count
...

 

[Richard Schollar]

Toby

As far as I can tell there is nothing worng with your syntax - the full Connection String to access an Access2007 accdb is:

'per www.connectionstrings.com:
 
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

But yours complies fully with this (and in any case having the Persist Security Info on the end seems to be optional as it works for me without it).

Hence, if it is your dll that is at fault, I believe it is probably this one:

Microsoft ActiveX Data Objects 2.8 Library

But I'm going to ask somebody else to take a look at this who is much more knowledgeable than me.

 

[RoryA]

I would tend to agree. The syntax itself is fine as written.
I would see if you can download the latest MDAC_type.exe from MS and reinstall it.

 

[Air_Cooled_Nut]

Unbelievable...trying to find a way to reinstall MDAC 2.8 was maddening. Even the Microsoft website was useless! Then I stumbled across this page:
http://www.pqsystems.com/kb/activekb/questions/165/GN%3A+MDAC+Information
It worked! I was able to reinstall/repair my MDAC. I then stepped through my VB Script that used ADO and it worked -- as we say here at work, "YAHOO!"

To those looking for addtional information about MDAC:
http://msdn.microsoft.com/en-us/data/aa937729.aspx

MVPs, thanks for your help