Need to Confirm Connection to Server Database

[Ross1]

I have a project with an SQLServer2000 Database back-end and an Access2000 front-end.

On one of my forms, I'm want to confirm that my network connection is still good before attempting to update the data on the server.

Here's a paraphrased sample of the code I've been using:

   Test_Data_Connection:
        If CurrentProject.IsConnected = True Then
            rsRecordSource(2).Value = NewStatus.Value
            rsRecordSource.Update
            ...
        Else
           If MsgBox("Error connecting to data source." ...
               vbRetryCancel + vbQuestion, "Error") =       vbRetry Then
               GoTo Test_Data_Connection
            Else
                DoCmd.Close acForm, Me.Name
                MsgBox "You're changes were not saved.", vbInformation + vbOKOnly, "FYI..."
            End If
        End If

Is there a better, (more effective) way to test the connection to the database than using 'CurrentProject.IsConnected'?

Our WAN connections are sometimes shaky and users often get an error message that says, "Write Conflict...another user has modified the record..." I believe they're getting this error because the network connection is temporarily interrupted while they're using the application, but I could be wrong. Any thoughts? Maybe I'm attacking the wrong problem.

 

[cmmrfrds]

Does this happen on all the tables, or just certain tables. Is this happening on certain Forms. What is the datasource for these Forms. Are you using an Access MDB or ADP. Can you show the properties of the recordset (ADO I assume). For example, cursor location, lock type, cursor type, etc...

 

[Ross1]

Hi 'cmmrfrds',
The Error I mentioned in my final paragraph occurs when the users are using a form in an Access ADP project connected to an MS SQLServer2000 database. The recordsource for the form is an updateable View saved on the server.

Regarding your last question: Are you referring to the recordset in my code sample or the recordset/recordsource I'm currently using on the form, which would be the view I mentioned above?

thanks for your interest in my situation.

Ross

 

[cmmrfrds]

Can you paste in the SQL for the View? Is this the only recordsource that gives you a problem?

 

[Ross1]

Here's the SQL for the view: (I know using the pound sign in a field name is very bad form, but it was inherited.)

SELECT     TOP 100 PERCENT TIN#, ShortName, LastName, FirstName, DBA, History, LastUpdateHistory, LastUpdateHistoryBy, Address, City, State, Zip, AsgndOfficer, AsgndOfcrSigned, SecondaryOfficer, DateLastSiteVisit, DateLastDocReview, NewReasonForRiskRating, LastUpdateNewRFRR, LastUpdateNewRFRRBy, ReasonForRiskRating, NewStatus, LastUpdateNewStatus, LastUpdateNewStatusBy, CrntStatus, NewActionPlan, LastUpdateNewActionPlan,  LastUpdateNewActionPlanBy, ActionPlan, NewLossExposure, LastUpdateNewLossExposureBy, LastUpdateNewLossExposure, LossExposureCode, LossExposure, UpgradeTrig, LastUpdateUpgradeTrig, LastUpdateUpgradeTrigBy, DowngradeTrig, LastUpdateDowngradeTrig, LastUpdateDowngradeTrigBy, CurrentSummary, Inactive, TotalOutstandings, RequiresCUR, Foreclosure, ReasonForCUR, CURDate, Locked, FirstName + ' ' + LastName AS FirstLast, City + ', ' + State + ' ' + Zip AS CityStateZip, NotePad
FROM         dbo.tblGenCURInfo
WHERE     (Inactive = 0) AND (RequiresCUR <> 0)
ORDER BY ShortName

When the user opens the form, a ServerFilter is applied on the load event:

    Me.ServerFilter = strPubWhere
    Me.Refresh

Where strpubwhere is a where clause created by the user's selections on the previous form.

I'm using the ServerFilter on the Load event because I was getting an errors when I tried to use the where clause at the end of the DoCmd.OpenForm command.

 

[cmmrfrds]

Can you update records through the View directly with a product such as Query Analyzer? There can be additional restrictions on Views that are not on the base table when it comes to updating.

A couple of links.

http://advisor.com/doc/13450

http://www.sqlteam.com/item.asp?ItemID=1015

 

[Ross1]

That's the curious thing...I can edit records right in the datasheet view and I can edit the records from the form...most of the time.

Are you thinking it could be an issue with the record locking?

Thanks for your thoughts. I'll check out the links.

 

[cjowsey]

This has happened to me because the table is being updated directly through form navigation or saving (e.g. DoCmd.RunCommand acCmdSaveRecord[/color blue]) and through a background action at the same time where a stored procedure or database trigger changes the underlying record. This means that the data Access originally retrieved is different to that on the database which causes it to complain.

Do you have any events on the form that call stored procedures etc? If so, you can often fix it by invoking a saverecord first or refreshing the query after the stored procedure.

 

[cmmrfrds]

I agree with cjowsey, something has changed one of the records you are updating. I had something similar happen on a bit field where it was not getting mapped correctly on Access and ADO thought the field had been changed where it had not. If the recordset does not have a unique key, such as, an unique index then ADO will take all the fields and see if any have been changed in the underlying source. The way to avoid this is to make sure there is something unique in the recordset. If nothing else, add a timestamp field to the underlying table and include it in the View. ADO will know the timestamp is guaranteed to be unique and will use it to update the underlying record.

 

[Ross1]

Thanks 'cmmrfrds' and 'cjowsey'.
That gives me some things to investigate.

Ross