locked
trying to open a linked table.... what's going on? RRS feed

  • Question

  • run-time errors: 3219 and 3251!

    Trying to open a linked table in order to directly access some records. What am I doing wrong?

     dim db as dao.database
     dim rst as dao.Recordset
      Set db = CurrentDb()
     Set rsT = db.OpenRecordset(stLnkTblName) ' ,dbOpenTable)
     rst.Index = "PrimaryKey" - err 3251 is raised here  

    error 3219  is raised when I try to open  with 'dbOpenTable

     

    Monday, May 6, 2013 7:02 PM

Answers

  • Hi all,

    Thanks for your help and consideration.

    I don't know why, or remember exactly the circumstance, under which FindFirst bombed, could have been a typo, however I abandoned that approach and  rewrote the code to use the Seek method - that approach seemed more straightforwards to me. But I was having a hard time with it, probably because this was the first time I used it. But it's all good now. The problem was   trying to OpenTable and do Seek method on a linked table and ACCESS wasnt having it.

    So I found another approach:

    Dim db  As dao.Database
    Dim rsT As dao.RecordsetSet

    db = OpenDatabase(Mid(DBEngine(0)(0).TableDefs(stT1).Connect, 11))
    Set rsT = db.OpenRecordset(stT1)  

    ' directly opens the database containing the linked table and creates a recordset of Type 1(dbOpenTable) 
    ' while the traditional: db=CurrentDB; set rst=db.OpenRecordset(stT1)
    'creates a recordset of type 2 and you can't do Seek on Type 2  

    rsT.Index = "PrimaryKey"
    rsT.Seek "=", lngKey

    If rsT.NoMatch Then
               'not found
               'skip 
            Else
                rsT.Edit 

    ~~~~ etc.

    - Cheers!

    • Proposed as answer by Dummy yoyo Wednesday, May 8, 2013 1:34 PM
    • Marked as answer by Dummy yoyo Tuesday, May 21, 2013 11:45 AM
    Tuesday, May 7, 2013 9:22 PM

