What is the best way to import data from MS Access database to another without changing the access db?

I have a table on access database A (store more 60.000 records), which I must import into a table on another Access database B.

In the beginning, table on Access d开发者_运维问答atabase B stores 3000 records from table on Access database A.

The access database A is handled by another program, and it would be best if I did not have to makes changes to it, I just can open it and get data. I need import updated records and new records from table on the access database A to access database B.

• Every day database A have more 100 new records and some update records, I need to import them into database B in the end of the day. I need do automatically.

• The new records is easy to find but how about update records ? With update record I need to add as a new row in database B, not change any rows in database B.

Is there a better way to do this, perhaps using an ODBC connection or something else?

Please help me ! Thanks and best regards.

Have a look at this microsoft web page

The basic steps are

1. Confirm that the fields and data types in the two tables are compatible e.g. data in fields are transferable;
2. Attach remote table in Database B from Database A;
3. Run an append query to transfer the records;
4. Confirm that it worked e.g. no error messages, correct number of records etc.

1. In database B, Right click->Link tables.
2. Choose your database.
3. Choose your table(s) of interest.

Your database B is now "linked" to the data in database A. Now you can use a "union" query or whatever you like to combine it with data stored in your database B table. Best of all, this doesn't require any changes to database A.

To update existing records, you need to compare the two records and update when they are not the same in the target database.

Depending on the number of fields involved, this can be complicated.

Here's code I've used for that purpose in the past:

  Public Function UpdateTableData(ByVal strSourceTable As String, _
      ByVal strTargetTable As String, ByVal strJoinField As String, _
      ByRef db As DAO.Database, Optional ByVal strExcludeFieldsList As String, _
      Optional strAdditionalCriteria As String) As Boolean
    Dim strUpdate As String
    Dim rsFields As DAO.Recordset
    Dim fld As DAO.Field
    Dim strFieldName As String
    Dim strNZValue As String
    Dim strSet As String
    Dim strWhere As String

    strUpdate = "UPDATE " & strTargetTable & " INNER JOIN " & strSourceTable & " ON " & strTargetTable & "." & strJoinField & " = " & strSourceTable & "." & strJoinField
    ' if the fields don't have the same names in both tables,
    '   create a query that aliases the fields to have the names of the
    '   target table
    ' if the source table is in a different database and you don't
    '   want to create a linked table, create a query and specify
    '   the external database as the source of the table
    ' alternatively, for strTargetTable, supply a SQL string with
    '   the external connect string
    Set rsFields = db.OpenRecordset(strSourceTable)
    For Each fld In rsFields.Fields
      strFieldName = fld.Name
      If strFieldName <> strJoinField Or (InStr(", " & strExcludeFieldsList & ",", strFieldName & ",") <> 0) Then
         Select Case fld.Type
           Case dbText, dbMemo
             strNZValue = "''"
           Case Else
             strNZValue = "0"
         End Select
         strSet = " SET " & strTargetTable & "." & strFieldName & " = varZLSToNull(" & strSourceTable & "." & strFieldName & ")"
         strSet = strSet & ", " & strTargetTable & ".Updated = #" & Date & "#"
         strWhere = " WHERE Nz(" & strTargetTable & "." & strFieldName & ", " & strNZValue & ") <> Nz(" & strSourceTable & "." & strFieldName & ", " & strNZValue & ")"
         If db.TableDefs(strTargetTable).Fields(fld.Name).Required Then
            strWhere = strWhere & " AND " & strSourceTable & "." & strFieldName & " Is Not Null"
         End If
         If Len(strAdditionalCriteria) > 0 Then
            strWhere = strWhere & " AND " & strAdditionalCriteria
         End If
         Debug.Print strUpdate & strSet & strWhere
         Debug.Print SQLRun(strUpdate & strSet & strWhere, dbLocal) & " " & strFieldName & " updated."
      End If
    Next fld
    rsFields.Close
    Set rsFields = Nothing
    UpdateTableData = True
  End Function

You can pass this function two table names, or two query names. This allows lots of flexibility. It assumes the field names are the same in both the objects it is passed, and if they aren't the same name, you can create a query to alias the fields to match those in the other table.

This is a variant of code I've used a bazillion times. The basic principle is that it executes a series of UPDATE queries that go column-by-column through your table and update based on which rows have differing values.