Late Bound DAO Constants. (1 Viewer)

[ChrisO]

Late Bound DAO Constants.

Late binding is the same for DAO as it is with other references. It allows the current compiler version to seek the “best fit” reference at run-time. In order for the compiler to seek the “best fit” reference the reference must be removed from Tools>References. It is therefore also necessary to remove all direct reference to the reference library in VBA.

As with other late binding, we do not have to develop in a late bound mode. We can make use of early binding while developing and switch to late binding for distribution.

During development, early bound or late bound, DAO constants are not available with Intellisense. The reason for this is that there is no precursor, class, collection, structure (whatever) to instruct the VBA editor from which structure we need to select.

An example:-
CurrentDb.Execute strSQL,
may give us [Options] as a guide but it may not give us a list of options from which to select.

This example gives the following options if we type , DbC. :-

In the above example, DbC.dbFailOnError equates to 128 which is the value of the DAO constant dbFailOnError.

Notes:-
1.)
DbC.dbFailOnError does not override the DAO constant value of dbFailOnError.
They can both exist in the same database without interaction. The reason for this is that, even though DbC.dbFailOnError is Public and has the same name as dbFailOnError, DbC.dbFailOnError is still a member of the structure, class (whatever), of DbC and can not be referred to without the structure precursor, DbC.

2.)
The class clsDbConstants can contain datatypes other than numerical, strings for example.

3.)
The instance of class clsDbConstants, DbC, is self-initialising. Some people like to write this up as self-healing but that is just a play on words for their own personal reasons. The point is that this class instance does not need to be sick before it can be healed. Further to that point, a class is not sick if it has never been initialised. What the reference to the class is, is set to Nothing.

In this case, DbC is defined as :-
Public DbC As New clsDbConstants

Here, DbC will be automatically initialised if DbC is Nothing. That will happen anytime DbC is called even on startup or program reset. The only requirement for initialisation is that DbC is Nothing.

4.)
You will have to manually add constants to the class as needed.
If your database needs a constant then add it to the class, else don’t.

5.)
There is no need to sort entries; Intellisense will do that for us.
Therefore the entries will be sorted by the names of the constants which Microsoft use.


And so on to the code…

Class module clsDbConstants:-

Option Explicit
Option Compare Text


Public dbOpenTable       As Long
Public dbOpenDynaset     As Long
Public dbOpenSnapshot    As Long
Public dbOpenForwardOnly As Long
Public dbOpenDynamic     As Long

Public dbFailOnError     As Long

Public dbLangGeneral     As String
Public dbLangSpanish     As String


Private Sub Class_Initialize()

    dbOpenTable = 1
    dbOpenDynaset = 2
    dbOpenSnapshot = 4
    dbOpenForwardOnly = 8
    dbOpenDynamic = 16
        
    dbFailOnError = 128
    
    dbLangGeneral = ";LANGID=0x0409;CP=1252;COUNTRY=0"
    dbLangSpanish = ";LANGID=0x040A;CP=1252;COUNTRY=0"

End Sub

Some usage:-

Option Explicit
Option Compare Text


Public DbC As New clsDbConstants
'


Sub TestIt()
    Dim strSQL As String
    Dim dbsNew As Object
    
    [color=green]' ---------------------
    ' Do some table stuff.[/color]
    
    strSQL = " INSERT INTO tblMyTable (SomeText)" & _
             " VALUES ('Fred')"
    
    CurrentDb.Execute strSQL, DbC.dbFailOnError
    
    With CurrentDb.OpenRecordset("tblMyTable", DbC.dbOpenDynaset)
        Debug.Print !SomeText
    End With
    
    
    [color=green]' ---------------------
    ' Create a couple of databases.[/color]
    
    Set dbsNew = DBEngine.Workspaces(0).CreateDatabase("C:\FredGeneral.mdb", _
                                                        DbC.dbLangGeneral)
    dbsNew.Close


    [color=green]' I know nothing, I am from Barcelona.[/color]
    Set dbsNew = DBEngine.Workspaces(0).CreateDatabase("C:\ManuelSpanish.mdb", _
                                                        DbC.dbLangSpanish)
    dbsNew.Close
    
End Sub

If you have any questions, please ask them in the appropriate forum.

Chris.