ACC2000: Error Setting Index Property of ADO Recordset Based on a Microsoft Jet Database
ID: Q249683
|
The information in this article applies to:
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies only to a Microsoft Access database (.mdb).
SYMPTOMS
When you try to set the Index property of an ActiveX Data Objects (ADO) recordset that is based on a table in a Microsoft Jet database, you may receive the following error message:
Run-time error '3251':
Object or provider is not capable of performing requested operation.
CAUSE
You are trying to set the Index property of a linked table within the database.
RESOLUTION
Open a separate ADO connection to the back-end database, and open the table directly instead of using the linked table. To see an example of how to do this, follow these steps:
Microsoft provides programming examples for illustration only, without warranty
either expressed or implied, including, but not limited to, the implied warranties of
merchantability and/or fitness for a particular purpose. This article assumes that you
are familiar with the programming language being demonstrated and the tools used to
create and debug procedures. Microsoft support professionals can help explain the functionality
of a particular procedure, but they will not modify these examples to provide added
functionality or construct procedures to meet your specific needs. If you have limited
programming experience, you may want to contact a Microsoft Certified Solution Provider
or the Microsoft fee-based consulting line at (800) 936-5200. For more information about
Microsoft Certified Solution Providers, please see the following page on the World Wide Web:
http://www.microsoft.com/mcsp/
For more information about the support options available from Microsoft, please see the
following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/overview/overview.asp
NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code
to run properly, you need to reference the Microsoft ActiveX Data Objects 2.1 Library.
- Follow steps 1 through 7 in the "Steps to Reproduce Behavior section" later in this article.
- Type the following procedure within the new module:
Sub LinkTableSeek()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "C:\Program Files\Microsoft " _
& "Office\Office\Samples\Northwind.mdb"
.Open
End With
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Source = "Customers"
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.Open Options:=adCmdTableDirect
.Index = "PrimaryKey"
.Seek "WOLZA"
If (Not .EOF And Not .BOF) Then
MsgBox rs.Fields("CompanyName").Value
Else
MsgBox "Record not found"
End If
.Close
End With
Set rs = Nothing
Set cn = Nothing
End Sub
- Follow steps 9 and 10 in the "Steps to Reproduce Behavior" section later in this article.
Note that you receive a message with the text "Wolski Zajazd," which is the company name of the CustomerID that the code was seeking.
STATUS
This behavior is by design.
MORE INFORMATION
NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code
to run properly, you need to reference the Microsoft ActiveX Data Objects 2.1 Library.
Steps to Reproduce Behavior
- Start Microsoft Access 2000.
- Create a new, blank database.
- On the File menu, point to Get External Data, and then click Link Tables.
- In the Link dialog box, locate the sample database Northwind.mdb, located in the C:\Program Files\Microsoft Office\Office\Samples folder by default.
- Click Northwind.mdb in the Link dialog box, and then click Link.
- In the Link Tables dialog box, click the Customers table, and then click OK.
- Create a new module and type the following line in the Declarations section if it is not already there:
Option Explicit
- Type the following procedure:
Sub LinkTableSeek()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Source = "Customers"
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.Open Options:=adCmdTableDirect
.Index = "PrimaryKey"
.Seek "WOLZA"
If (Not .EOF And Not .BOF) Then
MsgBox rs.Fields("CompanyName").Value
Else
MsgBox "Record not found"
End If
.Close
End With
Set rs = Nothing
Set cn = Nothing
End Sub
- On the Debug menu, click Compile project name to verify that the module is compiled correctly.
- To test the procedure, type the following line in the Immediate window, and then press ENTER:
LinkTableSeek
Note that you receive the error mentioned in the "Symptoms" section of this article.
REFERENCES
Additional query words:
prb attached
Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb