ado| Tips | The essence I use ActiveX Data Object (ADO) to start with the 1.5 version, which is already a very old version. Many things have changed in the current version. I've learned a lot of new things from every version upgrade. You can't find all these things from books, or at least you can't find them from one place.
I have carefully selected and summed up these ADO development points and techniques here. Some of these problems may be the problems you've been obsessing about, some are technologies you've never known, and some just show the knowledge of ADO development.
One, shared connection objects
When you pass a connection string to a command, Recordset, or Record object, each time you implicitly command ADO to create a Connection object:
Dim REC1 as ADODB. Record
Dim rec2 as ADODB. Record
Dim REC3 as ADODB. Record
Set rec1 = New ADODB. Record
Rec1. Open "Localstart.asp", "url=http://localhost/"
Set rec2 = New ADODB. Record
Rec2. Open "Global.asa", "url=http://localhost/"
Set rec3 = New ADODB. Record
REC3. Open "Iisstart.asp", "url=http://localhost/"
' Perform some action
Rec1. Close
Rec2. Close
REC3. Close
Set REC1 = Nothing
Set rec2 = Nothing
Set REC3 = Nothing
To conserve resources, you should first create a connection object and pass it on to all objects that require active connections. In other words, the above code should be changed to the following form:
Dim con as ADODB. Connection
Dim REC1 as ADODB. Record
Dim rec2 as ADODB. Record
Dim REC3 as ADODB. Record
Set con = New ADODB. Connection
Con. Open "url=http://localhost/"
Set rec1 = New ADODB. Record
Rec1. Open "Localstart.asp", con
Set rec2 = New ADODB. Record
Rec2. Open "Global.asa", con
Set rec3 = New ADODB. Record
REC3. Open "iisstart.asp", con
' Perform some action '
Rec1. Close
Rec2. Close
REC3. Close
Con. Close
Set REC1 = Nothing
Set rec2 = Nothing
Set REC3 = Nothing
Set con = Nothing
Second, read ConnectionString properties
From any open Connection object, including the connection object returned by the recordset, Command, or the ActiveConnection property of the Record object, You can always read the ConnectionString attribute.
Dim com as Adodb.command
Dim rst as ADODB. Recordset
Set com = New Adodb.command
Com. ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0" & "Data Source=nwind.mdb;"
Com.commandtext = "SELECT * FROM Customers"
Set rst = com. Execute
MsgBox com. Activeconnection.connectionstring
Rst. Close
Set rst = Nothing
Set com = Nothing
When the above code runs, you will see the following output from the message box:
provider=microsoft.jet.oledb.4.0;
Password= "";
User id=admin;
Data Source=nwind.mdb;
Mode=share Deny None;
Extended properties= "";
Jet oledb:system database= "";
Jet oledb:registry path= "";
Jet oledb:database password= "";
Jet Oledb:engine type=4;
Jet oledb:database locking mode=0;
Jet Oledb:global Partial Bulk ops=2;
Jet Oledb:global Bulk Transactions=1;
Jet oledb:new Database password= "";
Jet oledb:create System Database=false;
Jet Oledb:encrypt Database=false;
Jet Oledb:don ' t Copy Locale on Compact=false;
Jet oledb:compact without Replica repair=false;
Jet Oledb:sfp=false
Now you can parse the string to find out specific information about the connection, such as whether it will be encrypted when the database is compressed (Jet Oledb:encrypt database attribute).
Iii. Use of dynamic properties
The Properties collection for the Connection object can be used to set vendor-specific options, such as the prompt dynamic properties of SQL Server's OLE DB driver.
Dim con as ADODB. Connection
Set con = New ADODB. Connection
Con. Provider = "SQLOLEDB"
Con. Properties ("Prompt") = adPromptAlways
Con. Open
' Prompt user to select database '
Con. Close
Set con = Nothing
When the above code runs, the user will see a dialog box that allows the user to choose which database to log on to.
Wise selection of Cursor position
When choosing the location of a cursor, you must consider which services are important for the current connection.
If the data provider's service is exactly what you need, you should use a server-side cursor. These services are services provided by data source drivers that are typically very scalable. In addition, by keeping the server-side cursors, you do not have to always send all the data to the client as you would with a client cursor.
On the other hand, local cursor services, such as Microsoft Data Shape services for OLE DB, can provide some client-side cursor-specific services. For these services to work, the data must be sent to the local machine as required by the data-shape service.
You can set the cursor position with the Connection.cursorlocation property, but the choice should be sensible and discreet.
Choose a cursor type wisely
Selecting the type of cursor is as important as selecting the location of the cursor. There are four kinds of cursors, each of which has its own advantages and disadvantages.
A static cursor (a static cursor) provides a snapshot of the data at a given moment. In this type of cursor, data changes (including additional user data or deletion operations) are always invisible. A static cursor is ideal for making reports because it requires a consistent, unchanged view of the data, but a static cursor is not necessarily the fastest. Because data changes are not shown, for each connection that uses a static cursor, the service provider must create and maintain a copy of the data for it at a given moment, respectively.
The Forward only cursor (a forward-only cursor) is essentially the same as a static cursor, except that you can only move forward to access the data, but not backwards. This limitation can improve performance compared to a static cursor, but it still requires the data source to maintain a temporary copy of the data so that other users ' changes to the data do not affect your data.
Dynamic cursors (animated cursors) allow you to see the modification and deletion of data by other users, and you can move freely within the recordset. Unlike static and forward only cursors, dynamic cursors do not require a data source to maintain a static image of the data, so dynamic cursors are faster than the first two cursors.
The last type of cursor is a keyset cursor (keyset cursor). Keyset cursors are very similar to dynamic cursors, except that you cannot see new records from other users. In the keyset cursor, records deleted by other users will also be inaccessible. As with dynamic cursors, you can see other user modifications in the keyset cursor. The keyset cursor may be faster than the dynamic cursor because the keyset cursor does not need to check regularly for new records to join and records to be deleted (since the new records are not visible and the deleted records become inaccessible).
Consider every reason and then choose the type of cursor that suits you.
VI. Manual Construction Parameters
When performance factors are important, define the parameters manually:
Dim con as ADODB. Connection
Dim com as Adodb.command
Dim par as ADODB. Parameter
Dim RS
