[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

access tables

Posted on 2014-02-16
7
Medium Priority
?
359 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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

Accepted Solution

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

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

649 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