Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Index & Seek on Linked Tables (XP)

    I am attempting to split a small database so that all data is in one database, and queries, forms, and VBA code in another. I have created the linked tables in the second database and all queries work fine. I am having problems converting my VBA code. I get a runtime error 3251 - Current provider does not support the necessary interface for Index functionality, when trying to access a PrimaryKey. The program works fine in its original location. What changed between using a local vs. linked table?
    Any help is greatly appreciated. Thank you.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Index & Seek on Linked Tables (XP)

    I think we are going to need to see the code to determine why the error occurs. I presume you are linking to another MDB, and not to MSDE or SQL Server, or some other database engine.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Index & Seek on Linked Tables (XP)

    Are you trying to use Seek in code on a linked table? In DAO or ADO code? As Wendell suggested, you're going to have to post the specific code that is giving you problems.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index & Seek on Linked Tables (XP)

    I am really sorry for not answering sooner (vacation and hot issues on my return didn't help). My original message stated that the linked tables didn't want to work in the same fashion as local ones, especially when trying to index an ADO recordset. This is the first part of my program, where the issue arises. I sent the logic prior to the error as well. I don't know if it is relevent or not.

    The error occurs when checking tblWood - with line CommodRst.Index = "PrimaryKey"

    Once again, thank you for your time. If I get a breakthrough, I will let you know.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index & Seek on Linked Tables (XP)

    Charlotte
    I am really sorry for such a slow response. I appreciate your feedback. I have attached a copy of my program.
    The program uses ADO recordsets. This is my first attempt at using linked tables, so that is probably some of the problem.

    The problem child of the program seems to be that it does not like the line
    CommodRst.Index = "PrimaryKey"
    Once again - Thank you!!! I will be more prompt in future communications.
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index & Seek on Linked Tables (XP)

    I think I found a possible solution. It revolves around my lack of knowledge in ADO and linked table world. In the Access 2000 Developer's Handbook Volume 2, pages 27-28. there is an example of how to use Indexes and Seeks for linked tables in the ADO world. (I always thought of a handbook as something less than 1100 pages)

    This is the code used, which works fine. I think the difference is that it opens the table, instead of sending a query to extract information to populate the recordset.

    Dim cnn as ADODB.Connection
    Dim cat as ADOX.Catalog
    Dim strDB as String
    Dim rst as ADODB.Recordset

    Set cnn = Current Project.Connection
    Set cat = New ADODX.Catalog
    cat.ActiveConnection = cnn

    'This is supposed to get the name of the database if it exists or strDB remains empty
    strDB = cat.Tables(<table name>).Properties("Jet OLEDB:Link Datasource")

    If Len(strDB) > 0 then
    'Open a connection to the external database
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strDB & ";"
    End If

    Set rst = New ADODB.Recordset
    'This opens a table - type recordset to use Seek vs what I have been using - a query to populate the recordset.
    rst.Open <tablename>, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    From here, Indexes and Seeks can be done.
    I don't know how I overlooked the code before, but I did. Thank you for your interest.

  7. #7
    Star Lounger
    Join Date
    Aug 2002
    Location
    Des Moines, Iowa, USA
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index & Seek on Linked Tables (XP)

    I found what seems to be the solution. I sent this to WendallB as well.

    This was found in the Access 2000 Developer's Handbook, Volume 2:Enterprise Edition pages 27-28. I've cut it down somewhat to show only the pertinent information.

    Dim cnn as ADODB.Connection
    Dim cat as ADOX.Catalog
    Dim strDB as String
    Dim rst as ADODB.Recordset

    Set cnn = Current Project.Connection
    Set cat = New ADODX.Catalog
    cat.ActiveConnection = cnn

    'This is supposed to get the name of the database if it exists or strDB remains empty
    strDB = cat.Tables(<table name>).Properties("Jet OLEDB:Link Datasource")

    If Len(strDB) > 0 then
    'Open a connection to the external database
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strDB & ";"
    End If

    Set rst = New ADODB.Recordset
    'This opens a table - type recordset to use Seek vs what I have been using - a query to populate the recordset.
    rst.Open <tablename>, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    I think that this code extracts the table, instead of using an SQL statement to extract it. Since it extracts the table itself, Index and Seek commands work with it fine.
    Once again - Thank you for your time and interest.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •