Link to home
Start Free TrialLog in
Avatar of Akhater
AkhaterFlag for Lebanon

asked on

How to update Access database without deleting the data?

Hi, I am building a small access database app that will used by multiple members of my family. but each will have it's own file (think budget file)
However, as time pass, I will update the database (more tables, changes in the structure, create new forms etc...)
how to handle these updates in a way that each of us retains his/her own data inside the database but we all use the same "schema" ?

Thank you
Avatar of Bill Prew
Bill Prew

One approach would be to use the same tables, but have a "FamilyMember" column that has the name (or id) of each family member for any of the data that is associated with them.  Then you can work with just their data by including this in WHERE clauses, or aggregate data easily across multiple members.


»bp
Hi,

You have 2 concerns to deal with these updates:
- Update the GUI (forms / VBA code / queries ect ...)
In this case, it is best to break your database into 2 parts:
Back-end (wich hold only datas)  and Front-end (wich hold everthing else), and connect the front-end database to the back-end.
This way, you can safely overwrite the front-end without touching the back-end.

- As far as data-structure goes, you can write a "patch" (in fact another DB application) wich will execute update / create table / alter table queries against the back-end.
Avatar of Akhater

ASKER

@Bill thank you for the suggestion but I don't think this would work, I don't see myself giving access to my son on my budget or vice-versa

@Fabrice yes this is exactly what I need, would you have any kind of tutorial showing how to do it (specially the "patch" part)?
I also think that  using a common BE is the optimal solution.  You can provide a log on form that controls what data each person can see.  You would split the database into FE and BE regardless of whether you use a common BE or individual BE's.  With the common BE, you have less work to do since you would have only one BE to modify if you want to add enhancements.  To update the FE, you would give each user an updated copy.

All of this is easier if everyone is on the same network and you can use security features of the network to bock direct access to the shared BE so that every one (except you) must use their FE to access their own data.  This is how a professional would set up the app to allow for privacy but simplify BE and FE maintenance.

I use a batch file to distribute new copies of the FE.  Each user has a shortcut that runs the batch file which sits on the server.  The batch file copies the master FE from the server to a designated local directory on each user's PC.  So to distribute a new version you replace the master version on the server and tell everyone to shut down and reopen.

If you are not on the same LAN, the distribution is more of a problem.  You'll have to do it manually or via email and trust each individual to replace his old FE.  But the real problem occurs when you have to update the BE.  In this case,, you would either have to do each one manually or you would have to create an "update" database that when opened will apply changes to the individual BE.  This can be complicated and you have to use versions to ensure that the updates are taking BE ver 1 to BE ver 2.  You don't want them to accidentally run  next year and try to change BE ver 5 back to 2.  I has software that is sold to the public and since I do not have access to their computer to make the modifications to the BE, I have to distribute updates this way.

Since we don't know enough about your environment, it is hard to give you a firm direction.
Avatar of Akhater

ASKER

Thank you for your reply,

No we are not in the same LAN, my sisters (for instance) live in their own houses so I am in the last case where I need to update the BE via "update database" I don't mind using versions since I already do. however if you could point me to any kind of tutorial on how to create this "update database" it would really be helpful

thanks again
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Akhater

ASKER

@Pat this is genius thank you so much ....

do u actually do all the updates via queries or there is way to do it in the visual interface and track the SQL statements that are ran in the background ?

Cheers
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Akhater

ASKER

Thank you guys, it worked like a charm