activeX component can't create object

[Capitala]

Good day!
I'm writing the following code in Word 2013 VB to connect to MDB database
dim db as database
dim rst as recordset

set db="db path"
set rst= db.openrecordset("Table Name")
In this stage I'm getting the error message :run-time error '4029' activex component can't create object"
I checked libraries, they're OK.
I tried to use (DAO.database) but still the same problem.

Kindly help

 

[The_Doc_Man]

Qualify the database and recordset in the DIM statements

Dim DB As DAO.Database
Dim RST As DAO.Recordset

 

[Capitala]

Qualify the database and recordset in the DIM statements

Dim DB As DAO.Database
Dim RST As DAO.Recordset

Kindly note that I'm using Word 2016 not 2013.

I've already tried this solution, but still the same issue. Thanks

 

[theDBguy]

set db="db path"

What exactly are you using for "db path?"

 

[The_Doc_Man]

Oh, darn, had a senior moment.

You can do a SET DB = "db path" but it is meaningless. That syntax APPEARS to be setting a string value to DB.

You have to do something resembling SET DB = OpenDatabase()... see the details in the link.

Opening an External Database - MS-Access Tutorial

Sometimes you need to work with data in another Access database a dBase IV database or Excel spreadsheet but you donot want a permanent link.

sourcedaddy.com

After the DB is open, then your recordset should be viable.

 

[arnelgp]

use Adodb.Recordset, this is a universal recordset.
add Reference to Microsoft ActiveX Data Objects 2.8 Library, then on your code:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = CreateObject("adodb.connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "thePath + db.mdb here" & ";Persist Security Info=False;"
Set rs = CreateObject("adodb.recordset")
With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
End With
rs.Open "select * from [theTableHere];", cn
With rs
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        Debug.Print rs(0), rs(1)
        .MoveNext
    Loop
    .Close
End With
cn.Close
Set rs = Nothing
Set cn = Nothing

 

[Capitala]

use Adodb.Recordset, this is a universal recordset.
add Reference to Microsoft ActiveX Data Objects 2.8 Library, then on your code:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = CreateObject("adodb.connection")
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "thePath + db.mdb here" & ";Persist Security Info=False;"
Set rs = CreateObject("adodb.recordset")
With rs
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
End With
rs.Open "select * from [theTableHere];", cn
With rs
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        Debug.Print rs(0), rs(1)
        .MoveNext
    Loop
    .Close
End With
cn.Close
Set rs = Nothing
Set cn = Nothing

thanks for your fruitful reply; How about the index and search
Previously I was using the code:
rst.index = "index name"
rst.seek "=", "anything"
if not rst.nomatch then
How such code will be.
Sorry for any inconvenience

 

[arnelgp]

' arnelgp
'
' note:
'
' database:     Documents\test.mdb
' table;        tblCars
' Index:        RegNo
'
' Substitute Your database, table and Index on the code below
'
Dim rs As ADODB.Recordset

Set rs = CreateObject("adodb.recordset")
With rs
    '.Open "select * from [tblCars];", cn
.Open "tblCars", _
                "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & Environ$("userprofile") & "\documents\test.mdb" & ";", _
                adOpenKeyset, adLockOptimistic, adCmdTableDirect
    
    If .Supports(adIndex) And .Supports(adSeek) Then
        .Index = "RegNo"
        .Seek "TMV-167", adSeekFirstEQ
        If Not .EOF Then
            Debug.Print rs(0), rs(1), rs(2)
        End If
    End If
    .Close
End With
Set rs = Nothing

 

[Capitala]

' arnelgp
'
' note:
'
' database:     Documents\test.mdb
' table;        tblCars
' Index:        RegNo
'
' Substitute Your database, table and Index on the code below
'
Dim rs As ADODB.Recordset

Set rs = CreateObject("adodb.recordset")
With rs
    '.Open "select * from [tblCars];", cn
.Open "tblCars", _
                "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & Environ$("userprofile") & "\documents\test.mdb" & ";", _
                adOpenKeyset, adLockOptimistic, adCmdTableDirect
   
    If .Supports(adIndex) And .Supports(adSeek) Then
        .Index = "RegNo"
        .Seek "TMV-167", adSeekFirstEQ
        If Not .EOF Then
            Debug.Print rs(0), rs(1), rs(2)
        End If
    End If
    .Close
End With
Set rs = Nothing

Thanks a mile my dear. I'll work on.

 

[Capitala]

Dear Great arnelgp,
It's working perfect. How about editing the database. How to edit the current record in the MDB file.
Thaaaaaaaaaaaaaaaanks in advance