separating code from data in access

Question

I am extending an access application at the same time that some users are using it. This means that users are changing databases. I created a backup of access file and working on it. In this way I can make sure that I am not making any changes to system that change database when I am testing the system. But I only can develop when users are not using the system and hence they are not changing databases (data in tables). This is not good since I cannot work at the same time that users are working.

Is there any solution for it?

A good way to do this development is to separate VBA codes from Tables. What I mean is to have an access file which contains VBA codes and the other access file which contains tables.

Is it possible?

How can I do this?

Is there any documentation on how to do this? what is the best practise on this?

EDIT:

Good information presented in link in first comment to this question.

Answer 1

You need to split up the database into two databases: a frontend and a backend. Keep the backend at a central location and change the data structures there. Distribute a copy of the frontend to each user and let them run queries, reports, forms etc. on their own copies. Give them a consistent set of base queries in the frontend so that they never actually need to use the actual tables--which you can then change freely.

You can find a walk-through tutorial on database splitting at Microsoft's Office website. But the basic idea is that the frontend contains linked tables that point to actual tables in the backend. You can also do this manually using the Access data import wizard and choosing to link to a table instead of importing its data. Just be careful to ensure that the linked tables point to the database in the shared folder and not to your personal folder. You can do this by right-clicking a linked table, selecting Linked Table Manager, and updating the links for all tables where necessary.