Results 1 to 5 of 5
  1. #1
    penn
    Guest

    Trouble updating a table

    Hello,
    I'm new to Access and am having a problem with a new database I have created. I am using an Access 2000 frontend with direct ODBC links to an Oracle8 backend. I have managed to create forms which successfully add data to the tables but I can't seem to update the tables. The field I am trying to update is a date field and what I want is when someone enters a changed date into the text box on the form for the table to be updated. I have tried the following codes but neither of them have worked my first attempt is the one which is commented out.

    Private Sub cmdCommit_Click()
    Dim wrk As Workspace
    Dim dbs As Database
    Dim strInsertDate As String
    Dim rstInsertDate As Recordset
    Dim newExDate As Date
    Set wrk = DBEngine(0)
    Set dbs = CurrentDb
    newExDate = Me.txtChangeImpDate

    'strInsertDate = "update developer_cm_change set exp_impl_date = " & newExDate & " where change_ref = " & Me.cboChange & ""
    'dbs.Execute strInsertDate
    Set rstInsertDate = dbs.OpenRecordset("Q_change_exp_impl_date", dbOpenDynaset)
    wrk.BeginTrans
    rstInsertDate.Edit
    rstInsertDate!exp_impl_date = newExDate
    rstInsertDate.Update

    End Sub

    I would be so grateful for any help as there are a few more fields which need updating but I thought I'd try and get this one working before moving on to the others.

    Thanks very much
    Penny

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trouble updating a table

    Hi Penny,
    2 quick questions:
    First, is there a Commit within your code?
    Second, do you get any errors or does the update simply not 'take'?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    penn
    Guest

    Re: Trouble updating a table

    Hello,

    Thanks for responding so quickly. Yes there was a commit when I tried updating the recordset, and there were no errors just the change not taking.

    Cheers
    Penny

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

    Re: Trouble updating a table

    You opened a transaction in your code with wrk.BeginTrans. You must have a wrk.CommitTrans in the same routine or the transaction will roll back automatically.
    Charlotte

  5. #5
    penn
    Guest

    Re: Trouble updating a table

    Thanks for everybodys help. In the end I went with the insert into statement. Apparently I had my quotation marks wrong and needed to add single quotations as well as the double ones as I was inserting from a text box.

    Cheers
    Penn

Posting Permissions

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