Updating linked tables 1

[jorge79700]

I have a MS Access Frontend, MSSQL backend with about 10 users. Most of the data is entered into one form which is based on a select query which is based on a couple of linked tables from the MSSQL DB. When new records are entered or existing updated that information is not visible to others until the user exits the frontend Access DB. Is there any method to force Access to update this information on MSSQL when the affected record is unloaded?

I've searched the forums and can't seem to find a solution.

Thanks in advance for your response.

 

[JerryKlmns]

The recordset has a CursorType which should be adOpenDynamic and a CursorLocation of adUseServer for what you say. Also play with the values on your access db Tools-->Options-->Advanced

 

[jorge79700]

Jerry, thanks for your help. Can you give me a little more information on how to confirm that the record set is adOPenDynamic? I assume that that is written in either the connection string or somewhere in sql.

Thanks

 

[JerryKlmns]

You define these for the recordset before opening it...

Dim rstTemp As ADODB.Recordset

Set rstTemp = New ADODB.Recordset
With rstTemp
   .ActiveConnection = YourOpenedConnection
   .CursorLocation = adUseServer
   .CursorType = adOpenDynamic
   .LockType = adLockOptimistic
   .Source = "Select * From YourTableName;"
   .Open
End With

 

[jorge79700]

Jerry:
Thanks for all of your help. It turns out that all I really needed was for the switchboard to refresh every 60 seconds or so. I found information regarding this at
"

http://www.pcreview.co.uk/forums/thread-1111911.php"

(The link above does not work. Cut and past to a browser)
What I did was this:
I opened the switchboard in design view and opened properties. I set the timer interval to 60000 (1 minute). In the on timer field I set it to event procedure then code builder. This initiated a subroutine in which I inserted the code "me.Refresh". My switchboard has fields based on queries which count the records by day. It appears that the records are being written to the MSSQL server as soon as the form is completed and either closed or moved to another record on the form.
I very much appreciate your Help.

 

[JerryKlmns]

The pleasure is mine, jorge79700.