All replies

  • You can not open a linked table as a Table type recordset.  The common alternative is to open the recordset as dbForwardOnly or dbDynaset and use FindFirst instead of Index and Seek.  Yes, using Seek is faster, but unless you are doing thousands of them in a loop, you'll never notice the difference.

    If you have an overwhelming reason to use Index and Seek, then open the table's database and refer to the table directly in that database.  If you can justify doing it this way and need help with it, post back.

    Monday, May 6, 2013 7:38 PM
  • run-time errors: 3219 and 3251!

    Trying to open a linked table in order to directly access some records. What am I doing wrong?

     dim db as dao.database
     dim rst as dao.Recordset
      Set db = CurrentDb()
     Set rsT = db.OpenRecordset(stLnkTblName) ' ,dbOpenTable)
     rst.Index = "PrimaryKey" - err 3251 is raised here  

    error 3219  is raised when I try to open  with 'dbOpenTable

    You can only set the .Index property or use the .Seek method with table-type recordsets, and (as you appear to have found) you can't open a table-type recordset on a linked table.

    If your purpose won't be served by opening a dynaset on a SELECT query with a WHERE clause that specifies the primary key values of the records you want to read, you can open a Database object on the back-end database and open a table-type recordset on the local table in that database, rather than opening your recordset on the linked table in the front-end.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, May 6, 2013 7:40 PM
  • From my testing, you can't set an index like that on a linked table.  Is there a reason why you want to do it that way and not use an Order By clause in conjunction with a query?

    Bob Larson, Access MVP 2008, 2009, 2011

    Monday, May 6, 2013 7:40 PM
  • Ok I think I found the problem and it don't look too good.

    So if it's a local table - openrecordset gives tou a recordset on which you can seek, But if it's a linked table -

    you must first open the database where original table is and create a recoset and only then you can seek?

    I am not too happy with this roundabout way .... 

    blah....

    Monday, May 6, 2013 7:56 PM
  • Ok I think I found the problem and it don't look too good.

    So if it's a local table - openrecordset gives tou a recordset on which you can seek, But if it's a linked table -

    you must first open the database where original table is and create a recoset and only then you can seek?

    I am not too happy with this roundabout way .... 

    blah....


    You don't need SEEK to find something using a linked table.  You can use FindFirst instead.

    Bob Larson, Access MVP 2008, 2009, 2011

    Monday, May 6, 2013 7:58 PM
  • So if it's a local table - openrecordset gives tou a recordset on which you can seek, But if it's a linked table -

    you must first open the database where original table is and create a recoset and only then you can seek?

    I am not too happy with this roundabout way .... 

    It is very rare that you need to actually seek in a recordset.  If you know, before opening the recordset, the primary key(s) of the record(s) you want, open the recordset on a SQL statement which uses a WHERE clause to select just the record(s) you want.  The database engine will automatically use the index to restrict those records.  If you have a recordset already and need to find a record within it, use the .FindFirst or .FindNext method to navigate the recordset to the desired record.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, May 6, 2013 8:16 PM
  • Thank you all for confirming what I refused to beleive in.

    FindFirst also bombed on me..... for resons unknown..... so I started down the path of Seek and when that bombed .... well thanks all for spelling out to me the error of my ways.

     

    Monday, May 6, 2013 11:22 PM
  • "bombed" won't help us figure out why FindFirst didn't work and I think we should pursue that issue.  The first thing is to double check is if the DAO library is referenced.  It probably is or you would have gotten a compile error on the Dim db As DAO.Database line.

    The next thing is to check the syntax of the FindFirst line.  It should look something like:

       dim db as dao.database
       dim rst as dao.Recordset
       Set db = CurrentDb()
       Set rst = db.OpenRecordset(stLnkTblName, dbOpenDynaset) 
       rst.FindFirst "PrimaryKey =" & {numeric search variable}
    or
       rst.FindFirst "PrimaryKey =""" & {string search variable} & """ "
       If rst.NoMatch Then Exit Sub 'or something to deal with no matching record

    Note:  When posting code, please use Copy/Paste so we don't waste time going back and forth over posting typos.

    Monday, May 6, 2013 11:53 PM
  • Hi ADG144

    As you have a linked table it's not possible to open a Recordset with dbOpenTable. Unfortunatly the OH doesn't have this information inside anymore.

    Try following approach and completely forget about Seek, just read the record you are looking for with a WHERE condition:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDB
    Set rst = db.OpenRecordset("SELECT * FROM [" & stLnkTblName & "]" & _
     " WHERE ID=" & lngYourID, dbOpenDynaset)
    
    'do something with your recordset here
    
    rst.Close
    Set rst = Nothing
    Set db = Nothing

    use dbOpenSnapshot instead of dbOpenDynaset, if you don't want to change the data. In addition also set the Lockoption to dbSeeChanges if your linked Table is from an ODBC datasource and you edit it's content, so ensure Access get notified about the record changes you made.

    Henry
     "ADG144" schrieb im Newsbeitrag news:d402a8d2-0771-458c-b57e-09e2d6f0c536@communitybridge.codeplex.com...

    run-time errors: 3219 and 3251!

    Trying to open a linked table in order to directly access some records.
    What am I doing wrong?

    dim db as dao.database
    dim rst as dao.Recordset
     Set db = CurrentDb()
    Set rsT = db.OpenRecordset(stLnkTblName) ' ,dbOpenTable)
    rst.Index = "PrimaryKey" - *err 3251 is raised here  *

    error 3219  is raised when I try to open  with 'dbOpenTable

    Tuesday, May 7, 2013 3:24 AM
  • Thank you all for confirming what I refused to beleive in.

    FindFirst also bombed on me..... for resons unknown..... so I started down the path of Seek and when that bombed .... well thanks all for spelling out to me the error of my ways.


    Show us your original code and tell us what you're trying to accomplish, and we can probably tell you what was wrong and how to fix it.  As Marshall noted, "it bombed on me" is not informative enough to allow remote debugging.  :-)

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, May 7, 2013 3:05 PM
  • "bombed" won't help us figure out why FindFirst didn't work and I think we should pursue that issue.  The first thing is to double check is if the DAO library is referenced.  It probably is or you would have gotten a compile error on the Dim db As DAO.Database line.

    The next thing is to check the syntax of the FindFirst line.  It should look something like:

       dim db as dao.database
       dim rst as dao.Recordset
       Set db = CurrentDb()
       Set rst = db.OpenRecordset(stLnkTblName, dbOpenDynaset) 
       rst.FindFirst "PrimaryKey =" & {numeric search variable}
    or
       rst.FindFirst "PrimaryKey =""" & {string search variable} & """ "
       If rst.NoMatch Then Exit Sub 'or something to deal with no matching record

    Note:  When posting code, please use Copy/Paste so we don't waste time going back and forth over posting typos.


    I doubt, though, that "PrimaryKey" is the actual name of the primary key field.  Access assigns the name "PrimaryKey" to the index created when you click the little "key" button to designate a particular field (or fields) as the primary key.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, May 7, 2013 3:07 PM
  • Hi all,

    Thanks for your help and consideration.

    I don't know why, or remember exactly the circumstance, under which FindFirst bombed, could have been a typo, however I abandoned that approach and  rewrote the code to use the Seek method - that approach seemed more straightforwards to me. But I was having a hard time with it, probably because this was the first time I used it. But it's all good now. The problem was   trying to OpenTable and do Seek method on a linked table and ACCESS wasnt having it.

    So I found another approach:

    Dim db  As dao.Database
    Dim rsT As dao.RecordsetSet

    db = OpenDatabase(Mid(DBEngine(0)(0).TableDefs(stT1).Connect, 11))
    Set rsT = db.OpenRecordset(stT1)  

    ' directly opens the database containing the linked table and creates a recordset of Type 1(dbOpenTable) 
    ' while the traditional: db=CurrentDB; set rst=db.OpenRecordset(stT1)
    'creates a recordset of type 2 and you can't do Seek on Type 2  

    rsT.Index = "PrimaryKey"
    rsT.Seek "=", lngKey

    If rsT.NoMatch Then
               'not found
               'skip 
            Else
                rsT.Edit 

    ~~~~ etc.

    - Cheers!

    • Proposed as answer by Dummy yoyo Wednesday, May 8, 2013 1:34 PM
    • Marked as answer by Dummy yoyo Tuesday, May 21, 2013 11:45 AM
    Tuesday, May 7, 2013 9:22 PM