Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012

    Unanswered: Appending records from table in another database


    I recently started using access database. I have created a database which have few forms and tables. This database will be used in field (at different locations) to collect the data. After certain period of time I need to combine data from all these different locations and put in one single 'Master' database. This process will be done on routine basis.

    I want to create a form in this Master database where user can browse and select these other (field ) databases and import into master database. As the process will be repeated I want to avoid copying duplicate records which already exist in master database.

    I know very little about vb/sql coding , however I have managed to create a list box and button which can be used to browse and select the file. After selecting the file, the path is stored in list box.

    Here is the code :

    Private Sub Command2_Click()

    Dim fDialog As Office.FileDialog
    Dim varFile As Variant

    ' Clear listbox contents. '
    Me.List3.RowSource = ""

    ' Set up the File Dialog. '
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

    With fDialog

    ' Allow user to make multiple selections in dialog box '
    .AllowMultiSelect = False

    ' Set the title of the dialog box. '
    .Title = "Please select one or more files"

    ' Clear out the current filters, and add our own.'
    .Filters.Add "Access Databases", "*.ACCDB"
    .Filters.Add "All Files", "*.*"

    ' Show the dialog box. If the .Show method returns True, the '
    ' user picked at least one file. If the .Show method returns '
    ' False, the user clicked Cancel. '
    If .Show = True Then

    'Loop through each file selected and add it to our list box. '
    For Each varFile In .SelectedItems
    Me.List3.AddItem varFile

    MsgBox "You clicked Cancel in the file dialog box."
    End If
    End With
    End Sub

    I tried searching really hard on the internet but I was unable to find how to write code to import specific table from that selected database to master database. I would appreciate if some of experts would give me a direction about how to go from here...

    Thanks in advance..

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    You can specify an external database in the SELECT part of an append query:
    INSERT INTO Tbl_Databases (Server, DbName, Owner)
    SELECT Server, DbName, Owner 
    FROM Tbl_Databases IN 'C:\Documents and Settings\Sinndho\My documents\Access\SandBox64.mdb'
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts