复制行，以相同的表在新的数据库 [英] Copy Row to same Table in new Database

问题描述

我有Access数据库很少的经验，但我已经写了一个类似的VBA宏在Excel中。我想从一个.mdb文件复制到行完全相同的表在不同的.mdb文件。不过，我想它只是导入它，如果它已经不exsist。可能有人请告诉我最好的方式去了解这一点，也许有些code，我可以使用和修改？我已经看了看四周堆栈溢出，并似乎无法找到任何工作的例子。

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.

有8种不同的表和里这几百行。有可能5-20列。

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

如果该脚本可以VBS内进行，这将是理想的，因为这将让我没有加载到访问运行该更新。

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

编辑 -

泽夫的答案似乎做的工作，但是我正在此错误，也是在MDB内部站点2是一个我抄袭，并把它变成SITE1

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（保存为update.vbs）：

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:

\站点1 \ ActinicCatalog.mdb - 是目标数据库 \站点2 \ ActinicCatalog.mdb - 为原始数据库

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

这些数据库有大约20个栏目

These databases have about 20 columns

推荐答案

下面是一个例子，让你开始。它复制第二个数据库的【表1】的内容在当前数据库为[表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

这篇关于复制行，以相同的表在新的数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！