Results 1 to 14 of 14
  1. #1
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69

    update an sql server linked table

    the access db has a table that is 'linked' to a share point sql server table, I don't know the specifics of the link
    I didn't create an odbc or sdn, etc the 'link' has been setup by sys admin
    I can query and update the sql server table using access query

    however no luck trying to use vba code to find recs in the sql table, and then update them,
    also need to add recs and delete recs
    I have been using dao to open the sql table which works and I can read it
    it balks when trying to do a find or seek, I get the argument not optional error

    Set db = CurrentDb
    Set rstTargetTable = db.OpenRecordset("table_To_update")
    Set rstSourceTable = db.OpenRecordset(tblName, dbReadOnly)
    rstTargetTable.Seek = rstSourceTable!cqid.Value 'there is some thing there

    also getting operation not supported if I try to establish an index, err 3251
    rstTargetTable.Index = "idx" ' a valid column in the sql table



    the target table has an access generated auto primary ID

    is there some way I have to open the table, I doubt if it would let me go exclusive, just need to open for read/write

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    did you try running an update query?
    do you have rights to update?

  3. #3
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69
    You must have missed my original thread..."I can query and update the sql server table using access query"

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Seek and Index don't work on linked tables:

    http://www.access.mvps.org/access/tables/tbl0006.htm

    That said, it's inefficient to do it. I'd open the recordset on the desired record, rather than the whole table:

    Set rstTargetTable = db.OpenRecordset("SELECT * FROM table_To_update WHERE KeyField = " & rstSourceTable!cqid)

    adapted as appropriate to your situation.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69
    thanks for the reply
    so this db has about 600 recs
    your suggesting to open it 600 times, to simulate a seek that is not possible, yes?
    I appreciate your concern about inefficiency, however it can't be less efficient than opening the recset 600 times??
    but I would agree its a mute point if seek and index just not allowed for linked tables

    the link in your post has the solution below, however my 2016 access/vba does not have "OpenForSeek" and that solution seems to indicate you can use seek and index after all?
    +++++++++++++++++++++++++++++++++++++++++++++
    Just use:
    Dim rst as Recordset
    set rst=OpenForSeek("TableName")

    and you can use rst.Seek and rst.Index on linked table TableName.
    ++++++++++++++++++++++++++++++++++++++++++++++


  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is there more to your code? Were you looping the source table or something? I was assuming you were opening the recordset once to update a single record. If you're updating all the records, an update query would typically be a better option, as ranman suggested.

    The OpenForSeek function was included in the link. You'd copy/paste it to a standard module in your db.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69
    also I found the seek syntax problem, it needs quotes and a comma
    rstTargetTable.Seek "=", rstSourceTable!idx.Value but since I can seem to use seek anyway...........................

    and was trying to use findfirst instead of seek but so far can't get the
    right syntax, tried a bunch of variations

    first I open the table with dbOpenDynaset then
    rstTargetTable.FindFirst "=", rstSourceTable!idx.Value results in compile error wrong number of args or invalid property
    or
    rstTargetTable.FindFirst "rstSourceTable!idx = 'rstSourceTable!idx.Value'" results in syntax error missing operator

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The syntax is the same as the wherecondition argument:

    http://www.baldyweb.com/wherecondition.htm

    so for a numeric value something like:

    rstTargetTable.FindFirst "idx = " & rstSourceTable!idx

    presuming idx is the field name.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69
    ok this works, it seems if its alpha it needs the ' surrounding the field
    rstTargetTable.FindFirst "'rstSourceTable!idx' = 'rstSourceTable!idx'"

    it also works if there is a space in the variable name
    rstTargetTable.FindFirst "'rstSourceTable!idx 123' = 'rstSourceTable!cqid.Value'"

    I wonder if the sql wizards can actually make a findfirst basically as efficient as a seek?
    in my case for 600 recs 1x per week, I could even just sequential read start to finish to find or not the rec I want if I had too!!!!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Do those actually work? I'd expect syntax like my link.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69
    well the findfirst I posted executes with no errors but it always results to nomatch=true even though for sure there are recs there that match
    I have tried with multiple fields, been checking many posts, but right now baffled and frustrated after all the time it took to find seek is a no go and quite a few iterations of findfirst

    rstTargetTable.FindFirst "'rstTargetTable!idx' = 'rstSourceTable!idx'" (first post above had a typo but this code is working
    if
    rstTargetTable.nomatch = true then.......

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you completely changed from what I showed, and didn't follow the example for a text field from my link. That looks like:

    "FieldName = '" & Me.ControlName & "'"

    Which for you translates to:

    rstTargetTable.FindFirst "idx = '" & rstSourceTable!idx & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    bbxrider is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2010
    Posts
    69
    wow, many thanks for this long trail, afraid to say I don't understand how that string is being built with the combinations of ' and "
    and now to also fully realize that .nomatch=false means a rec was found
    I did get a version working when the target rst, (the searched rst) field has no spaces ie "idx" however I was using that for posting examples, my actual search field has a space in the name, eg "idx 123"
    so I tried this and seems to be working

    rstTargetTable.FindFirst "[idx 123] = '" & rstSourceTable!idx & "'"

    so very happy to have this working and again thanks very much but hate it when that happens and I'm not sure why....

    any suggestions for links to explain how the ' and " work to build strings and with something like findfirst where to find things like ....you can interchange using []'s or strings to specify field names

    Paul (wino moderator)

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This has a lot of the "rules":

    http://www.baldyweb.com/BuildSQL.htm

    Generally speaking, things like FindFirst and the wherecondition of OpenForm shown in the link above follow the same rules. They are an SQL WHERE clause without the word "WHERE".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 12-02-2016, 04:14 PM
  2. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  3. Replies: 1
    Last Post: 01-22-2011, 12:23 PM
  4. Linked SQL Server Table Problems
    By jalton in forum Import/Export Data
    Replies: 3
    Last Post: 02-13-2010, 12:52 AM
  5. Linked Table To SQL Server?
    By snkscore in forum Import/Export Data
    Replies: 3
    Last Post: 12-09-2009, 06:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums