Copy Row to same Table in new Database

问题:

I have very little experience with Access Databases however I have wrote a similar VBA macro in excel. I am trying to copy rows from one .mdb file into the exact same table on a different .mdb file. However I would like it to only import it if it does not already exsist. Could someone please advise me the best way to go about this and maybe some code I can use and modify? I have already looked around stack overflow and can't seem to find any examples that work.

There are 8 different tables and inside these a few hundred rows. with maybe 5-20 columns.

If the script could be made inside VBS this would be ideal, as it would allow me to run the updates without loading into access.

Thanks for any help or advice, Simon

EDIT -

Zev's answer seems to do the job however I am getting this error, also the MDB inside site2 is the one I am copying from and putting it into site1

Error: Expected end of statement
Code: 800A0401
Line: 17
Char: 13

Code (saved as "update.vbs"):

Dim eng
Set eng = CreateObject("DAO.DBEngine.120")
Set dest = eng.OpenDatabase("C:\Users\simon\Documents\garden games redesign\import script\Site1\ActinicCatalog.mdb")

Sub CopyTable()
    Dim rs
    Set rs = dest.OpenRecordset("Person")

    Dim sWhere
    For Each fld In rs.Fields
        sWhere = sWhere & " AND " & fld.Name & " <> t1." & fld.Name
    Next
    sWhere = Mid(sWhere, 6)

    Dim sql: sql= _
        "INSERT INTO Person " & _
        "SELECT * " & _
        "FROM Person AS t1 IN ""C:\Users\simon\Documents\garden games redesign\import script\Site2\ActinicCatalog.mdb"" " & _
        "WHERE " & sWhere
    dest.Execute(sql)
End Sub

Edit for more info:

\Site1\ActinicCatalog.mdb - is destination database \Site2\ActinicCatalog.mdb - is original database

These databases have about 20 columns

回答1:

Here is an example to get you started. It copies the content of [Table1] of the current database to [Table1] of a second database.

Option Compare Database
Option Explicit

Sub copyTables()

    'Open source database
    Dim dSource As Database
    Set dSource = CurrentDb

    'Open dest database
    Dim dDest As Database
    Set dDest = DAO.OpenDatabase("C:\Users\Admin\Desktop\DBdest.accdb")

    'Open source recordset
    Dim rSource As Recordset
    Set rSource = dSource.OpenRecordset("Table1", dbOpenForwardOnly)

    'Open dest recordset
    Dim rDest As Recordset
    Set rDest = dDest.OpenRecordset("Table1", dbOpenDynaset)

    'Loop through source recordset
    While Not rSource.EOF

        'Look for record in dest recordset
        rDest.FindFirst _
            "Field1 = '" & rSource.Fields("Field1") & "' AND " & _
            "Field2 = " & rSource.Fields("Field2")

        'If not found, copy record - Field1 is text / Field2 is numeric
        If rDest.NoMatch Then
            rDest.AddNew
            rDest.Fields("Field1") = rSource.Fields("Field1")
            rDest.Fields("Field2") = rSource.Fields("Field2")
            rDest.Update
        End If

        'Next source record
        rSource.MoveNext
    Wend

    'Close dest recordset
    rDest.Close
    Set rDest = Nothing

    'Close source recordset
    rSource.Close
    Set rSource = Nothing

    'Close dest database
    dDest.Close
    Set dDest = Nothing

    'Close source database
    dSource.Close
    Set dSource = Nothing
End Sub

回答2:

I would suggest using an SQL statement if possible. From VBScript using DAO/ACE:

Dim eng
Set eng = CreateObject("DAO.DBEngine.120")
Set dest = eng.OpenDatabase("path\to\destination\database.accdb")

Using ADO:

Dim conn
Set conn = CreateObject("ADODB.Connection")
With conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=""path\to\destination\database.accdb"";"
    .Open
End With

The SQL statement would be something like this:

INSERT INTO Table1
SELECT *
FROM Table1 AS t1 IN "path\to\source\database.accdb"
WHERE Table1.Field1 <> t1.Field1

and executed like this:

Dim sql = _
    "INSERT INTO Table1 " & _
    "SELECT * " & _
    "FROM Table1 AS t1 IN "path\to\source\database.accdb" " & _
    "WHERE Table1.Field1 <> t1.Field1"

'Using DAO or ADO
dest.Execute sql

Considering that each table has a variable number of columns, you might have to generate the WHERE expression dynamically:

Sub CopyTable(tablename)
    Dim rs
    Set rs = dest.OpenRecordset(tablename)
    'if using ADO:
    'Set rs = conn.Execute(tablename)

    Dim sWhere
    For Each fld In rs.Fields
        sWhere = sWhere & " AND " & fld.Name & " <> t1." & fld.Name
    Next
    sWhere = Mid(sWhere, 6)

    Dim sql
    sql = _
        "INSERT INTO " & tablename & " " & _
        "SELECT * " & _
        "FROM " & tablename & " AS t1 IN ""path\to\source\database.accdb"" " & _
        "WHERE " & sWhere
    dest.Execute(sql)
End Sub

Update
If you are only using one column to determine whether the record exists, the SQL statement should look like this:

INSERT INTO Table1
SELECT *
FROM Table1 AS t1 IN "path\to\source\database.accdb"
LEFT JOIN Table1 ON t1.FirstField = Table1.FirstField
WHERE Table1.FirstField IS NULL

and CopyTable like this:

Sub CopyTable(tablename)
    Dim rs
    Set rs = dest.OpenRecordset(tablename)
    'if using ADO:
    'Set rs = conn.Execute(tablename)

    Dim field0Name
    field0Name=rs.Fields(0).Name

    Dim sql
    sql = _
        "INSERT INTO " & tablename & " " & _
        "SELECT * " & _
        "FROM " & tablename & " AS t1 IN ""path\to\source\database.accdb"" " & _
        "LEFT JOIN " & tablename & " ON t1." & field0Name & "=" & tablename & "." & field0Name & " " & _
        "WHERE " & tablename & "." & field0Name & " IS NULL"
    dest.Execute(sql)
End Sub