[RESOLVED] Failing Opening Access DB run-time error- '2147467259(80004005)'

[Volos]

Hello again,

So now that I've finished my previous project, and migrated everything successfully to an Excel Spreadsheet, I have been "allowed" time to try and get it working with an Access/Excel combination solution.

I'm already failing at opening the DB. Here's the code:

Sub first_shot()

Dim cn As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String

strFile = "E:\LacProj\Dummies and Slaves\Debug\Test.accdb"

strCon = "Microsoft.Ace.OLEDB.12.0; Data Source=" & strFile

''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")

cn.Open strCon  'fail point is the first time I try to do anything
....

I'm getting a run time error when I debug:
Run-time error- '2147467259(80004005)':
Automation Error
Unspecified Error

and when I run it outside of debug I get:
[Microsoft][ODBS Driver Manager] Data source name not found and no default driver specified

So I'm guessing I have more than a few errors. I've looked at other situations with this error, but haven't found one that seemed to fit yet. The first one that I'm guessing is wrong is my name path, particularly the folder "Dummies and Slaves." What is the proper way to handle spaces in this instance? I certainly can rename things, but I'd rather know how to account for that in VBA.

My second guess as to what's wrong is "Microsoft.Ace.OLEDB.12.0;" what driver should it be to talk to an Access '07 (accdb)?

I'm still in the planning stages, so not much is set in stone. My eventual goals are:

1. to have this on a network drive with both MsOf07 and MsOf10 working with the files (though not at the same time).
2. Using a user form started from Excel to control most actions on the Access DB (most people in my lab have never used Access, so I want to try and keep it simple).
3. Be able to select multiple instances from a single table in the DB and insert them into a specific location on a specific sheet in the Excel file
4. Have multiple tables with relevant instances represented on a single sheet, with the number of relevant instances being of a variable length.
5. Generate all the forms I've already got working in excel and email them to the appropriate parties.

All those goals and here I'm stuck on opening the DB. I know I have a lot of hacking to do before I start trouble shooting that list, but any reading or solutions/ideas on those would be appreciated along with opening problem.

Thanks,
Volos

[Volos]

Ok so clearly I'm way off base on perhaps even the correct way to do what I want. Watching an Excel written macro has led me to:

Dim DBPath As String
 DBPath = "E:\LacProj\Dummies and Slaves\Debug\Test.accdb"
 
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;" _
        , _
        "Data Source=" & DBPath & ";" _
        ), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("SPRACdata")
        .SourceDataFile = DBPath
        .Refresh BackgroundQuery:=False
    End With

Which makes me wonder if I even need to open the database in the first place.

[Volos]

Sorry I need to dig more, before I ask. I really don't know what caused these errors, but I'm on to other methods which seem better do the same thing. Example:

    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
     
    ListBox1.Clear
     
    Set cn = New ADODB.Connection
     
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DBPath;"
     
    Set rs = New ADODB.Recordset
     
    rs.Open "SELECT desc FROM Material;", cn, adOpenStatic, adLockOptimistic
     
    rs.MoveFirst
.....
.....
    Set rs = Nothing
    Set cn = Nothing

Sorry about that.