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, and you may want to 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®/ActiveX® script function is CreateObject. CreateObject has a single argument identifying 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
Or even more simply:
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.