access tables

Posted on 2014-02-16
Medium Priority
Last Modified: 2014-03-04
I have two access db. called access db1 and access db2. and They are the same design.
what I want to do is if db1.table1 data changes, I want db2.table1 data changes as well.

How can I do that? Or is it possible to do that?

Question by:CoolDev2014

Expert Comment

ID: 39863645
I have had something similar in the past. What i did was have a "main" database and a "user" database. The user database just linked to the tables from the main database.


Author Comment

ID: 39863713
i know but how to link?

Expert Comment

ID: 39863760
Function LinkTable(TargetDatabaseName As String, TableName As String) As Boolean
    Dim dbLocal As Database
    Dim tdfCurrent As TableDef
    Dim flgAddTable As Boolean
        '-- Attempt to open the current link
        On Error Resume Next
        Set dbLocal = CurrentDb
        Set tdfCurrent = dbLocal.TableDefs(TableName)
        flgAddTable = Err.Number
        On Error GoTo Err_LinkTable
        '-- If there was an error, create the link from scratch,
        '-- otherwise, just update the connect string
        If flgAddTable Then
            Set tdfCurrent = dbLocal.CreateTableDef(TableName)
            tdfCurrent.SourceTableName = TableName
            tdfCurrent.Connect = ";DATABASE=" & TargetDatabaseName
            CurrentDb.TableDefs.Append tdfCurrent
            tdfCurrent.Connect = ";DATABASE=" & TargetDatabaseName
        End If
        LinkTable = True
    Exit Function

    MsgBox "ERROR: " & Err.Number & " - " & Err.Description
    LinkTable = False
    Resume Exit_LinkTable

End Function
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.


Author Comment

ID: 39863978
any ways to do without codes?

Expert Comment

ID: 39863995
Yes.   First, you must delete the tables that contain data on access db2.  Then (if you are using access 2010), select the Access option from the External Data tab on the ribbon menu.  Here use the Browse button to find the access db1 (that contains the tables you want to link).  Select the second option (link to the data source), and click OK.  A new dialog will open showing all the tables in that database.  Select the table that you want (the one you deleted from access db2) and click OK.

This will create a link to the table in access db1, from the access db2.  The data will only be in one place (access db1) but you will be able to view and modify this data from either database.

Hope this helps.

Oh, yes.  Make sure you have backups of both databases before you do anything!
LVL 66

Expert Comment

by:Jim Horn
ID: 39864720
Couple of additions to the above correct answer:

*  Make sure both Access databases can refer to each other with a network path, otherwise you'll not be able to link.

*  Might not be a bad idea to eyeball the data in both tables and makes sure they are the same, before you decide which db.table is the 'master', and which one is the 'child linked table', just to make sure you don't lose any rows.
LVL 41

Accepted Solution

PatHartman earned 2000 total points
ID: 39864872
You actually need to go a step further.  Figure out which database is the "master".  Then use the split utility to split the database into FE (Front End - forms/reports/queries/macros/code) and BE (tables only).

The BE needs to be saved to a network share where both parties have access to it.  
Then give each user his own personal copy of the FE.

You will need to add a relink function to allow the users to "find" the BE because moving the BE after you did the split will have broken the links and you need to relink.  You could teach the users to use the Linked Tables Manager or build a form yourself or use a commonly available solution such as the JStreet linker.  Also, if the users don't have the drive mapped the same way, one or the other will have trouble.  The easiest solution for this is to use UNC for the link.  That way it won't matter if user A has the server mapped as the F: drive and user B has it mapped as the M: drive.

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.

Join & Write a Comment

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question