Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Illinois
    Posts
    4

    Unanswered: Update Method ODBC Error

    I'm getting an error on some code that I wrote a while back, and which worked up until recently. Now it's telling me I have an ODBC error (3146). The back-end is an SQL server and the tables are linked to the server. I realize that the ADO method is preferrable in this case, but I'm not a VBA programmer (yet), and I can't shift the whole db to that standard just yet. Also, I've read that you have to use the "dbOpenDynaset" method of OpenRecordset when using linked tables...oh, and the Access 97 db I'm using as the front end is running on Windows 2000. Please let me know if you need more info. Thanks!!!!!

    -Chad

    Code:
    --------------------------------------------------------------------------------

    Private Sub duplicate_record_Click()
    Me.Refresh
    Dim dbs As Database
    Dim rec As Recordset
    Set dbs = CurrentDb()
    Set rec = dbs.OpenRecordset("dbo_Items", dbOpenDynaset, dbSeeChanges)
    rec.AddNew
    rec.Fields("Title") = Title
    rec.Fields("Authors") = Authors
    rec.Fields("JournalName") = JournalName
    rec.Fields("JournalVolume") = JournalVolume
    rec.Fields("JournalNumber") = JournalNumber
    rec.Fields("JournalDate") = JournalDate
    rec.Fields("BookEdition") = BookEdition
    rec.Fields("BookPublisher") = BookPublisher
    rec.Fields("StaffNote") = StaffNote
    rec.Fields("WeOwn") = WeOwn
    rec.Fields("Location") = Location
    rec.Fields("SourceCallNum") = SourceCallNum
    rec.Fields("ReserveUnit") = ReserveUnit
    rec.Update
    rec.MoveNext
    MsgBox ("Duplication successful!")
    dbs.Close
    Set rec = Nothing
    Me.Requery
    End Sub

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Update Method ODBC Error

    Change this line
    Set rec = dbs.OpenRecordset("dbo_Items", dbOpenDynaset, dbSeeChanges)
    to
    Set rec = dbs.OpenRecordset("dbo_Items", dbOpenDynaset)


    Originally posted by libsys
    I'm getting an error on some code that I wrote a while back, and which worked up until recently. Now it's telling me I have an ODBC error (3146). The back-end is an SQL server and the tables are linked to the server. I realize that the ADO method is preferrable in this case, but I'm not a VBA programmer (yet), and I can't shift the whole db to that standard just yet. Also, I've read that you have to use the "dbOpenDynaset" method of OpenRecordset when using linked tables...oh, and the Access 97 db I'm using as the front end is running on Windows 2000. Please let me know if you need more info. Thanks!!!!!

    -Chad

    Code:
    --------------------------------------------------------------------------------

    Private Sub duplicate_record_Click()
    Me.Refresh
    Dim dbs As Database
    Dim rec As Recordset
    Set dbs = CurrentDb()
    Set rec = dbs.OpenRecordset("dbo_Items", dbOpenDynaset, dbSeeChanges)
    rec.AddNew
    rec.Fields("Title") = Title
    rec.Fields("Authors") = Authors
    rec.Fields("JournalName") = JournalName
    rec.Fields("JournalVolume") = JournalVolume
    rec.Fields("JournalNumber") = JournalNumber
    rec.Fields("JournalDate") = JournalDate
    rec.Fields("BookEdition") = BookEdition
    rec.Fields("BookPublisher") = BookPublisher
    rec.Fields("StaffNote") = StaffNote
    rec.Fields("WeOwn") = WeOwn
    rec.Fields("Location") = Location
    rec.Fields("SourceCallNum") = SourceCallNum
    rec.Fields("ReserveUnit") = ReserveUnit
    rec.Update
    rec.MoveNext
    MsgBox ("Duplication successful!")
    dbs.Close
    Set rec = Nothing
    Me.Requery
    End Sub

  3. #3
    Join Date
    Jan 2004
    Location
    Illinois
    Posts
    4

    Re: Update Method ODBC Error

    Sorry for not replying sooner...out of the office. I used DB see changes because I was previously getting an error that said I needed to select this option when connecting to an SQL Serve with an Identity column. Anyway, that doesn't seem to be the problem. I get the feeling this is an ADO/DAO issue. But I'm no expert. Anyway, thanks for the response.

    Cheers,
    Chad

    Originally posted by khan
    Change this line
    Set rec = dbs.OpenRecordset("dbo_Items", dbOpenDynaset, dbSeeChanges)
    to
    Set rec = dbs.OpenRecordset("dbo_Items", dbOpenDynaset)

Posting Permissions

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