Page 1 of 4 1234 LastLast
Results 1 to 15 of 50
  1. #1
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147

    Index = "PrimaryKey" error with SQL BE linked

    I have a database I have the tables linked to SQL Server 2008. As going through my form updating the VBA errors I have two I can not figure out. The Index and Seek (highlighted in green) highligh it's self in yellow in the module. The primary key is set on ID_D.
    Have any ideas?
    Private Sub Display()
    Dim DAO As Database
    Dim RS As Recordset
    Dim LngCnt As Long

    LngCnt = 0

    Set DAO = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.ac cdb")

    Set RS = DAO.OpenRecordset("dbo_TblRDStudyAuditDocument")


    LngCnt = RS.RecordCount

    With RS
    If LngCnt > 0 Then
    .MoveFirst
    .Index = "PrimaryKey"
    .Seek "=", ID_D
    If .NoMatch Then
    MsgBox ("No Match"), vbInformation, "ERROR: Display()"
    Exit Sub

    Else

    Studyid = .Fields(1)
    AuditDate = .Fields(2)
    TxtSectionName = .Fields(3)
    DocName = .Fields(4)
    Comments = .Fields(5)

    End If

    End If
    .Close

    End With

    Set DAO = Nothing
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have never used .Index or .Seek but then I haven't done much with SQL db.

    With recordsets of Access tables, I use .FindFirst

    Found example that shows this syntax: .Seek ("T")
    http://www.java2s.com/Code/VBA-Excel...nRecordset.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    Thanks but that won't work with a SQL BE.

  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
    If memory serves, Seek is not available on linked tables. In any case, much more efficient would be:

    Set RS = DAO.OpenRecordset("SELECT * FROM dbo_TblRDStudyAuditDocument WHERE PrimaryKey = " & ID_D)

    If I've got everything right. Then you're not pulling the whole table over the wire.

    PS: I would not have a variable with the name "DAO", as it can be confusing. Also, I'd disambiguate your declarations:

    Dim DAO As DAO.Database
    Dim RS As DAO.Recordset
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    Where do I place this: Set RS = DAO.OpenRecordset("SELECT * FROM dbo_TblRDStudyAuditDocument WHERE PrimaryKey = " & ID_D)

    Thanks!

  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
    In place of this, which opens the entire table:

    Set RS = DAO.OpenRecordset("dbo_TblRDStudyAuditDocument")

    You can test for EOF which would indicate no records were found:

    If RS.EOF Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    It didn't like that all. Gives me a run error '3622'. Says must use dbSeeChanges option with OpenRecordset......
    Highlights your script in yellow.

  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
    Set RS = DAO.OpenRecordset("SELECT...", dbOpenDynaset, dbSeeChanges)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    Now I get a Run-Time error 3075.
    Syntax error (missing operator) in query expression '...'.

  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
    What exactly is your code now? What is in ID_D?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, and I'd use a variable for the SQL so you can debug it:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    Here is a copy of the form use. The ID_D is the primary key for the table and is set to unique. ID_D is on the drop-down where it shows 111 in the text box. when I select it, it selects the ID for that record and then I would click Display Record so I can make changes to the record then I would select update. They are in written in VBA.

    Click image for larger version. 

Name:	Form.jpg 
Views:	6 
Size:	66.3 KB 
ID:	8275

    Here is the code:

    Private Sub Display()
    Dim DAO As DAO.Database
    Dim RS As DAO.Recordset
    Dim LngCnt As Long

    LngCnt = 0

    Set DAO = OpenDatabase("W:\RCO\Databases\SQL_RCO_Database.ac cdb")

    Set RS = DAO.OpenRecordset("SELECT (*), dbOpenDynaset, dbSeeChanges")
    LngCnt = RS.RecordCount


    With RS
    If LngCnt > 0 Then
    .MoveFirst
    .Index = "PK_TblRDStudyAuditDocument"
    .Seek "=", ID_D
    If .NoMatch Then
    MsgBox ("No Match"), vbInformation, "ERROR: Display()"
    Exit Sub

    Else

    Studyid = .Fields(1)
    AuditDate = .Fields(2)
    TxtSectionName = .Fields(3)
    DocName = .Fields(4)
    Comments = .Fields(5)

    End If

    End If
    .Close

    End With

    Set DAO = Nothing
    End Sub


  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You need valid SQL in the OpenRecordset line. You've got options and all within the quotes. Plus your field is text, so try

    Set RS = DAO.OpenRecordset("SELECT * FROM dbo_TblRDStudyAuditDocument WHERE ID_D = '" & Me.ID_D & "'", dbOpenDynaset, dbSeeChanges)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Brian62 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2009
    Posts
    147
    Still giving me an error of 3464.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. "not an index in this table" error
    By everette in forum Access
    Replies: 4
    Last Post: 04-06-2012, 05:03 AM
  2. Replies: 2
    Last Post: 11-04-2011, 02:45 AM
  3. Replies: 1
    Last Post: 04-25-2011, 12:36 PM
  4. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM
  5. Replies: 4
    Last Post: 06-10-2010, 07:51 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