Solved

access tables

Posted on 2014-02-16
7
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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 37

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Viewers will learn how the fundamental information of how to create a table.

734 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