Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm conducting a study, where we will be collecting data using Access. Since there will be multiple persons conducting these studies using the same structured Access DB we need to figure a way to update/synchronize to a central Database with the information collected from all other databases. In other words, the data that is collected by the multiple persons needs to be collected into one central area. How do I do this?

In essence this is exactly what I want to do but don't know how:

Indirect synchronization

Indirect synchronization is useful when you work in a disconnected environment, such as when you are traveling with a laptop.

Indirect synchronization can only be configured by using Replication Manager. Once indirect synchronization is configured with Replication Manager, you can execute an indirect synchronization by using Microsoft Access, Replication Manager, or JRO.

site

share|improve this question
    
Have you considered Google Docs and creating a single DB that can be accessed via any modern browser? –  Darius Jun 2 '11 at 22:15
    
@Darius Unfortunately gDocs isn't the answer here as it's not as powerful as Microsoft Access. Besides I don't think that gdocs has a database alternative –  KronoS Jun 2 '11 at 22:16
    
a@KronoS - Will the Access DBs run on LAN? There is no nice way of having it work over the WAN, it will most likely blow up on you. –  Darius Jun 2 '11 at 22:20
1  
What I am saying that major corporations don't use Access over the WAN –  Darius Jun 2 '11 at 22:23
5  
Developing custom applications with MS Access CAN BE AND USUALLY IS SIGNIFICANTLY MORE EXPENSIVE than developing business applications with MSDE or MS SQL Server. IT tends to spend more time, more effort, and run into more headaches with MS Access. MS Access is significantly more difficult to deploy and control as a distributed business application without driving up IT support costs. And even if you have invested the extra time and effort to overcome the limitations, the MS Access platform does not scale. –  Darius Jun 2 '11 at 22:27

4 Answers 4

The most reliable way I know of to do this is to write a program that works with the database off a fileserver. I've had to work on a program (from scratch) to do this for a client that wanted to use MS Access, nothing else. Unfortunately, it's too tricky to explain here. If you can just access the Access database file from multiple places, the MS Access software should be able to make changes but I don't know if those changes would propagate through other systems, or how reliable it would be.

share|improve this answer

See this article : Merge Multiple MS Access Databases into a single one :

Find below a Merge() function written in MS Access VBA for consolidating multiple typically structured databases into a single one. I had to write this function for one of my project where I needed to consolidate many island databases. The structure of the databases were exact same.

You will receive warnings when data can not be inserted as they will create duplicate records. In such situation simply ignore the warning message and proceed.

The script itself just loops on executing commands that look like:

INSERT INTO target SELECT * FROM source

Since the original webserver seems to be broken, the entire script is retrieved from the Wayback Machine and reproduced here :

Option Compare Database
Option Explicit

'Set the below variable to TRUE
'When you are done with testing
'Basically testing mode will print
'the names of all the databases
'you are going to merge
Const bTest = False

Sub Merge()

    'Copyright © iLoveCoding, 2010
    'Web: http://www.iLoveCoding.co.uk

    'The Merege() is a function that merges
    'records from multiple MS Access databases
    'into a consolidated one provided that the
    'structure of all databases are exactly same.
    '
    'This function assumes that there are
    'no relationships defined among the tables.
    'Which is typically the scenario when an
    'MS Access database is used in an Intranet or
    'Web application.

    'However properly altering the order of the
    'dbfiles() initialization one can easily
    'address the issue of relationships and
    'data integrity among tables

    On Error GoTo errhand
    Dim appPath$
    '----------------------------------------------
    'Change the below number to number of databases
    'you are going to merge
    '----------------------------------------------
    Const ndb = 22
    Dim dbfiles$(2 To ndb)
    Dim i%
    'ANA.mdb
    '----------------------------------------------
    'Array of the database file names without path
    'Copy this code into a module of the first database
    'The first database is going to be the consolidated
    'capturing the records from all other databases.
    'The first database name is not present here
    'That is why the index starts with 2.
    '----------------------------------------------
    dbfiles(2) = "second.mdb" '<= change the file name
    dbfiles(3) = "third.mdb"
    dbfiles(4) = "fourth.mdb"
    '
    'similarly initialize other files
    '
    dbfiles(10) = "tenth.mdb"
    '----------------------------------------------
    ' The databases should be copied to the same
    ' folder of the first database
    '----------------------------------------------
    appPath = CurrentProject.Path
    For i = 2 To ndb
        Dim dbpath$, db As Database
        dbpath = appPath & "\" & dbfiles(i)
        Set db = OpenDatabase(dbpath)
        Dim tbl As TableDef, j%
        For j = 0 To db.TableDefs.Count - 1
            DoEvents
            Set tbl = db.TableDefs(j)
            If tbl.Attributes = 0 Then
                If bTest Then
                    Debug.Print tbl.Name
                Else
                    DoCmd.TransferDatabase acLink, "Microsoft Access", _
                    dbpath, acTable, tbl.Name, tbl.Name & "_Linked", False
                    Dim sql$
                    sql = "INSERT INTO [" & tbl.Name & "] SELECT * FROM [" & _
                    tbl.Name & "_Linked" & "]"
                    DoCmd.RunSQL sql
                    DoCmd.DeleteObject acTable, tbl.Name & "_Linked"
                End If
            End If
        Next j
        Debug.Print dbfiles(i)
    Next i
    Exit Sub
errhand:
    MsgBox Err.Description
End Sub
share|improve this answer
    
link appears to be broken –  KronoS Jun 14 '11 at 21:03
    
Found on the Wayback Machine and copied above. –  harrymc Jun 15 '11 at 5:43
    
The code doesn't seem to work :( I keep getting a syntax error on line 64: "dppath = appPath &amp; "\" &amp; dbfiles(i)"... as I know nothing about VBA I'm not sure what the prob is exactly. –  KronoS Jun 15 '11 at 19:37
    
The code was reproduced incorrectly, maybe even since the original posting. "&amp;" is the html encoding of "&" (ampersand). I have fixed the code and hope it will work now. –  harrymc Jun 15 '11 at 21:33
    
Still no luck. The script is running correctly, however when it goes to append the values it gives an error. –  KronoS Jun 16 '11 at 0:50

Something similar has been discussed on StackOverflow, maybe it can give you some pointers on how to continue.

Another option which might be above your budget, could be Altova DatabaseSpy, it can import CSV/XML data into an Access database.

share|improve this answer

I'm not sure what type of UI you're using for data input, but I'd suggest NOT using access and instead using SQL Express, and perhaps doing the data input as a .Net application.

You can then run SQL Server and merge the individual express databases back into the master. Take a look at http://msdn.microsoft.com/en-us/library/ms152746.aspx

You don't have details of the data you're collecting, but it sounds like there wouldn't be any overlap - i.e. each collector has a unique data set, without any possibility of merge conflicts - so it should go fairly easily.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.