AppleVB.com

下面的窍门将教会你如何加速数据库的访问速度，当人们要读取一个数据库时往往会这么做：

combo1.additem records![Full Name]

经常遇到的问题是每次数据库移动到下一条记录的时候，它必须检查是否到达文件底部，这将使数据的访问速度大打折扣。当你需要在一个巨大的数据库中移动或寻找时，最好是这样做：

intRecCount=records.RecordCount

records.movefirstfor intCounter=1 to intRecCount combo1.additem records![Full Name]

试试看，你将得到33%的速度提升！
返回

缺省时的数据库字段为空字符（并不是指一个字符串值为“空格”，而是什么也没有），当你读取这些字段的时候把它们赋值给VB的String变量，你就会得到“变量类型不匹配”的错误。最好的解决方法应当是嵌入一串空格和字段连接起来，请看下面的代码：

Set DB = OpenDatabase("Biblio.mdb")

Set RS = DB.OpenRecordset("Authors")

sYear = "" & RS![Year Born]
返回

When retrieving NULL values from a recordset object, errors can occur. One
way to avoid this is to inspect the value of the field, and if it's NULL,
convert it to an empty string or zero. For example:

If isnull(rs("Field")) then tmp="" else tmp=rs("Field")
form.textfield=tmp

An even simpler way is to use the format function, which will convert a
NULL value to an empty string automatically, avoiding any error messages.
It will look like this:

form.textfield=format(rs("Field"))
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:6.0
OPER/SYS:WINDOWS
KEYWORDS:kbVBp kbVBp500 kbVBp600 kbCtrl

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Enterprise Edition for Windows, version 6.0
---------------------------------------------------------------------

SUMMARY
=======

This article explains how to highlight a specific row in the DataGrid
control (6.0) in an unbound mode.

The same procedure could be applied in Visual Basic 5.0 using the DBGrid
Control 5.0.

MORE INFORMATION
================

In order to highlight a specific row in the DataGrid you need to specify
the bookmark of the target record within the recordset of your data source.
You need to add this bookmark to the collection of bookmarks.

Each bookmark previously appended to the collection shows in the range of
selected rows on your DataGrid control. You need to explicitly remove these
rows from the collection before appending the bookmark for the record you
wish to highlight.

The following Visual Basic 6.0 sample code demonstrates the process of
searching for a record and then highlighting the hit within your grid:

1. Start a new Standard EXE project in Visual Basic. Form1 is created by
default.

2. Select Components on the Project menu. Select "Microsoft DataGrid
Control 6.0 (OLE DB)." Select Project References and add "Microsoft
ActiveX Data Objects 2.0 Library."

3. Add a CommandButton to your form; the default is Command1. Add a
DataGrid control to your form; the default is DataGrid1.

4. Place the following code in the General Declaration section of Form1:

Option Explicit

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim i As Integer
Dim FindLastName As String

Private Sub Command1_Click()

' Remove previously saved bookmark from collection
If (DataGrid1.SelBookmarks.Count <> 0) Then
DataGrid1.SelBookmarks.Remove 0
End If

' Prompt user for desired author's last name
FindLastName = InputBox("Please enter the author's last name you
want to search for", "Find")

rs.Find "au_lname = '" & FindLastName & "'", , , 1

' Append your bookmark to the collection of selected rows
DataGrid1.SelBookmarks.Add rs.Bookmark

End Sub

Private Sub Form_Load()

' Open your ADO connection where "Pubs" is an ODBC DSN that
' points to pubs database in SQL Server
cn.Open "Pubs"

' Create your command to query for all records in Authors table
With cmd
.ActiveConnection = cn
.CommandText = "select * from authors"
End With

' Open your recordset
With rs
' Set rs properties
.CursorType = adOpenKeyset
.LockType = adLockOptimistic

' Call open using active command
.Open cmd
End With

' Populate the DataGrid providing rs as the data source
Set DataGrid1.DataSource = rs

End Sub

5. Press the F5 key to run the application. Click the CommandButton and
enter an Author's last name. Note it moves the cursor to the Author
selected and highlights the entire row.
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0,6.0
OPER/SYS:WINDOWS
KEYWORDS:kbVBp500 kbVBp600 kbWizard kbAppSetup kbSDKWin32 kbRDO

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
---------------------------------------------------------------------

SUMMARY
=======

If you need to know all of the files needed to distribute for RDO to work
successfully on a machine that does not have Visual Basic installed, you
can determine this by using the Dependency file generated by the Setup
Wizard. ODBC must already be on the machine where RDO is being installed.

Another option is to use the Dependency Walker that comes with the Platform
SDK. This shows all of the files a particular DLL depends upon in a
graphical format. It can be obtained at:

http://www.microsoft.com/msdownload/platformsdk/sdktools.htm

MORE INFORMATION
================

The following steps will generate a dependency file showing the files
needed for RDO:

1. Create a Standard EXE project in Visual Basic. Form1 is created by
default.

2. Add a reference to "Microsoft Remote Data Object 2.0."

3. Save this project and run the Application Setup Wizard on it.

4. Select "Generate Dependency File" in the Setup Wizard.

5. Once the setup files are created. run the Setup.exe.

6. You will then have a file with a .DEP extension that can be opened with
Notepad. MSRDO20.DLL will be shown in this file and the files it uses
are also shown (RDOCURS.DLL, COMCAT.DLL, and ODBC32.DLL).
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:6.0
OPER/SYS:WINDOWS
KEYWORDS:kbVBp600 kbDATAENV

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Learning, Professional, and Enterprise Editions
for Windows, version 6.0
---------------------------------------------------------------------

SUMMARY
=======

When using the DataEnvironment in Visual Basic 6.0, it is not possible to
set the prompting behavior at design-time. This property is set to
adPromptComplete by default. You may want to change this property so that a
prompt is not displayed when establishing a connection to a database. To do
this you must set the "Prompt" property of the DataEnvironment at run-time.

MORE INFORMATION
================

The prompt property of the DataEnvironment can be set using the following
syntax:

DataEnvironment.Connection1.Properties("Prompt") = adPromptNever

There are four possible values to specify here:

adPromptAlways (1)
adPromptComplete (2)
adPromptCompleteRequired (3)
adPromptNever (4)

Below are the steps to create a Visual Basic project that illustrates how
to use this property.

1. Create a Standard EXE project in Visual Basic. Form1 is created by
default.

2. Add a CommandButton to the default form.

3. Add a DataEnvironment to the project.

4. From the DataEnvironment window, right-mouse click on Connection1 and
select Properties.

5. Select the Microsoft OLEDB Provider for ODBC Drivers. Click Next and
configure the second tab so that the connection will point to a valid
Database using either an existing DSN or a connection string.

6. Add the following Code to the default form:

Private Sub Command1_Click()
Debug.Print DataEnvironment1.Connection1.Properties("Prompt")
DataEnvironment1.Connection1.Open
End Sub

Private Sub Form_Load()
' Uncomment this line to suppress the prompt dialog:
' DataEnvironment1.Connection1.Properties("Prompt") = adPromptNever.
End Sub

7. Run the project and click the CommandButton. Note that a prompt is
displayed when you try to connect to the database. Uncomment the line of
code in the Form_Load() event and run the code again. Note that this
time no prompt is displayed.
返回

PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:1.5,2.0,5.0,6.0
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

- Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
- Microsoft Data Access Components versions 1.5, 2.0
---------------------------------------------------------------------

SUMMARY
=======

A "Query is too complex" error occurs when using RDO or ADO to update data
in an Access (Jet) database. Due to a limitation of the Jet database
engine, this error can occur if the recordset to be updated contains more
than 40 fields. This article describes the problem scenario, the Jet
limitation, and several workarounds.

MORE INFORMATION
================

When executing the Update() or BatchUpdate() methods of RDO's rdoResultset
object, or the Update() or UpdateBatch() methods of ADO's Recordset object,
the ODBC error S1000 "Query is too complex" (Visual Basic run-time error
40002) occurs.

This behavior occurs because the default behavior of ADO and RDO is to use
each field in the recordset to determine the record to be updated on the
server. That is, when the update is attempted, a SQL Update query is sent
to the server. Part of this query is a WHERE clause that is used to
identify the record to be updated. An AND clause appears within the WHERE
clause for each field to be used in that identification. Updating a
recordset with more than 40 fields involves a WHERE clause with more than
40 ANDs.

The Jet engine allows only 40 AND clauses within a SQL WHERE or HAVING
clause. The Access ODBC driver returns a "Query is too complex" error when
it encounters such a SQL statement. See the REFERENCES section of this
article for more information.

There are several ways to avoid this behavior:

1. For new applications, use DAO when working with Access databases. DAO
was designed for this purpose, and will not issue a SQL query to perform
the update. Note that RDO and especially ADO were designed to operate
efficiently with many other types of databases and are recommended for
applications that will interact with various servers or that will be
upsized in the future to use Microsoft SQL Server, for example.

2. When opening the recordset to be updated (such as the RDO OpenResultset
or ADO OpenRecordset methods), select specific, and 40 or fewer fields.

3. Use a server-side cursor.

- To do this in ADO, set the CursorLocation location property of the
Recordset object to adUseServer before opening the Recordset:

...
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
rs.Open ...
...

- In RDO, set the CursorDriver property of the rdoEnvironment object
before calling the OpenConnection() method:

...
Dim eng As RDO.rdoEngine
Dim cn As RDO.rdoConnection
Dim env As RDO.rdoEnvironment
Dim rs As RDO.rdoResultset

Set eng = New rdoEngine
Set env = eng.rdoCreateEnvironment(...)
env.CursorDriver = rdUseServer
Set cn = env.OpenConnection(...)
Set rs = cn.OpenResultset(...)
...

4. Ensure that a unique key is used to identify the record to be updated,
rather than the default behavior of using every field. A unique key
(though not necessarily a primary key) must be defined in the underlying
recordset for this technique to work.

- To do this in ADO (this will not work in versions prior to ADO
2.0), set the Recordset object's "Update Criteria" property to
adCriteriaKey. This will work both for the Update() and UpdateBatch()
methods:

...
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Properties("Update Criteria").Value = adCriteriaKey
rs.Open ...
...

- For RDO, use the ClientBatch cursor library and ensure that the
Resultset object's UpdateCriteria property is not set to
rdCriteriaAllCols. By default, this property is set to rdCriteriaKey,
which will cause the use of the primary key to determine the row(s) to
be updated on the server:

...
Dim eng As RDO.rdoEngine
Dim cn As RDO.rdoConnection
Dim env As RDO.rdoEnvironment
Dim rs As RDO.rdoResultset

Set eng = New rdoEngine
Set env = eng.rdoCreateEnvironment(...)
env.CursorDriver = rdUseClientBatch
Set cn = env.OpenConnection(...)
Set rs = cn.OpenResultset(...)
rs.Edit
...
rs.UpdateCriteria = rdCriteriaKey
rs.Update
...
返回