从CSV导入更新现有的访问记录，原生到MS Access或在VB.NET [英] Update Existing Access Records from CSV Import , native to MS Access or in VB.NET

问题描述

我是我组织的票务系统的应用程序管理员。
我们正在添加一个新客户，需要将他们的客户记录导入我们的系统。

但是，我们被拒绝访问，只是抓住这些记录

我们只能输入自己的票务系统，并将现有记录导出为CSV文件。

为了使它与我们的系统一起工作，我把这些CSV文件，并将它们输入到MS Access数据库，我们的系统将从我们的票务系统读取和导入/更新记录。 / p>

但是，我找不到在MS Access数据库中从CSV中的记录更新记录的方法。

我可以将记录导入到表中，但它会跳过任何已经存在的记录（客户数据中包含的字段被用作唯一标识符/主键来区分新/现有记录）。

已经包含此主键的任何记录都将被忽略，它不会更新包含该键的ms访问数据库中的记录。

除了创建基本表和表单之外，我没有太多的MS Access经验;它不在我的正常工作范围。

我需要找到一种方法来获取包含可能已经或可能不在这个ms访问db中的记录的CSV文件，创建新记录（如果不包含在CSV中），或更新记录（如果它们包含在CSV中）。

这不一定要怎么做，这使用vb.net或宏，如果你可以提供一个方法来做到这一点。

我明白这是一个有点偏离，我试图找到办法做我自己，并没有能够提出任何代码测试和发布为起点（我的道歉）。

解决方案

基本上，你必须做一系列的动作查询：append，update和delete，使用过滤器和联接。您可以将查询保存为存储的查询，并通过 DoCmd.OpenQuery 调用它们，或者可以将它们作为字符串写入VBA，以传递到 DoCmd.RunSQL sqlStatement 或 CurrentDb.Excecute sqlStatement

IMPORT / p>

首先，使用 DoCmd.TransferText acImportDelim 将临时表导入CSV。在导入之前，请务必从临时表中清除先前的csv数据。

  DELETE FROM tempTable; 
  

UPDATE

然后，更新temp表和live表之间的现有记录，但更新live表中现有客户的条件。在Access SQL中，联接可用于更新查询，但您可能遇到无法更新的查询：

  UPDATE tempTable INNER JOIN liveTable 
 ON tempTable.CustomerID = liveTable.CustomerID 
 SET liveTable.Col1 = tempTable.Col1，
 liveTable.Col2 = tempTable.Col2，
 liveTable.Col3 = tempTable.Col3，
 ...; 
  

或者，绕过不可更新的查询：

  UPDATE liveTable 
 SET liveTable.Col1 = DLookUp（Col1，tempTable，CustomerID =& liveTable.CustomerID），
 liveTable。 Col2 = DLookUp（Col2，tempTable，CustomerID =& liveTable.CustomerID），
 liveTable.Col3 = DLookUp（Col3，tempTable，CustomerID =& liveTable.CustomerID ），
 ... 
 WHERE CustomerID IN 
（SELECT CustomerID from tempTable）; 
 

完成后，清除刚刚从临时表更新的记录（不与附加步骤和重复条目冲突） ）：

  DELETE FROM tempTable 
 WHERE CustomerID IN 
（SELECT CustomerID from liveTable）; 
  

APPEND

最后，将临时表中的新记录附加到活动表中，但对于不能在活动表中的客户，可以使用 LEFT JOIN ... NULL ：

  INSERT INTO（Col1，Col2，Col3 ...）
 SELECT Col1，Col2，Col3 ... 
 FROM tempTable LEFT JOIN liveTable 
 ON tempTable.CustomerID = liveTable.CustomerID 
 WHERE liveTable.CustomerID Is Null; 
  

I am the application administrator for a ticketing system at my organization. We're adding a new client, and need to import their customer records into our system.

However, we have been denied access to simply grab these records from a direct database connection.

We are limited to entering their ticketing system, and running an export of the existing records to a CSV file.

In order to make this work with our system, I'm taking these CSV's and entering them into an MS Access database, which our system will read and import/update records on our ticketing system from.

However, I cannot find a way to update records in the MS Access db from records in the CSV.

I can import records into a table, but it skips any records that already exist (There is a field included in the customer data that is being used as a unique identifier / primary key to distinguish new / existing records).

Any records which already contain this primary key are simply skipped, it does not update the records in the ms access db containing that key.

I do not have much experience with MS Access other than creating basic tables and forms; it's outside my normal scope of work.

I need to find a way to take a CSV file containing records that may or may not already be in this ms access db, and create new records if not contained within the CSV, or update records if they are contained in the CSV.

It doesn't necessarily matter how this is done, I could implement this using vb.net or a macro, if you could provide a way to do this with either.

I understand that this is a bit off-guidelines, I tried to find ways to do this on my own and haven't been able to come up with any code to test out and post as a starting point (my apologies).

解决方案

Essentially, you will have to do a series of action queries: append, update, and delete that use filters and joins. You can save queries as stored queries and call them by DoCmd.OpenQuery, or you can write them in VBA as strings to be passed into DoCmd.RunSQL sqlStatement or CurrentDb.Excecute sqlStatement

IMPORT

First, import the CSV using DoCmd.TransferText acImportDelim into a temporary table. Be sure to clean out previous csv data from temp table prior to import.

DELETE FROM tempTable;

UPDATE

Then, update existing records between temp and live table but condition for existing customers in live table. In Access SQL, joins can be used in update queries but you may run into not updateable queries:

UPDATE tempTable INNER JOIN liveTable 
ON tempTable.CustomerID = liveTable.CustomerID
SET liveTable.Col1 = tempTable.Col1,
    liveTable.Col2 = tempTable.Col2,
    liveTable.Col3 = tempTable.Col3, 
    ... ;

Alternatively, to bypass non-updateable query:

UPDATE liveTable
SET liveTable.Col1 = DLookUp("Col1", "tempTable", "CustomerID=" & liveTable.CustomerID),
    liveTable.Col2 = DLookUp("Col2", "tempTable", "CustomerID=" & liveTable.CustomerID),
    liveTable.Col3 = DLookUp("Col3", "tempTable", "CustomerID=" & liveTable.CustomerID), 
    ... 
WHERE CustomerID IN 
    (SELECT CustomerID FROM tempTable);

Once done, clean out records just updated from temp table (to not conflict with append step and duplicate entries):

DELETE FROM tempTable 
WHERE CustomerID IN 
      (SELECT CustomerID FROM liveTable);

APPEND

Finally, append new records from temp table into live table but condition for customers NOT in live table which you can do with the LEFT JOIN ... NULL:

INSERT INTO (Col1, Col2, Col3 ...) 
SELECT Col1, Col2, Col3 ... 
FROM tempTable LEFT JOIN liveTable 
ON tempTable.CustomerID = liveTable.CustomerID
WHERE liveTable.CustomerID Is Null;

这篇关于从CSV导入更新现有的访问记录，原生到MS Access或在VB.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！