An OLE Automation controller provides at least one mechanism for creating an instance of an object. Creating a SQL-DMO object, specifically an instance of a SQLServer object, is part of almost any SQL-DMO application.
OLE object creation can be a resource-intensive process. It is recommended that you consider the costs of object creation for an application.
All OLE Automation controllers provide a function that creates an instance of a specified object. The Microsoft® Visual Basic® or Microsoft ActiveX® script function is CreateObject. CreateObject has a single argument that identifies the OLE object by application identifier and object class name. The SQL-DMO application identifier is SQLDMO, and the following example illustrates creating an instance of a Database object:
Dim oDatabase Set oDatabase = CreateObject ("SQLDMO.Database")
Using CreateObject does not require an application or project level reference to the SQL-DMO object library. All information necessary for object creation is contained in the function's single argument.
CreateObject represents the least efficient method for object creation and use and should be used only when no other alternative exists. When you use the Visual Basic project reference method to indicate use of the SQL-DMO object library, the Visual Basic keyword, New, can be used to create an instance of a SQL-DMO object. For example:
Dim oDatabase as SQLDMO.Database Set oDatabase = New SQLDMO.Database
Dim oDatabase as New SQLDMO.Database
When the New keyword is used, the Visual Basic application is built so that object creation is accomplished in the most optimal fashion. Further, the Visual Basic compiler can ensure that object references, such as those required to get or set property values, are resolved efficiently.