csv - 使用 CSV 文件自动更新 Access 表

Question

问题背景:

我有一个 Powershell 脚本，我可以从我的 Microsoft Access 表单执行该脚本，该脚本扫描包含不同设施信息的文件夹，并生成类似于以下内容的 CSV:

SiteCode    FacilityNumber  DocumentType    HyperlinkPath
DKFZ        10              DD1400          C:\FACILITIES DATABASE\path
DKFZ        10              FLRPLN          C:\FACILITIES DATABASE\path
SMQL        17              P1              C:\FACILITIES DATABASE\path
SMQL        17              P2              C:\FACILITIES DATABASE\path

这样每次新文件添加到这些文件夹时，我都可以运行这个脚本并生成我拥有的所有内容的更新列表:

C:\...\Output\scanResults.csv

我现在需要做的就是获取该 CSV 文件并更新(甚至覆盖)我在 Access 数据库中的一个表，该表与其他表有关系，并被数据库中的各种查询和表单使用。 CSV 列已按照与 Access 表相同的方式命名和格式化。

我查看并尝试复制以下线程:

VBA procedure to import csv file into access

Access Data Project Importing CSV File In VBA

VBA Import CSV file

我找到的最接近的答案是:

Sub Import()
   Dim conn as new ADODB.Connection
   Dim rs as new ADODB.Recordset
   Dim f as ADODB.field

   conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\temp;"
   rs.Open "SELECT * FROM [test.txt]", conn, adOpenStatic, adLockReadOnly, adCmdText

   While Not rs.EOF
      For Each f In rs.Fields
         Debug.Print f.name & "=" & f.Value
      Next
   Wend
End Sub

但这显然不会将数据写入表中，我无法理解作者将Select更改为Insert的意思。

我还发现:

DoCmd.TransferText acImportDelim, "YourCustomSpecificationName", _
    "tblImport", "C:\SomeFolder\DataFile.csv", False

因为这两个都是 2010 年的，我想知道在 Access 2013 中是否有更好的方法来完成这个。虽然我可以手动完成这一切，但我想将它合并到我用来的 VBA 代码中告诉 Powershell 生成 CSV，这样我就可以生成 CSV，然后立即上传。

非常感谢任何帮助或建议。总的来说，我对 Access、VBA 和 SQL 语句还很陌生，所以这在很大程度上是一个“边学边学”的过程。

Answer 1

我更喜欢使用 SQL 子句和查询来导入此类数据。详细信息取决于您的确切配置，但它往往看起来像这样:

SELECT *
INTO MyTable
FROM [Text;FMT=CSVDelimited;HDR=No;DATABASE=C:\...\Output].[scanResults#csv]

或者将信息附加到表中:

INSERT INTO MyTable
(SiteCode,    FacilityNumber,  DocumentType,    HyperlinkPath)
SELECT *
FROM [Text;FMT=CSVDelimited;HDR=No;DATABASE=C:\...\Output].[scanResults#csv]

这允许您在导入之前进行检查(使用 WHERE 子句)，仅导入特定值，并允许您在不使用外部文件的情况下进行大量自定义。

DATABASE= 后跟您的文件夹名称(如果其中有需要转义的字符，请使用 {})，然后是您的文件名和 . 替换为 #。

您可以通过将其保存为查询或将其用作 VBA 或宏中的字符串来执行它。请注意，我很少推荐宏，但您可以使用计划任务执行它们并在导入后关闭 Access。

要备份和恢复更新前后的关系，可以使用以下函数:

Public Function DeleteRelationsGiveBackup(strTablename As String) As Collection
    Dim ReturnCollection As Collection
    Set ReturnCollection = New Collection
    Dim i As Integer
    Dim o As Integer
    Do While i <= (CurrentDb.Relations.Count - 1)
        Select Case strTablename 
            Case Is = CurrentDb.Relations(i).Table
                ReturnCollection.Add DuplicateRelation(CurrentDb.Relations(i))
                o = o + 1
                CurrentDb.Relations.Delete CurrentDb.Relations(i).NAME
            Case Is = CurrentDb.Relations(i).ForeignTable
                ReturnCollection.Add DuplicateRelation(CurrentDb.Relations(i))
                o = o + 1
                CurrentDb.Relations.Delete CurrentDb.Relations(i).NAME
            Case Else
                i = i + 1
        End Select
    Loop
    Set DeleteRelationsGiveBackup = ReturnCollection
End Function

Public Sub RestoreRelationBackup(collRelationBackup As Collection)
    Dim relBackup As Variant
    If collRelationBackup.Count = 0 Then Exit Sub
    For Each relBackup In collRelationBackup
        CurrentDb.Relations.Append relBackup
    Next relBackup
End Sub

Public Function DuplicateRelation(SourceRelation As Relation) As Relation
    Set DuplicateRelation = CurrentDb.CreateRelation(SourceRelation.NAME, SourceRelation.Table, SourceRelation.ForeignTable)
    DuplicateRelation.Attributes = SourceRelation.Attributes
    Dim i As Integer
    Dim fldLoop As Field
    Do While i < SourceRelation.Fields.Count
        Set fldLoop = DuplicateRelation.CreateField(SourceRelation.Fields(i).NAME)
        fldLoop.ForeignName = SourceRelation.Fields(i).ForeignName
        DuplicateRelation.Fields.Append fldLoop
        i = i + 1
    Loop
End Function

然后，在导入时:

Dim colRelBackup As Collection
Set colRelBackup = DeleteRelationsGiveBackup("MyTable")
'Delete MyTable
'Import new version
RestoreRelationBackup colRelBackup 

(请注意，代码很长，是为几年前的项目开发的，并没有经过广泛测试。如果字段名称/类型与导入前的情况不完全，则恢复备份可能会失败，并且关系将永久丢失)。