Open method (ADO connection)
Open the connection to the data source.
grammar
Connection.open Connectionstring, UserId, Password, Options
parameter
Connectionstring optional, string, contain connection information. See the Connectionstring property to get a valid setting details.
UserId optional, string, the user name is used when establishing a connection.
Password options, strings, passwords you use when building a connection.
Options optional, connectoptionenum value. Deciding that the method is returned before the connection is established (asynchronous) or the connection is established (synchronized). Can be a constant as follows:
Constant Description AdconnectunSpecified (default) is synchronously open. ADASYNCCONNECT opens asynchronously. The ConnectComplete event can be used to determine when the connection is available.
Description
The OPEN method using the Connection object can be created to the physical connection to the data source. The connection is active after the method successfully completed, and it can be commanded and the result is processed.
Use the optional Connectionstring parameter to specify the connection string, which contains a series of Argument = Value statements separated by a semicolon. The Connectionstring property automatically inherits the value for the Connectionstring parameter, so the ConnectionString property of the Connection object can be set before open, or use the connectionstring parameter to set or override the current connection parameter when the Open method is called.
If users and password information are transmitted in the Connectionstring parameter and optional userid and password parameters, the userID and password parameters override the values specified in Connectionstring.
After the operation of the open Connection is over, you can use the Close method to release all associated system resources. Closing the object is not to remove it from memory; you can change its property settings and open it again using the Open method again. To remove objects entirely from memory, set object variables to Nothing.
Remote Data Services When using an Open method on the client's Connection object, the Open method is not established to the server before the Connection object is opened on the Connection object.
Open method (ADO Recordset)
Open the cursor.
grammar
Recordset.open Source, ActiveConnection, Cursortype, LockType, Options
parameter
Source optional, variable body, calculating the variable name of the Command object, SQL statement, table name, stored procedure call, or persistent RECORDSET file name.
ActiveConnection is optional. Variable size, calculate valid Connection object variable name; or string containing the connectionString parameter.
CURSORTYPE Optional, CURSORTYPEENUM Value, determine the type of cursor that the provider should use when opening Recordset. These settings can be obtained for one of the following constants (see the CURSORTYPE attribute).
Constant Description AdopenForwardOnly (default) Opens only the forward type cursor. AdoPenKeySet opens the key set type cursor. AdoPENDYNAMIC opens the dynamic type cursor. AdoPenStatic opens a static type cursor.
LockType is optional. Determining the LockTyPEENUM value of the lock (concurrent) type that the provider should use when the Recordset is used, and can be one of the following constants (see the LockType property to get details). Constant Description AdlockReadOnly Read-only - You cannot change the data. AdlockPessimistic conservative lock (one by one) - The provider completes the work required to ensure successful editing records, usually by locking the record of the data source immediately when editing. AdlockOptimistic open lock (one by one) - The provider uses an open lock that is only locked only when the Update method is called. AdlockBatchOptimistic open batch update - for batch update mode (relative to immediately update mode).
Options Optional, long integer value, is used to indicate how the provider calculates the Source parameter (if it is not a Command object), or recover the Recordset from the file that previously saved Recordset. A detailed description of the top five constants in this list can be obtained for one of the following constants (see the CommandType property).
Constant Description ADCMDText Indicates that the provider should calculate the source as the text of the command. ADCMDTABLE indicates that ADO generates a SQL query to return all rows from a table named in Source. AdcmdtableDirect Indicator The provider changes all rows from the table named in SOURCE. AdcmdStoredProc indicates that the provider should treat Source as a stored procedure. AdcmDunknown Indicates that the command type in the Source parameter is unknown. AdcmdFile indicates that the reserved (saved) Recordset should be restored from the file named in Source. Adasyncexecute indicates that Source should be executed asynchronously. ADASYNCFETCH indicates that all remaining rows should be extracted asynchronously after extracting the initial number specified in the initial fetch size property. If the required line is not extracted, the main thread will be blocked until the row is re-available. ADASYNCFETCHNONBLOCKING indicates that the main thread never clogged during extraction. If the requested line has not been extracted, the current line is automatically moved to the end of the file.
Description
Open methods using the Recordset object open the cursor recorded in the RECORDSET that represents the basic table, query results, or the previously saved Recordset.
Use optional Source parameters to specify a data source using one of the following: Command object variables, SQL statements, stored procedures, table names, or complete file path names.
If Source is a file path name, it can be a full path ("C: /DIR/file.rst"), relative path ("../file.rst") or url ("http://files/file.rst) ").
The ActiveConnection parameter corresponds to the ActiveConnection property and specifies which connection opens to the Recordset object. If the connection definition of the parameter is transmitted, the ADO opens a new connection using the specified parameters. You can change the value of this property after opening the Recordset to send the update to another provider. Or you can set this property to Nothing (in Microsoft Visual Basic) to disconnect the Recordset with all the providers.
The relationship between the parameters, Cursortype and LockType directly corresponding to the Recordset object property is as follows:
Before the Recordset object is opened, the property is read / write. The property setting will be used unless the corresponding parameters are transmitted when the Open method is executed. If the parameter is transmitted, it will overwrite the corresponding property settings and update the property settings with the parameter value. These properties will become read-only after opening the Recordset object. Note that the ActiveConnection property is read only for its Source attribute to a RECORDSET object that is a valid Command object, even if the Recordset object is not open.
If the Command object is transmitted in the Source parameter and the ActiveConnection parameters are passed, the error will be generated. The ActiveConnection property of the Command object must have been set to a valid Connection object or a connection string.
If you are transferred in the Source parameter, you can use the Options parameter to optimize the calculation of the Source parameter. If you do not define Options, performance will decrease because the ADO must call the provider to determine if the parameter is a SQL statement, a stored procedure, or a table name. If the Source type used is already determined, the Options parameter can be set to indicate that the ADO will jump directly to the relevant code. If the Options parameter does not match the Source type, an error will occur.
If there is no connection associated with RecordSet, the default value of the Options parameter will be AdcmdFile. This is a typical situation of lasting Recordset objects.
If the data source does not return a record, the provider sets the BOF and EOF attributes to TRUE and does not define the current recording location. If the cursor type is allowed, new data can still be added to the empty recordset object.
When the operation is completed on the open RecordSet object, you can use the Close method to release any relevant system resources. Closing the object does not delete it from memory, you can change its property settings and open it again using the Open method. To completely delete objects from memory, set object variables to Nothing.
Calling an OPEN that does not have an operand before setting an ActiveConnection property, you can create an instance of the RECORDSET by adding the field to the RecordSet Fields collection.
If you have set the CursorLocation property to AduseClient, one of the two ways can be used. The recommended method is to set Options to ADASYNCFETCH. Alternatively, you can use the "asynchronous line set process" dynamic properties in the Properties collection, but if the Options parameter is not set to AdaSyncFetch, you may lose the relevant retrieved event.
Note that the background extraction in the MSREMote provider can only be supported by the OPTIONS parameter of the OPEN method.
OpenSChema method
Get database mode information from the provider.
grammar
Set Recordset = Connection.Openschema (QueryType, criteria, Schemaid)
return value
Returns the RECORDSET object containing the mode information. Recordset will open read, static cursors.
parameter
The pattern query type to be running QueryType can be any constant.
Criteria is optional. The query limit condition array of each querytype option is listed below:
QueryType value Criteria value AdSchemaAssertsCONSTRAINT_CATALOGCONSTRAINT_SCHEMACONSTRAINT_NAMEAdSchemaCatalogsCATALOG_NAMEAdSchemaCharacterSetsCHARACTER_SET_CATALOGCHARACTER_SET_SCHEMACHARACTER_SET_NAMEAdSchemaCheckConstraintsCONSTRAINT_CATALOGCONSTRAINT_SCHEMACONSTRAINT_NAMEAdSchemaCollationsCOLLATION_CATALOGCOLLATION_SCHEMACOLLATION_NAMEAdSchemaColumnDomainUsageDOMAIN_CATALOGDOMAIN_SCHEMADOMAIN_NAMECOLUMN_NAMEAdSchemaColumnPrivilegesTABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEGRANTORGRANTEEadSchemaColumnsTABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEadSchemaConstraintColumnUsageTABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEadSchemaConstraintTableUsageTABLE_CATALOGTABLE_SCHEMATABLE_NAMEadSchemaForeignKeysPK_TABLE_CATALOGPK_TABLE_SCHEMAPK_TABLE_NAMEFK_TABLE_CATALOGFK_TABLE_SCHEMAFK_TABLE_NAMEadSchemaIndexesTABLE_CATALOGTABLE_SCHEMAINDEX_NAMETYPETABLE_NAMEadSchemaKeyColumnUsageCONSTRAINT_CATALOGCONSTRAINT_SCHEMACONSTRAINT_NAMETABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEadSchemaPrimaryKeysPK See the description _TABLE_CATALOGPK_TABLE_SCHEMAPK_TABLE_NAMEadSchemaProcedureColumnsPROCEDURE_CATALOGPROCEDURE_SCHEMAPROCEDURE_NAMECOLUMN_NAMEadSchemaProcedureParametersPROCEDURE_CATALOGPROCEDURE_SCHEMAPROCEDURE_NAMEPARAMTER_NAMEadSchemaProceduresPROCEDURE_CATALOGPROCEDURE_SCHEMAPROCEDURE_NAMEPROCEDURE_TYPEadSchemaProviderSpecific adSchemaProviderTypesDATA_TYPEBEST_MATCHadSchemaReferentialConstraintsCONSTRAINT_CATALOGCONSTRAINT_SCHEMACONSTRAINT_NAMEadSchemaSchemataCATALOG_NAMESCHEMA_NAMESCHEMA_OWNERadSchemaSQLLanguages
adSchemaStatisticsTABLE_CATALOGTABLE_SCHEMATABLE_NAMEadSchemaTableConstraintsCONSTRAINT_CATALOGCONSTRAINT_SCHEMACONSTRAINT_NAMETABLE_CATALOGTABLE_SCHEMATABLE_NAMECONSTRAINT_TYPEadSchemaTablePrivilegesTABLE_CATALOGTABLE_SCHEMATABLE_NAMEGRANTORGRANTEEadSchemaTablesTABLE_CATALOGTABLE_SCHEMATABLE_NAMETABLE_TYPEadSchemaTranslationsTRANSLATION_CATALOGTRANSLATION_SCHEMATRANSLATION_NAMEadSchemaUsagePrivilegesOBJECT_CATALOGOBJECT_SCHEMAOBJECT_NAMEOBJECT_TYPEGRANTORGRANTEEadSchemaViewColumnUsageVIEW_CATALOGVIEW_SCHEMAVIEW_NAMEadSchemaViewTableUsageVIEW_CATALOGVIEW_SCHEMAVIEW_NAMEadSchemaViewsTABLE_CATALOGTABLE_SCHEMATABLE_NAMESchemaID OLE DB specification does not define the GUID for the provider mode queries. If QueryType is set to Adschemaproviderspecific, this parameter is required, otherwise it is not used.
Description
The OpenSchema method returns information related to the data source, such as information about the table on the server and the columns in the table.
The criteria parameter is an array of values that can be used to restrict the results query results. Each mode query has different parameter sets it supports. The actual mode is defined by the OLE DB specification under the IDBSChemarowSet interface. The parameter set supported in the ADO is listed above.
If the provider defines the non-standard mode query not listed above, the constant ADSCHEMAPROVIDERSPECIFIC will use the querytype parameter. The GUID that requires the schemaid parameter transfer mode query is required when using this constant for execution. If QueryType is set to Adschemaproviderspecific but does not provide schemaid, it will cause errors.
The provider does not need to support all OLE DB standard mode queries, only ADSCHEMATABLES, AdsChemacolumns, and AdschemaprovideTypes are the OLE DB specification. But for these mode queries, the provider does not need to support the criteria condition constraints listed above.
Remote Data Services OpenSchema is invalid on the client Connection object.
Note In Visual Basic, 4-byte unsigned integer (DBTYPE UI4) in the Recordset returned from the OpenSchema method of the Connection object cannot be compared with other variables.
Query method (RDS)
Return the Recordset with a valid SQL query string.
grammar
Set recordset = datafactory.query connection, query
parameter
Recordset object variable represents the Recordset object.
The DataFactory object variable represents the RDSServer.DataFactory object.
Connection string contains server connection information. It is similar to the Connect property.
Query string contains SQL queries.
Description
The query should use the specific SQL language of the database server. Returns the result status if an error is executed. The Query method does not perform any syntax check for the Query string. REFRESH method
Update the object in the collection to reflect the available objects from the provider and the provider's object.
grammar
Collection.refresh
Description
The Refresh method completes different tasks based on the different collections called from the call.
parameter
The Refresh method on the Parameters collection using the Command object can be used for the stored procedure or parameterized query provider terminal parameter information specified in the COMMAND object. For providers who do not support stored procedures or parameterized queries, the collection will be empty.
Setting the ActiveConnection property of the Command object to a valid Connection object before calling the Refresh method, set the CommandText property to a valid command, and set the CommandType property to AdcmdStoredProc.
If you access the parameters collection before calling the Refresh method, the ADO will automatically call the method and fill the collection.
Note If you use the REFRESH method to get parameter information from the provider and it returns one or more becoming Data Type Parameter objects, the ADO may allocate memory based on the maximum value of its size, which will cause errors during execution. The size property of these parameters should be explicitly set before calling the Execute method to prevent errors.
Fields
There is no visible effect on the refresh method on the Fields collection. To retrieve changes from the basic database structure, you must use the REQUERY method; if the Recordset object does not support bookmarks, use the MoveFirst method.
PROPERTIES
Using the Refresh method on some object's Properties collection Use the provider's dynamic attribute to fill in the set, which only provides functional information to the provider other than the built-in attribute supported by the ADO.
Refresh Method (RDS)
Require query and update the query results for the ODBC data source specified in the Connect property.
grammar
DataControl.refresh
parameter
DataControl object variables represent RDS.DataControl objects.
Description
You must set up CONNECT, Server, and SQL properties before using the Refresh method. All data binding controls in the table associated with the RDS.DataControl object reflect the new recordset, all previous RecordSet objects will be released and give up all unsaved changes. The Refresh method allows the first record to automatically become the current record.
It is best to call the Refresh method periodically when processing data. If you retrieve data and place it on the client, the data is likely to have expired, and all changes made at this time will be invalid because others may change the record and submit it first.
Requuery method
Update the data in the Recordset object by re-executing the query based on the object.
grammar
RecordSet.Requery Options
parameter
Options is optional. Indicates the bit mask affecting the operation option. If the parameter is set to AdaSyncexecute, the action will perform asynchronously and generate the RecordSetChangeComplete event at the end of it.
Description
The Requory method can be used to refresh the entire contents of the Recordset object from the data source by researting the original command and retrieve data again. Calling this method is equal to successively calling the Close and Open methods. If you are editing the current record or add a new record, an error will occur.
During the Recordset object, define the properties of the cursor nature (CURSORTYPE, LOCKTYPE, MAXRECORDS, etc.) to read only, so the Requuery method can only refresh the current cursor. To change a bike property and view the result, you must use a Close method to make the property becomes read / write again. You can then change the property setting and call the OPEN method to reopen the cursor. Reset method (RDS)
Sort or filter the client RECORDSET based on the specified sort and filtering properties.
grammar
DataControl.reset (Value)
parameter
DataControl object variables represent RDS.DataControl objects.
Value Optional, Boolean value, if you want to perform a filter operation on the current "Filtered" row, it is true true (default); false means performing filtering operations on the original row, and deletes all previous Screening Operation Options.
Description
SortColumn, SortDirection, FilterValue, Filtercriterion, and FilterColumn properties provide sorting and filtering functions on the client cache. The sorting function will be sorted according to the value of a column. When all RecordSets are in the cache, the filtering function displays the record subset according to the lookup criteria. The reset method will perform the lookup standard and replace the current Recordset with the updatable Recordset.
If there is still no changes to the original data, then the reset method will be invalid. First use the SubmitChanges method to save all changes in the read / write Recordset, then use the reset method to sort or filter the record.
If you want to perform multiple filtering to perform multiple filters, you can use the optional Boolean parameters in the reset method. The following example will explain this process:
Adc.sql = "SELECT AU_LNAME FROM Authors"
Adc.refresh 'gets a new set of rows.
ADC.FILTERCOLUMN = "au_lname"
ADC.FILTERCRITERION = "<"
Adc.FilterValue = "'m'"
The adc.reset 'record set now contains records that are less than "M" at the first name.
ADC.FILTERCRITERION = ">"
Adc.FilterValue = "'f'"
'Don't transfer the True value because it is the current
The default filter criteria for the "Screened" record set.
Adc.reset (TRUE) 'record set now contains all surnames
'Small than "M" and is greater than "f" record.
ADC.FILTERCRITERION = ">"
Adc.filterValue = "'t'"
'Screening the original record set and waives the previous screening options.
Adc.reset (false) 'record set now contains all the names of the last name
'Is greater than the record of "T".
