ListManager.CreateFromSql

Use this method to create a list from a SQL query.

Definition

Function CreateFromSql(listName As String,listDesc As String,lFlags As Long,userFlags As Long,connStr As String,queryStr As String,errLimit As Long,bAsync As Boolean,pvarOpID As Variant) As Variant

Parameters

listName

A String that provides the name for the list.

listDesc

A String that provides a description for the list.

lFlags

A Long that specifies the bitmask flags that control the type of list created.

userFlags

A Long that contains application specific user flags. Not used by the ListManager object.

connStr

A String that contains the connection string for the SQL Server database.

queryStr

A String that contains the SQL query for retrieving the contents of the new list.

errLimit

A Long that is not used.

bAsync

A Boolean that specifies whether the creation operation is performed synchronously or asynchronously. A value of True indicates asynchronous processing.

pvarOpID

An optional Variant used to return an Operation ID.

Return Values

If this method completes successfully, it returns a Variant that contains the ID of the new list.

Error Values

This method sets the Number property of the global Err object to S_OK (&H00000000) to indicate success and to either standard or custom COM error values to indicate failure. For more information about standard COM errors, see Standard COM Errors.

The following table shows the custom COM errors that this method can return.

Constant	Value	Description
E_LM_CONNECTIONFAILED	&H8C400001	Failed to establish database connection.
E_LM_INVALID_FLAGS	&H8C400005	Invalid flags value.
E_LM_LIST_ALREADY_EXISTS	&H8C400007	The specified list has already been created.
E_LM_MAILABLE_LIST_NO_EMAILS	&H8C40000D	The list is designated as a Mailable list but does not contain an rcp_email column.
E_LM_OPER_CANCELED	&H8C40000B	The operation was canceled.
E_LM_SQLERROR	&H8C400003	Error while executing SQL/ADO operation.
E_LM_USER_LIST_NO_GUIDS	&H8C40000E	The list is designated as a User list but does not contain an rcp_guid column.
OLE_E_BLANK	&H80040007	The ListManager object has not been properly initialized.

Additional information may be available using the global Err object. In particular, the Description property may contain a text description of the error.

Remarks

It is recommended to use the MSDASQL (ODBC) OLEDB provider with this method.

The CreateFromSQL method uses the T-SQL OpenRowset command to run the query against the database. Therefore, any ODBC data source may be specified with the following restriction:

If the SQLOLEDB provider is used, any object names used in the query must be fully qualified. For example, on SQL Server 7.0 databases, use "SELECT * FROM database.dbo.tablename" instead of "SELECT * FROM tablename".

For a list of possible values for the lflags parameter, see the GetListFlags method.

For more information about memory ownership issues related to COM property and method parameters, see Managing COM Parameter Memory.

Example

' vListID is a Variant
vListID = oListManager.CreateFromSql("LegalTequillaBuyers", _
 "Tequilla buyers at least 21", 20, 0, "Provider=sqloledb;" & _
 "Data Source=TequillaServer;Initial Catalog=Margaritas;" & _
 "User=userid;Password=******", "SELECT * FROM dbname.dbo.Tequilla " & _
 "WHERE Age >= 21", 10, False)

See Also

ListManager Object

All rights reserved.