Auto Update MS Access fields

I am trying to update a MS Access table based on a table in a MS SQL Server:

Example: Persons 01 to 10 and Rooms A to J

The Table on the server gets updated periodically and I want my Access table to reflect that.

Example:  I have an Access table with Rooms A-J in a column, when Person 05  moves from Room A to Room E; I want my table to reflect that.

Is this possible?  If so, what is the easiest way?  Thanks.

Who is Participating?
Either simply link your Access database to the SQL Server database so that you are actually looking at the SQL Server table, or link to the SQL Server table and run an UPDATE query periodically to update your Access table.

The best way to do the periodic update is to use the On_Timer event in a form that is always open in your Access application.  Set the Timer_Interval to a suitable amount (remember it's in milliseconds, so it might be a very large number).  In the event, add code something like:
DoCmd.RunSQL "UPDATE MyLocalTable SET Room = MySQLServerTable.Room WHERE MySQLServerTable.Person = MyLocalTable.Person"

Open in new window

or something like it.
Did that help any?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.