Solved

access tables

Posted on 2014-02-16
7
347 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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 34

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
I have a large data set and a SSIS package. How can I load this file in multi threading?
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now