ado在excel中，将记录插入到Access数据库中 [英] ado in excel, inserting records into access database

问题描述

第一次问问者，通常我可以通过搜索找到答案，但是今天我的google-fu似乎很虚弱.

First time asker, usually I can find the answer by searching, but my google-fu seems weak today.

我有一个Excel工作簿，该工作簿连接到Access 2003数据库以插入使用记录

I have an excel workbook connecting to an access 2003 database to insert usage records

我正在使用的代码是:

sdbpath = ThisWorkbook.Path & "\Data.mdb"
sCommand = "INSERT INTO Usage VALUES('" & Environ("Username") & "',#" & Now() & "#)"

Dim dbCon As New ADODB.Connection
Dim dbCommand As New ADODB.Command

dbCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sdbpath & "; Jet OLEDB:Database Password=TestPass;"
dbCommand.ActiveConnection = dbCon

dbCommand.CommandText = sCommand
dbCommand.Execute

dbCon.Close

代码在dbCommand行上失败.执行时出现运行时错误'-2147217900(80040e14)':自动化错误.

The code fails on the line dbCommand.Execute with run-time error '-2147217900 (80040e14)' : Automation error.

其试图将记录插入的数据库包含一个表，用法和两列-UserID和AccessDate，分别设置为文本和日期时间.

The database its trying to insert the record into contains one table, usage, with two columns - UserID and AccessDate, formatted as text and DateTime respectively.

奇怪的是，连接字符串似乎正常，因为在打开连接后失败，但是如果我在运行execute之前获取sCommand值，则将其粘贴到access中的查询中并执行-它会运行很好！

The odd part is the connection string seems OK, since it fails after the connection is already open, yet if I take the sCommand value before the execute is run, then paste it into a query in access and execute that - it runs fine!

万一它试图用日期时间格式访问时，我尝试将其切换为文本(以及代码中的#标签)，但这仍然失败.我也尝试过指定列名.

In case it was access struggling with the datetime format i've tried switching it to text (and the hashtags in the code) but that still fails. I've also tried specifying the column names too.

有人能说明我做错了什么吗?我对使用非常简单的SQL从未遇到过太多麻烦.

Can anyone shed some light on what i'm doing wrong? I've never had so much trouble with a very simple bit of SQL.

提前谢谢！

推荐答案

在Access中，我们需要指定字段名称.即便如此，我发现在插入之前，我需要将表名包装在方括号中:

In Access we need to specify the field-names. Even so, I found that I needed to wrap the table-name in square brackets before it would insert:

sCommand = "INSERT INTO [Usage] (UName, SomeDate) VALUES ('" & Environ("Username") _
    & "',#" & Now() & "#)"

这篇关于ado在excel中，将记录插入到Access数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！