Solved

access tables

Posted on 2014-02-16
7
352 Views
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?

Thanks
0
Comment
Question by:CoolDev2014
7 Comments
 
LVL 8

Expert Comment

by:TheNautican
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.

Regards,
-Naut
0
 

Author Comment

by:CoolDev2014
ID: 39863713
i know but how to link?
0
 
LVL 1

Expert Comment

by:samjomoore
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
        Else
            tdfCurrent.Connect = ";DATABASE=" & TargetDatabaseName
            tdfCurrent.RefreshLink
        End If
        LinkTable = True
   
Exit_LinkTable:
    Exit Function

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

End Function
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:CoolDev2014
ID: 39863978
any ways to do without codes?
0
 
LVL 1

Expert Comment

by:samjomoore
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!
0
 
LVL 65

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.
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 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