vba Microsoft Access DateTime Default Now 通过 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/972273/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Microsoft Access DateTime Default Now via SQL
提问by Kevin Lamb
I am writing a number of scripts to update numerous Access tables. I would like to add a column to each that has a field called "date_created" that is a timestamp of when the record is created. Doing this through the table view is simple, simply set the DefaultValue = now(). However, how would I accomplish this in sql?
我正在编写许多脚本来更新许多 Access 表。我想为每个字段添加一个名为“date_created”的字段，该字段是创建记录的时间戳。通过表视图执行此操作很简单，只需设置 DefaultValue = now()。但是，我将如何在 sql 中完成此操作？
This is my current attempt for tables that already have the column. This example uses "tblLogs".
这是我目前对已经有该列的表的尝试。本示例使用“tblLogs”。
ALTER TABLE tblLogs ALTER COLUMN date_created DEFAULT now()
Thanks for your help!
谢谢你的帮助！
Update - Would there be a way to do this in VBA?
更新 - 有没有办法在 VBA 中做到这一点？
Update 2 - Tested all of the answers and the following by onedaywhen is the shortest and most accurate
更新 2 - 测试了所有的答案和以下 onedaywhen 是最短和最准确的
CurrentProject.Connection.Execute _
"ALTER TABLE tblLogs ALTER date_created DATETIME DEFAULT NOW() NOT NULL;"
采纳答案by onedaywhen
I'm assuming:
我假设：
- your target database is the ACE or Jet engine, rather than SQL Server;
- you want the date andtime i.e. what is known as
CURRENT_TIMESTAMPin Standard SQL (not directly supported by ACE/Jet, though) and not to be confused with SQL Server'sTIMESTAMPdata type; - you want an answer using SQL DDL.
- 您的目标数据库是 ACE 或 Jet 引擎，而不是 SQL Server；
- 您想要日期和时间，即
CURRENT_TIMESTAMP标准 SQL 中已知的内容（尽管 ACE/Jet 不直接支持），并且不要与 SQL Server 的TIMESTAMP数据类型混淆； - 您想要使用 SQL DDL 的答案。
If you created your table using this SQL DDL:
如果您使用此 SQL DDL 创建表：
CREATE TABLE tblLogs
(
date_created DATETIME NOT NULL
);
then the following SQL DDL would add the DEFAULTyour require:
那么以下 SQL DDL 将添加DEFAULT您的要求：
ALTER TABLE tblLogs ALTER
date_created DATETIME DEFAULT NOW() NOT NULL;
The above ACE/Jet SQL syntax is ANSI-92 Query Mode flavour. To use this via the MS Access interface (e.g. a Query object's SQL view) see Microsoft Office Access: About ANSI SQL query mode (MDB). To do this programmatically using SQL DDL requires (AFAIK) the use of OLE DB, which in VBA requires (AFAIK) the use of ADO. DAO always uses ANSI-89 Query Mode syntax, whose SQL DDL syntax lacks support for DEFAULT.
上面的 ACE/Jet SQL 语法是 ANSI-92 查询模式风格。要通过 MS Access 界面（例如查询对象的 SQL 视图）使用此功能，请参阅Microsoft Office Access：关于 ANSI SQL 查询模式 (MDB)。要使用 SQL DDL 以编程方式执行此操作，需要 (AFAIK) 使用 OLE DB，而在 VBA 中需要 (AFAIK) 使用 ADO。DAO 始终使用 ANSI-89 查询模式语法，其 SQL DDL 语法缺乏对DEFAULT.
If you are using Access (rather than ACE/Jet standalone), you can use a single line of VBA (ADO) code:
如果您使用的是 Access（而不是独立的 ACE/Jet），您可以使用一行 VBA (ADO) 代码：
CurrentProject.Connection.Execute "ALTER TABLE tblLogs ALTER date_created DATETIME DEFAULT NOW() NOT NULL;"
回答by Arvo
Instead of now() you can use getdate(). Can't check other part of syntax (no sql here and I rarely change tables :)), but should be about same.
您可以使用 getdate() 代替 now()。无法检查语法的其他部分（这里没有 sql，我很少更改表 :)），但应该大致相同。
edit: Seems that SQL doesn't allow change defaults so easily. Look at this page, where practical example is provided - you need to change constraints, because MSSQL treats defaults as constraints. Sorry for misinformation.
编辑：似乎 SQL 不允许如此轻松地更改默认值。查看此页面，其中提供了实际示例 - 您需要更改约束，因为 MSSQL 将默认值视为约束。对不起，错误信息。
回答by James Conigliaro
In SQL Server it would be:
在 SQL Server 中，它将是：
ALTER TABLE tblLogs ALTER COLUMN date_created DEFAULT getDate()
ALTER TABLE tblLogs ALTER COLUMN date_created DEFAULT getDate()
回答by David-W-Fenton
I suggest that to create or restructure Jet tables, you use DAO instead of DDL. It offers the ability to control properties that are lacking in Jet's implementation of DDL. The Access help should provide you what you need on this.
我建议创建或重构 Jet 表，您使用 DAO 而不是 DDL。它提供了控制 Jet 的 DDL 实现中所缺乏的属性的能力。Access 帮助应该为您提供这方面的需要。
