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
LVL 49
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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.

Fabrice LambertFabrice LambertCommented:

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.
AkhaterAuthor Commented:
@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)?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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.
AkhaterAuthor Commented:
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
There is no tutorial because this is very uncommon.  It is also completely unique for each update.  Here's an example of one I did a few years ago. Use the shift key when you open the database to bypass the opening form.  Then open that form in design view and look at the button code which runs all the  DDL (Data Definition Language) queries and other code that does the updates.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AkhaterAuthor Commented:
@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 ?

Fabrice LambertFabrice LambertCommented:
Here is a function you can use:
Public Sub patchDb(ByVal strSQL As String, ByVal path As String)
On Error GoTo Error
    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Set ws = DBEngine.Workspaces(1)
    Set db = ws.OpenDatabase(path)
    db.Execute strSQL, dbFailOnError
    Set db = Nothing
    Set ws = Nothing
    If Not (db Is Nothing) Then
    Set db = Nothing
    If Not (ws Is Nothing) Then
        Set ws = Nothing
    End If
    err.Raise err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext
End Sub

Open in new window

Call sample:
Public Sub runPatch()
    Dim path As String
    Dim strSQL As String
    path = "c:\****\***.accdb"
    strSQL = vbNullString
    strSQL = "ALTER TABLE ......"
    patchDb strSQL, path
End Sub

Open in new window

To answer your 2nd question:
The simple way to have a "visual interface" running queries is to create database and link it to the back-end. Wich is more or less like openning the back-end directly since you'll be able to see its datas.
So no point doing that.
You're welcome.  The point of creating a process like this is so that it can be run by a user who has no knowledge of the underlying technology.  Access doesn't have the kind of Macro recorder that Excel has so, NO, things you do via the interface cannot be "tracked" whatever that means

I didn't look closely at the file I posted.  I think it only runs DDL queries to modify the table structures.  In other situations, I might add new  tables and populate them.  I decided at the beginning that I would not ever delete columns or tables.  If I didn't need them anymore, the app would simply stop using them.   So, far the schema has been solid enough that in 12 years, I have never had to restructure anything.  Basically, I add new columns and new tables to support new features.
AkhaterAuthor Commented:
Thank you guys, it worked like a charm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.