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

user_1540019 0
Viewed 194

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
ms-access dao vba
2 Answers

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
user_72968930

Access does not require a reference to the DAO library anymore (since Access 2007, the DAO library is built in). so REMOVE the dao reference. You don't need it, and late binding will NOT help nor change the broken reference issue.

So, you will note the references are now this:

enter image description here

You cannot remove the ACE data engine reference, but you REALLY want to make sure you do NOT reference the DAO object library.

You ONLY reference DAO if you using a pre-2007 database, and ALSO that you NOT use ACE, and using a mdb file.

For accDB, and access 2007 onwards?

The Access team now owns DAO for Access and they do NOT update nor maintain the external DAO library now. DO NOT reference DAO library.

So, DAO is now built into Access. (but yes, you do have to referance the ACE data engine now)

However, EVEN with the above references, you can, and should make a habit of referencing DAO in your code, since it not only a good habit, but also gives you intel-sense as a bonus.

Good idea to have a option explict in your code modules.

So, no need to late bind DAO, since you don't need the reference to DAO anymore!!

user_105270
Related