使用vba将excel表导入到现有的访问表中 [英] importing an excel table into already existing access table with vba

问题描述

大家好！


我在互联网和这个论坛搜索了类似的问题，但我没有找到任何帮助......而且，我对这个迷人的世界完全是新手编程，VBA和Access，所以我的问题很可能是非常愚蠢的。


任务是：


1）将Excel表导入Access

2）添加新列并用日期/时间类型的变量填充它。


步骤1和2只需要完成一次，我几乎成功完成了它们。现在来了黑客：


3）这个导入的表需要每天更新......换句话说，它会随着每天的增长而增长。


我创建了一个FileDialog对象，我可以在其中选择要导入的Excel文件。首先，程序检查所请求的Access表是否已经存在，如果它不存在，代码会执行简单的TransferSpreadsheet。如果表存在，我尝试对粘合进行追加查询。 Excel表记录 - 那是我完全失败的地方。


这是我目前的代码片段：

展开 | 选择 | Wrap | 行号

解决方案

请尝试＃12/31/2008＃。

展开 | 选择 | Wrap | 行号

尝试＃12/31 /改为2008＃。

展开 | 选择 | Wrap | 行号

谢谢，兔子！


我仍​​然在日期得到语法错误...我曾尝试过


格式（" 31.12.2008"，vbShortDate）


但它也没有用。


至于追加查询，我知道vrtSelectedItem是错误的。但是我不知道如何使用该查询引用Excel表....

问题可能在于您定义了该字段创建时Monat为文本。


你不能参考excel表。但是你不必这么做，因为你将excel表导入Access，名称为Blublu。为什么你想要附加一组重复记录超出我的范围。


你想要做的是相当于：

展开 | 选择 | Wrap | 行号

Hello all!

I searched the Internet and this forum for a similar problem, but I found no help... Also, I''m a complete newbie to the fascinating world of programming, VBA and Access, so my question can very well be very stupid.

The tasks are:

1)Import an Excel table into Access
2)Add a new column and fill it with variables of date/time type.

Steps 1 and 2 need to be done only once, and I''ve almost managed to accomplish them. Now comes the hack:

3)This imported table needs to be updated daily... In other words, it will grow with every day.

I created a FileDialog object where I can choose the Excel file to import. First, the programme checks whether the requested Access table already exists and if it doesn''t, the code does a simple TransferSpreadsheet. If the table exists, I tried to make an append query to "glue" the Excel table records - that''s where I failed completely and miserably.

Here''s a code snippet from what I have so far:

Expand|Select|Wrap|Line Numbers

解决方案

Try #12/31/2008# instead.

Expand|Select|Wrap|Line Numbers

Try #12/31/2008# instead.

Expand|Select|Wrap|Line Numbers

Thank you, Rabbit!

I still get the syntax error on the date... I had tried

Format("31.12.2008", vbShortDate)

but it didn''t work either.

As for the append query, I know that vrtSelectedItem is wrong. But I don''t know how can I refer to the Excel table withing that query....

The problem may lie in the fact that you defined the field Monat as text when you created it.

You can''t refer to the excel table. But you don''t have to because you imported the excel table into Access under the name Blublu. Why you want to append a set of duplicate records is beyond me.

What you want to do is the equivalent of:

Expand|Select|Wrap|Line Numbers

这篇关于使用vba将excel表导入到现有的访问表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！