Cannot access an ACCDB table from Excel

[USAMax]

I have this code that looks like it will do everything I need but it will not make the connection. adStateOpen is always empty.

Function MakeConnection(ImptDB As String, TableName As String) As Boolean
'*********Routine to establish connection with database

Dim DBFullName As String
Dim cs As String

DBFullName = Application.ActiveWorkbook.Path & "\ImptDB"

cs = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

Set cn = CreateObject("ADODB.Connection")

If Not (cn.State = adStateOpen) Then
cn.Open cs
End If

Set rs = CreateObject("ADODB.Recordset")

If Not (rs.State = adStateOpen) Then

   rs.Open TableName, cn, adOpenKeyset, adLockOptimistic

End If

End Function

Sorry but I am new to working with Access from Excel.

 

[USAMax]

I'm sorry but I made a very clear mistake and I corrected it but it still does not work:

Public Function MakeConnection(ImptDB As String, TableName As String) As Boolean
'*********Routine to establish connection with database
'    Workbooks.OpenDatabase Filename:="C:\Users\Dave\Documents\Tibra\DB.accdb", _
        CommandText:=Array("tblInstrumentsInfo"), CommandType:=xlCmdTable, _
        ImportDataAs:=xlTable

Dim DBFullName As String
Dim cs As String

DBFullName = Application.ActiveWorkbook.Path & "\" & ImptDB

cs = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

Set cn = CreateObject("ADODB.Connection")

If Not (cn.State = adStateOpen) Then
cn.Open cs
End If

Set rs = CreateObject("ADODB.Recordset")

If Not (rs.State = adStateOpen) Then

   rs.Open TableName, cn, adOpenKeyset, adLockOptimistic

End If

End Function

Thanks,

 

[USAMax]

I found I was missing a refference, "Microsoft ActiveX Data Objects Recordset 6.0 Library" I add and the adStateOpen is now set to 1!


I am making progress but, now I have a run-tim error '-2147467259 (80004005)'

Unrecognized database format
'C:Users\David\Documents\DB.accdb'

I think it has something to do with the line, "cs = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

Any ideas.... anybody?

 

[USAMax]

I found the problem was with the old OLEDB:

Public Function MakeConnection(ImptDB As String, TableName As String) As Boolean
'*********Routine to establish connection with database

'Dim db As database, rs As Recordset, r As Long
Dim DBFullName As String
Dim cs As String

DBFullName = Application.ActiveWorkbook.Path & "\" & ImptDB

cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFullName & ";"

Set cn = CreateObject("ADODB.Connection")
'Set cn = OpenDatabase(DBFullName)

If Not (cn.State = adStateOpen) Then
cn.Open cs
End If

Set rs = CreateObject("ADODB.Recordset")

If Not (rs.State = adStateOpen) Then

   rs.Open TableName, cn, adOpenKeyset, adLockOptimistic

End If

End Function

Now that I got the connection working I have to find out why rcount is not working but that will have to be another thread.