I'm trying to implement late binding on an access front end and can not get a DAO refrence to work

Question

I'm working on making a database late bound, so that when the front end is opened, users with different version of MS Office won't have issues. I keep on getting a run time error 438 (Object doesn't support this properts or method) in this code, on the line with For Each tdf In dbs.TableDefs.

I can't see what is going wrong here. Everything is declared and it should find it. Can someone point out what might be happening?

Function RelinkTables()
On Error GoTo EndFast
'Routine to relink the tables automatically. Change the constant LnkDataBase to the desired one and run the sub
'DB front end could be used in 2 or more locations with different backends.

Dim dbs As Object
#If VBA7 Then
    Set dbs = CreateObject("DAO.DBEngine.120")
#Else
    Set dbs = CreateObject("DAO.DBEngine.36")
#End If
'    Dim dbs As DAO.Database
'    Set dbs = CurrentDb
Dim tdf As Object
'Dim tdf As DAO.TableDef
Dim strTable As String
Dim strLocation As String

'Abandon relinking if file is development version
If VBA.InStr(1, VBA.UCase(GetNamePath), "_DEV") > 0 Then Exit Function

'Get the Path of the Document and form backend name
strLocation = GetFolderFromPath(GetNamePath)
strLocation = FormBackendName(strLocation)

'Go about relinking
For Each tdf In dbs.TableDefs
    If VBA.Len(tdf.Connect) > 1 Then 'Only relink linked tables
       'If tdf.Connect <> ";DATABASE=" & LnkDataBaseDubai Then 'only relink tables if the are not linked right '' With PW, Access wont relink, even when the PW is Correct. MUST RELINK!
            If VBA.Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
                strTable = tdf.Name
                'dbs.TableDefs(strTable).Connect = ";DATABASE=" & strLocation & ";PWD=" & DatenbankPW 'With password
                dbs.TableDefs(strTable).Connect = ";DATABASE=" & strLocation & ";" 'Without password
                dbs.TableDefs(strTable).RefreshLink
            End If
        'End If
    End If
Next tdf

dbs.Close
    Exit Function
    
EndFast:
On Error GoTo 0
    MsgBox "The backend database was not found. Without the backend this database does not work." & vbCrLf _
        & "" & vbCrLf _
        & "Ensure that an Access Backend DB is located in the a subfolder called: ""_Sources"" and that read and write permission for the folder are granted." & vbCrLf _
        & "" & vbCrLf _
        & "Contact the developer if further support is needed.", vbOKOnly Or vbExclamation Or vbSystemModal Or vbMsgBoxSetForeground, "Database backend not found"

End Function

Answer 1

You need to actually open a database if you want to use tables.

You have set dbs to be a database engine, not a database.

If you want it to be the current database, just set it as such, no early binding needed:

Dim dbs As Object
Set dbs = CurrentDb

Else, open up a database:

Dim dbs As Object
Dim dbe As Object
#If VBA7 Then
    Set dbe = CreateObject("DAO.DBEngine.120")
#Else
    Set dbe = CreateObject("DAO.DBEngine.36")
#End If
Set dbs = dbe.OpenDatabase("C:\Some database.mdb") 
' Must be mdb since DAO.DBEngine.36 doesn't support accdb