Solved

Vb6,Access2000,Ado (Using Access2000 Index's) Not Working

Posted on 2001-08-13
6
303 Views
Last Modified: 2010-08-05
I am trying to use Ado and Access2000 and Access2000 Index's
I get the error below  ...see ???????????????????



Private Sub Command1_Click()

Dim rs As ADODB.Recordset
Dim cnn As New ADODB.Connection
   cnn.Mode = adModeShareDenyNone
   cnn.CursorLocation = adUseClient
   cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
   cnn.Open Trim(App.Path) & "\accesswol.MDB"
   ff = cnn.Properties("Jet OLEDB:Transaction Commit Mode")
   cnn.Properties("Jet OLEDB:Page Timeout") = 4000
   cnn.Properties("Jet OLEDB:Transaction Commit Mode") = 1
   cnn.Properties("Jet OLEDB:Lock Delay") = 120 + Int(Rnd * 80)

Set rs = New ADODB.Recordset

rs.LockType = adLockOptimistic
rs.Open "SELECT * FROM Wol", cnn, adOpenKeyset, adLockOptimistic

''''''If rs.Supports(adIndex) And rs.Supports(adSeek) Then
rs.Index = ("code")


????????????????
Run time-error '3251'
The operation requested by the application is not supported by the Provider

I get the same error id using DAO as well!



Have tried converting Mdb  back to Access97!!

Would appreciate help as to what i am doing wrong



0
Comment
Question by:turnerrob
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
From MSDN:

Q249683 - ACC2000: Error Setting Index Property of ADO Recordset Based on a Microsoft Jet Database
http://support.microsoft.com/support/kb/articles/Q249/6/83.ASP

-------------------------------------------------------
ACC2000: Error Setting Index Property of ADO Recordset Based on a Microsoft Jet Database

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

Microsoft Access 2000

-----------------------------------------------------------Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

For a Microsoft Access 2002 version of this article, see Q290060.


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.
In Microsoft Windows 2000, you may receive the following error message:
Run-time error '3251':

Current provider does not support the necessary interface for Index functionality.

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 Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please see the following page on the World Wide Web:

http://www.microsoft.com/partner/referral/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run properly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor, and ensure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.


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 must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor, and ensure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.



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
For additional information about using the Seek method in an ADO recordset based on a table in a Microsoft Jet database, click the article number below to view the article in the Microsoft Knowledge Base:

Q243465 ACC2000: How to Use the Seek Method with ActiveX Data Objects (ADO) Against a Jet Recordset

Additional query words: prb attached run time error 3251

Keywords : kbdta
Issue type : kbprb
Technology : kbAccessSearch kbAccess2000 kbAccess2000Search


0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
Comment Utility
To be honest I am not sure why you are setting the connection mode (cnn.Mode) and some of the other connection properties, but in any case make the following changes and you will find it now is supported:

Dim rs As ADODB.Recordset
Dim cnn As New ADODB.Connection
  cnn.Mode = adModeShareDenyNone
  'cnn.CursorLocation = adUseClient   'Comment this
  cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
  cnn.Open Trim(App.Path) & "\accesswol.MDB"
  ff = cnn.Properties("Jet OLEDB:Transaction Commit Mode")
  cnn.Properties("Jet OLEDB:Page Timeout") = 4000
  cnn.Properties("Jet OLEDB:Transaction Commit Mode") = 1
  cnn.Properties("Jet OLEDB:Lock Delay") = 120 + Int(Rnd * 80)

Set rs = New ADODB.Recordset

'rs.LockType = adLockOptimistic   ' repeated
rs.Open "wol", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
Debug.Print rs.Supports(adIndex)

''''''If rs.Supports(adIndex) And rs.Supports(adSeek) Then
rs.Index = ("code")
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I think you may find this easier to read:

Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
 
Set cnn = New ADODB.Connection
With cnn
   .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Trim(App.Path) & "\accesswol.MDB"
   .Open
End With

Set rs = New ADODB.Recordset
With rs
   .Source = "wol"
   .ActiveConnection = cnn
   .CursorType = adOpenKeyset
   .LockType = adLockOptimistic
   .Open Options:=adCmdTableDirect
   Debug.Print .Supports(adIndex), .Supports(adSeek)
   .Close
End With
Set rs = Nothing

cnn.Close
Set cnn = Nothing
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
wsh2

Just got round to reading your solution.  The problem as I see it, is not that the provider does not support setting the index, it does.  Nor that the database does not support it, providing it is an Access 2000 database it is supported.  The problem is that turnerrob is trying to set an index to a Select statement which is clearly never going to work.  The other problem is that he/she is setting CursorLocation property for the Connection object to adUseClient and this also will not allow setting any indexes even if a table is opened in adCmdTableDirect.

Having said that, I do not recall ever having used this (Index) property.  Probably because I very rarely have the need to do record searches. So I could well be wrong, even though it appeared to work as advertised on my workstation.


Anthony
0
 
LVL 14

Expert Comment

by:wsh2
Comment Utility
Anthony..

I totally agree with your code solution.. (ie. the old "K.I.S.S." - Keep It Simple Stupid principle.. <smile>). Looking at some of the ADO code settings used by the Questioner <and still scratching my head>.. either he/she is some kind of ADO Gawd or he/she is no more than a mere mortal like us trying to make it through to the next paycheck by randomly hitting keys until finally a work of Shakespearian proportion finally appears. Long story short.. the code Anthony provided is VERY servicable and a terrific starting point to build.. <smile>.

As I understand it.. the Index / Seek methodology so well used in the good old, old, old Access days is no longer in vogue.. and if I am correct.. met its demise in the later stages of DAO. I am fairly certain that the cause of its demise.. was the overhead required to build the Index before any processing could be done.. proved to be too onerous a task.. virtually negating ANY retreival savings to be had once the Index was finally built.

Which now makes me wonder.. what the questioner is attempting to do with the Index / Seek properties.. and that perhaps there is a much better and simpler way to achieve this were we to know the Questioners intentions.. <smile>.
0
 

Author Comment

by:turnerrob
Comment Utility
Thanks for the Comment re my Ado Problem.
The alterations did solve the problem.

I copied this code from Microsoft MSN (Somewhere).
It did seem a little complex, even to my inexperienced VB mind!!!.

I had a feeling it might have been a VB Reference etc, problem. I was wrong, it was code.!!

Appreciate the help from wsh2, with a similiar answer,will note for any future questions asked and answered.

Appreciate the help

Turnerrob
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now