msdn: 由 Index 属性命名的索引必须针对基本表基本 Recordset 对象已在前面声明过。即索引必须已在程序中声明为 ADOX Index 对象,或在创建基本表时声明。如果无法设置索引,则会发生运行时错误。无法在 WillRecordsetChange 或 RecordsetChangeComplete 事件处理程序内设置 Index 属性。如果 Recordset 正在执行操作,也无法对它进行设置。如果 Recordset 是关闭的,则总能成功设置 Index 属性,但如果基本提供者不支持索引,则 Recordset 将无法成功打开,或者索引将无法使用。所以我已申明adox index对象 Dim idx As New ADOX.Index idx.Name = "english" idx.Columns.Append "English" tblTable.Indexes.Append idx如上所述,adorst未打开时,index可设置,但无法使用 若改了位置,在adorst打开以后设置index 属性,则设置adorst.index="idxitem"是就报错。所以我还是担心该提供者不支持索引。
所以我已申明adox index对象 Dim idx As New ADOX.Index idx.Name = "idxitem" idx.Columns.Append "English" tblTable.Indexes.Append idx
谁说 ado 没 index :: This example uses the Recordset object's Seek method and Index property in conjunction with a given Employee ID, to locate the employee's name in the Employees table of the Nwind.mdb database.'BeginSeekVB Public Sub SeekX() ' To integrate this code replace the data source ' in the connection string
'recordset and connection variables Dim rstEmployees As ADODB.Recordset Dim Cnxn As ADODB.Connection Dim strCnxn As String Dim strSQLEmployees As String
Dim strID As String Dim strPrompt As String strPrompt = "Enter an EmployeeID (e.g., 0 to 9)"
' Open connection Set Cnxn = New ADODB.Connection strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\Program Files\Microsoft Office\Office\Samples\northwind.mdb;" & _ "user id=admin;password=;" Cnxn.Open strCnxn
' open recordset server-side for indexing Set rstEmployees = New ADODB.Recordset rstEmployees.CursorLocation = adUseServer strSQLEmployees = "employees" rstEmployees.Open strSQLEmployees, strCnxn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
' Does this provider support Seek and Index? If rstEmployees.Supports(adIndex) And rstEmployees.Supports(adSeek) Then rstEmployees.Index = "PrimaryKey" ' Display all the employees rstEmployees.MoveFirst Do While rstEmployees.EOF = False Debug.Print rstEmployees!EmployeeId; ": "; rstEmployees!firstname; " "; _ rstEmployees!LastName rstEmployees.MoveNext Loop
' Prompt the user for an EmployeeID between 0 and 9 rstEmployees.MoveFirst Do strID = LCase(Trim(InputBox(strPrompt, "Seek Example"))) ' Quit if strID is a zero-length string (CANCEL, null, etc.) If Len(strID) = 0 Then Exit Do If Len(strID) = 1 And strID >= "0" And strID <= "9" Then rstEmployees.Seek Array(strID), adSeekFirstEQ If rstEmployees.EOF Then Debug.Print "Employee not found." Else Debug.Print strID; ": Employee='"; rstEmployees!firstname; " "; _ rstEmployees!LastName; "'" End If End If Loop End If
' clean up rstEmployees.Close Set rstEmployees = Nothing Cnxn.Close Set Cnxn = Nothing
由 Index 属性命名的索引必须针对基本表基本 Recordset 对象已在前面声明过。即索引必须已在程序中声明为 ADOX Index 对象,或在创建基本表时声明。如果无法设置索引,则会发生运行时错误。无法在 WillRecordsetChange 或 RecordsetChangeComplete 事件处理程序内设置 Index 属性。如果 Recordset 正在执行操作,也无法对它进行设置。如果 Recordset 是关闭的,则总能成功设置 Index 属性,但如果基本提供者不支持索引,则 Recordset 将无法成功打开,或者索引将无法使用。所以我已申明adox index对象
Dim idx As New ADOX.Index
idx.Name = "english"
idx.Columns.Append "English"
tblTable.Indexes.Append idx如上所述,adorst未打开时,index可设置,但无法使用
若改了位置,在adorst打开以后设置index 属性,则设置adorst.index="idxitem"是就报错。所以我还是担心该提供者不支持索引。
Dim idx As New ADOX.Index
idx.Name = "idxitem"
idx.Columns.Append "English"
tblTable.Indexes.Append idx
This example uses the Recordset object's Seek method and Index property in conjunction with a given Employee ID, to locate the employee's name in the Employees table of the Nwind.mdb database.'BeginSeekVB
Public Sub SeekX() ' To integrate this code replace the data source
' in the connection string
'recordset and connection variables
Dim rstEmployees As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strSQLEmployees As String
Dim strID As String
Dim strPrompt As String
strPrompt = "Enter an EmployeeID (e.g., 0 to 9)"
' Open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Program Files\Microsoft Office\Office\Samples\northwind.mdb;" & _
"user id=admin;password=;"
Cnxn.Open strCnxn
' open recordset server-side for indexing
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorLocation = adUseServer
strSQLEmployees = "employees"
rstEmployees.Open strSQLEmployees, strCnxn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
' Does this provider support Seek and Index?
If rstEmployees.Supports(adIndex) And rstEmployees.Supports(adSeek) Then
rstEmployees.Index = "PrimaryKey"
' Display all the employees
rstEmployees.MoveFirst
Do While rstEmployees.EOF = False
Debug.Print rstEmployees!EmployeeId; ": "; rstEmployees!firstname; " "; _
rstEmployees!LastName
rstEmployees.MoveNext
Loop
' Prompt the user for an EmployeeID between 0 and 9
rstEmployees.MoveFirst
Do
strID = LCase(Trim(InputBox(strPrompt, "Seek Example")))
' Quit if strID is a zero-length string (CANCEL, null, etc.)
If Len(strID) = 0 Then Exit Do
If Len(strID) = 1 And strID >= "0" And strID <= "9" Then
rstEmployees.Seek Array(strID), adSeekFirstEQ
If rstEmployees.EOF Then
Debug.Print "Employee not found."
Else
Debug.Print strID; ": Employee='"; rstEmployees!firstname; " "; _
rstEmployees!LastName; "'"
End If
End If
Loop
End If
' clean up
rstEmployees.Close
Set rstEmployees = Nothing
Cnxn.Close
Set Cnxn = Nothing
End Sub
'EndSeekVB
多谢呆呆提示,奉上一点点(80分)心意,敬请笑纳。:)另,我查过mircosoft support,没见过这段示例。您的例子说明是从那座宝山上挖来的?可告诉我吗?
QQ:51971759 :D