Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2009
    Posts
    89

    Unanswered: Telling access not to update the table??

    Hi. I've got a form bound to an SQL Server ODBC table. I'm having loads of issues when Updating any record on that form (only that form, every other form works fine). THe error is inconsistent, and I cannot find why it's happening.

    So now I want to let sql Server manage the updating of records for that table. This will mean a stored procedure that updates all the fields, which I can run on when the form is closed or when "save" is pressed.

    Question is, how Do i tell access to not try and update the records in the table that it is bound to?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look in th eforms on update events (either before or after update
    however if you are using a server back end you would be far better off using unbound forms,. reports and controls
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2009
    Posts
    89
    Ok, but then when you open the form in the first place, where would it get its information from? At the moment, I use the filter like this:

    Code:
    Docmd.OpenForm "RM", ,, "[ID] = " & Me.ID
    and as it's bound, it finds all the information and displays it. If it's not bound then I don't see when it will find the info. the form that opens it only has a list of names so it can't pass the information on.
    Last edited by rudeboymcc; 11-10-09 at 07:53.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can still work with a bound form. However none of it's controls can be bound the the underlying recordset, i.e. the controls (Textboxes, Optionframes etc.) cannot have anything assigned to their ControlSource property: your form is bound (it has a recordset) but it's controls are not. In such case, the name of the controls must be different from the name of the fields in the recordset. This has the advantage that you can rely on the built in mechanism of Access to manage several aspects of the form, such as: applying a filter, sorting, moving from one record to the next etc.

    Now you must provide your own mechanism to "feed" data from the form recordset to the controls. It can be done on the Current event of the form, like this:
    Code:
    Private Sub Form_Current()
    '
    '    Control.Value = Recordset!Field
    '
        Me.Text_Field1.Value = Me!Field1
        Me.Text_Field2.Value = Me!Field2
        Me.OptionX.Value = Me!FieldX
    ' etc.
    End Sub
    There are several methods to automate this operation without having to write a line of code for each control on the form, using a FOR EACH loop instead. You can do it through a naming convention, by using the Tag property to relate a control to its data field etc.

    And, of course, you must provide a mechanism to write back the modified values to the server, using stored procedures for instance.
    Have a nice day!

  5. #5
    Join Date
    Jun 2009
    Posts
    89
    Never thought of doing that ! bound forms with unbound conrols. think I'll try that it seems the best of both worlds :-)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by rudeboymcc View Post
    Ok, but then when you open the form in the first place, where would it get its information from? At the moment, I use the filter like this:

    Code:
    Docmd.OpenForm "RM", ,, "[ID] = " & Me.ID
    and as it's bound, it finds all the information and displays it. If it's not bound then I don't see when it will find the info. the form that opens it only has a list of names so it can't pass the information on.
    It's a catch-10. You can utilize the docmd.openform using the WHERE clause for a bound form but when it comes to an unbound form, you must manually populate all the fields on the form via a function, passing the ID to the function.

    See these posts:
    http://www.dbforums.com/microsoft-ac...lds-table.html

    I usually just link the tables into the mdb and keep the form bound. I consider unbound forms only when I have a problematic/slow user connection to the mdb location or I'm dealing with millions of records. You have to consider that it takes 2-3 times longer designing unbound forms since everything is function driven. Consider this along with your coding time.
    Last edited by pkstormy; 11-11-09 at 22:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Jun 2009
    Posts
    89
    THe only reason I am doing this is that I had a problem with a bound form not being able to update records (hanged for 2 minutes and then closed without updating with a timeout error).

    In the end the form is still bound, but the ID field is the only Bound box. So on load it receives all the values from the table, and then on update all the txt box values are passed to a SPROC that updates the table, with a warning if it fails.

    Code:
    Private Sub Form_Load()
        If Me.NewRecord = False Then
            Me.txtTenantID = Me.TenantID
            Me.txtLettingID = Me.LettingID
            etc
    .
    .
            ' For some reason these two fields are not found when I use
            ' Me.Controlled and Me.Reminder - anyone can suggest a solution?
            Me.chkControlled_Tenant = Forms![Tenancies Details].Controlled
            Me.txtReminder = Forms![Tenancies Details].Reminder
          
            
        End If
    End Sub
    
    Private Sub SaveAndClose_Click()
        If Me.NewRecord = True Then
            If Insert = True Then
                DoCmd.Close
            Else
                MsgBox "Failed to create tenancy."
            End If
        Else
            If Update = True Then
                DoCmd.Close
            Else
                MsgBox "Failed to save changed."
            End If
        End If
    cmdOK_Click_exit:
        Exit Sub
        
    cmdOK_Click_err:
        Call ErrorLog(Err.Description, Err.Number, Me.Name)
        Resume cmdOK_Click_exit
       
    End Sub
    
    
    Public Function Update() As Boolean
        Dim qdf As QueryDef
        Dim db As Database
    
       On Error GoTo Update_Error
    
            If Nz(Me.txtTenantID, "") = "" Or Nz(Me.txtLettingID, "") = ""  Then
                MsgBox "Please fill in all required fields (*)."
                Exit Function
            Else
                'Start UPDATE CODE
                
                ' Set database variable to current database.
                Set db = CurrentDb
                
               
                'Open QueryDef object.
                Set qdf = db.QueryDefs("Temp_TenancyUpdate")
            
                qdf.sql = "EXEC dbo.sp_TenancyUpdate @ID=" & Me.ID & "," _
                        & "@TenantID = " & Me.txtTenantID & "," _
                        & "@LettingID = " & Me.txtLettingID & "," _
                        & "@Startdate = '" & Format(Me.txtContractStartDate, "yyyy-mm-dd") & "'," _
                        & "@Enddate = '" & Format(Me.txtContractEndDate, "yyyy-mm-dd") & "'," _
                      etc
    .
    .
    .
                        
                qdf.ReturnsRecords = False
                qdf.Execute
    
                qdf.Close
                db.Close
                Set db = Nothing
                
                'END UPDATE CODE
                
            End If
        Update = True
        
       On Error GoTo 0
       Exit Function
    
    Update_Error:
        Call ErrorLog(Err.Description, Err.Number, Me.Name)
    End Function
    Insert code is nearly the same as update code, just uses a different SPROC.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by rudeboymcc View Post
    THe only reason I am doing this is that I had a problem with a bound form not being able to update records (hanged for 2 minutes and then closed without updating with a timeout error).

    If you have SQL Server as a backend and linked tables into a frontend, you can design your forms bound to those tables and you won't have problems.

    There are some things you should check though to make sure you won't have problems:

    1. Autonumber type field in the SQL Server (ie. identity field, increment = +1)
    Not having an autonumber type field (on SQL Server or MSAccess) will give you slow response time and other problems.

    2. After 1 above is confirmed, see if it hangs when you're just updating the table or via the form.
    If it's slow via the table, you have other problems not related to MSAccess. You're getting a slow connection to the backend data. Check indexes, connections, etc..
    If it's slow via the form, you need to work on your form design. See page 6 in the code bank for some suggestions.

    3. Make sure EVERYONE who is using the mdb, has EXACTLY the SAME ODBC DSN Name. Someone connecting to the same tables via a different ODBC DSN will cause problems.

    Don't change your method of linked tables with a bound form until you fully know what's causing the problem. This is a typical setup (I do it all the time) and it works fast and efficient with rarily any problems.

    If you can't update records on a form:
    1. 1st check to see if you can update them in the underlying recordsource of the form (ie. open it and see if they are updatable.)
    2. If they are updatable in the recordsource query, then you're dealing with some form limitation. This could be many things (ie. a required field isn't getting updated, etc...)

    If it takes 2 minutes to load the form. It's because you're loading ALL the records on the form AND subform(s) when it opens. Again, see page 6 in the code bank for some techniques. You don't typically want ALL the records loading and the recordsource in your subform should have criteria in it so that it only loads records related to the single record on the main form. See this post: http://www.dbforums.com/6366815-post77.html
    Last edited by pkstormy; 11-13-09 at 00:47.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jun 2009
    Posts
    89
    @pkstormy, the failed updates also happen when directly editing the linked table (i.e not through a form), so I know it's not the form.

    You say it's a slow connection to the sql server. Everything is connected via gigabit ethernet, and there is no trouble loading other forms that have 10 thousand records (done as just a test). every computer uses the same DSN name (as they all use the same front end), with the same username and password (which is coded into the front end, i.e. it doesn't use their windows login). Plus this has happenned with just one computer using the database.

    The table has always had an autonumber primary key which is indexed, even before it had any data in it, so there's no chance that there is data in there that violates any of the criteria. interestingly though, when the problem occurs, if I rebuild the index for that table it has always worked after for at least 1 update, only to fail again very soon.

    Anotehr wierd thing is that an insert statement never fails. it is just an update that sometimes hangs.

    Any thing you can think of that I can try?

    EDIT: also, it doesn't take 2 minutes to load the forms. IF someone updates and it hangs, it will hang for two minutes.

  10. #10
    Join Date
    Jun 2009
    Posts
    89
    Ok turns out the timeout still occurs when running the query in SQL server Mangement studio, so I've asked for help in teh Microsoft SQL forum :
    http://www.dbforums.com/microsoft-sq...-looks-ok.html

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    hmmm....

    I create SQL Server tables daily.

    Usually a 'delay' when updating only 1 table directly on SQL Server (and then an error return) indicates that SQL Server is struggling somewhere with the validation parsing/relationships on that specific table (which causes a delay and then the error return.) Explaining why an insert might possibly work correctly where an update causes problems (other than table corruption) could be due to default values versus validation rules for 1 or more fields.

    I would first check default value properties and validation rules on ALL fields in this table. Especially check relationships as relationships aren't as easily drawn out as in MSAccess. An incorrect relationship would definitely slow things down.

    You may also want to try adding a 'TimeStamp' field type to the table (you can name it anything.) This was a 'hack' fix from Microsoft for problems where the identity/index wasn't working correctly with other field types in the table (I believe if you had a Memo/Text type field in the table. You'll notice the MSAccess -> SQL Server -> table upsizing wizard often creates this field automatically.)

    You mentioned that it works shortly after re-indexing but then doesn't. This is concerning. An insert working where an update not working is also concerning. I may even consider designing a new table and then copying all the data (recreating the relationships).

    Another last thing to try is to run a dbcc command on SQL Server itself against the database. There are 2-3 different SQL Server commands you can run to do something similar to the compact/repair command in MSAccess (I can't recall the 3 key ones - 1 is dbcc though.) I often had to run these with SQL Server 2000 and occasionally with 2008 (I'll see if I can recall the other 2 key ones.)

    You can also run a 'Trace' in SQL Server and then attempt to update the table. The trace will show you where there are locks and other things slowing down the process (note: you may have to add things to monitor in the trace.) Also check the SQL Server Event Logs. These are sometimes a good source for tracking down problems like this.
    Last edited by pkstormy; 11-13-09 at 20:01.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jun 2009
    Posts
    89
    Posted a reply in my other thread, I figured I'll use the SQL server forums.
    http://www.dbforums.com/microsoft-sq...ml#post6434179

Posting Permissions

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