没有为一个或多个必需参数提供值-将文本导入Access [英] No value given for one or more required parameters - import Text to Access

问题描述

我想将带有此代码的文本文件导入到Access 2007数据库文件中:

I would like to import a text file with this code into an Access 2007 database file:

Dim test As New cAccess
test.Connect()
test.Command.Connection = test.Connection
test.Command.CommandText = 
        "insert into kvks " &
        "select f1 as artnr, f2 as [lfdnr-kal], f3 as saln, f4 as suchbegriff, f5 as wert, f6 as eh " &
        "from [Text;FMT=Delimited;HDR=No;CharacterSet=850;DATABASE=" & txtKvks.Text.Substring(0, txtKvks.Text.LastIndexOf("\")) & "].[" & txtKvks.Text.Substring(txtKvks.Text.LastIndexOf("\") + 1) & "]"
Dim start As DateTime = Now
test.Command.ExecuteNonQuery()

但是我收到错误消息没有为一个或多个必需参数提供值".

But I get the error "No value given for one or more required parameters".

文本文件用分号(;)分隔.起初我以为是因为使用标准定界符逗号，它会将一行识别为一个字段，然后当然会丢失其他字段.

The text file is delimited with semicolon (;). At first I thought, it is because it recognises one line as a field because of the standard delimiter comma, and then of course the another fields are missing.

我也将schema.ini文件插入了存储文件的相同目录，但结果相同.

I inserted a schema.ini file too in the same directory, where the file is stored, but the same result.

可能是什么问题?

推荐答案

此错误的一个可能原因是，使用

One potential cause of this error is that when using a schema.ini file with the Jet/ACE "Text" engine the HDR=No clause in the [Text;...] database specification is ignored.

因此，对于名为[MyExistingTable]的现有表

So, for an existing table named [MyExistingTable]

CREATE TABLE MyExistingTable (
    id INT,
    firstname TEXT(255)
)

名为"toImport.txt"的文本文件

a text file named "toImport.txt"

1;Larry
2;Hank
3;Artie

和一个"schema.ini"文件

and a "schema.ini" file

[toImport.txt]
Format=Delimited(;)

命令

cmd.CommandText =
        "INSERT INTO MyExistingTable " &
        "SELECT F1 AS id, F2 AS firstname " &
        "FROM [Text;FMT=Delimited;HDR=No;CharacterSet=850;DATABASE=C:\Users\Public].[toImport.txt];"

将失败，因为它将"toImport.txt"的第一行解释为列名:[1]和[Larry]，而不是在不包含任何列名的情况下分配的默认[F1]和[F2]

will fail because it will interpret the first line of "toImport.txt" as column names: [1] and [Larry], instead of the default [F1] and [F2] that are assigned when no column names are included.

最简单的解决方法是将ColNameHeader=False添加到"schema.ini"文件中

The easiest fix is to add ColNameHeader=False to the "schema.ini" file

[toImport.txt]
Format=Delimited(;)
ColNameHeader=False

您还可以变得更加复杂，并将完整的列规格添加到"schema.ini"文件中(请参阅

You can also get more sophisticated and add complete column specifications into the "schema.ini" file (see the documentation for details).

另一个可能的原因:

该错误也可能是由使用UTF-8 BOM(字节顺序标记)保存的"schema.ini"文件引起的. Jet文本"引擎比Unicode早，并且仅支持两个字符集. "ANSI"或"OEM"– 分别用于数据文件和"schema.ini"文件.

The error can also be caused by a "schema.ini" file that is saved with a UTF-8 BOM (byte order mark). The Jet "Text" engine pre-dates Unicode and only supports two character sets – "ANSI" or "OEM" – for both the data file and "schema.ini" file.

这篇关于没有为一个或多个必需参数提供值-将文本导入Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！