Excel to Access，SQL和VBA [英] Excel to Access, SQL and VBA

问题描述

来自业余新手的第一篇文章......


已将此代码放在一起，以便将数据从Excel电子表格上传到Access后端。从一个电子表格中，数据正确输入，但是从另一个稍微适应的数据，数据不会出现。我可以看到链接几乎正在制作，因为我的PK自动编号正在递增，留下失败的插入引起的间隙。使用了debug.print我想知道前导/尾随空间是否搞乱了所以我使用了Trim，但仍然没有快乐。

这是代码 - 感谢去年ZMDB发布的帖子这个的基础。

展开 | 选择 | Wrap | 行号

解决方案

要做的第一件事就是确保导入的数据是该字段的正确数据类型...


我们还需要了解第20行中的SQL如何解析。调试打印：）

绝对。


一个技巧，当从执行SQL返回的信息有点微不足道时，就是采取解析SQL并从中创建QueryDef（打开一个新的QueryDef;切换到SQL视图;粘贴SQL;切换回设计视图）。运行时，这通常会提供更多有用的信息，说明为什么有些记录失败。

非常感谢您的回复。


B27是一个日期字段，格式化为长日期。目标，中期日期，参考设置了周表的完整性。

D27是用户姓名首字母，文本。目标文本，参考。设置员工表的完整性。

A29是一个需要5位数的文本字符串，格式为excel中的文本。目标，文字，参考完整性设置为作业表。我的一些失败很可能源于此（尽管事实证明这并不是导致失败的唯一原因），因为同一个工作并没有从不同的工作表中失败。

C29是十进制小时，原点在相邻单元格B29中，在excel中舍入为2（C29 = ROUND（B29 * 24,2）; B29格式= [h]：mm。目标数据库小时为数字，双倍，只有1分钟的地方（虽然查看数据，但这并不是数据的存储方式，因为它有2个十二分的位置。



1.使用Debug.Print Trim调整打印失败的数据行（Range（B27））;B27

Debug.Print Trim（Range（" D27"）） ;D27

Debug.Print Trim（Range（A29））;A29

Debug.Print Trim（Range（" C29"） ））;C29：

展开 | 选择 | Wrap | 行号

First post from an amateur newbie......

Have put this code together to upload data from an Excel spreadsheet into an Access backend. From one spreadsheet the data enters correctly, but from another which is slightly adapted, the data does not appear. I can see that the link is almost being made, as my PK autonumbers are incrementing leaving gaps the failed inserts are causing. Having used a debug.print I wondered if leading/trailing spaces were messing things up so I used Trim, but still no joy.
Here is the code - thanks to a post last year from ZMDB for the basis of this.

Expand|Select|Wrap|Line Numbers

解决方案

the first thing to do is to make sure that the imported data is the correct data type for the field...

we also need to see how the SQL in line 20 is resolving. Debug print that :)

Absolutely.

One trick, when the information coming back from executing SQL is a little paltry, is to take the resolved SQL and create a QueryDef from it (Open a new QueryDef; Switch to SQL view; Paste the SQL in; Switch back to Design view). When run, this typically gives more helpful information as to why some records failed.

Thanks kindly for your responses.

B27 is a date field, formatted as long date. Target, medium date, ref. integrity to a weeks table is set.
D27 is user initials, text. Target text, ref. integrity to a staff table is set.
A29 is a text string needing 5 digits, formatted as text in excel. Target, text, ref. integrity set to a jobs table. Some of my failures may well have stemmed from this (although this is not proven to be the only cause of failure) as the same Job no fails from different sheets.
C29 is decimal hours with origin in neighbouring cell B29, rounded to 2 in excel (C29=ROUND(B29*24,2); Format of B29=[h]:mm. Target database hrs is Number, double, only 1 dec place (though looking at the data this has not been how the data is stored as much has 2 dec places.



1. Debug print of a failing line of data using Debug.Print Trim(Range("B27")); " B27"
Debug.Print Trim(Range("D27")); " D27"
Debug.Print Trim(Range("A29")); " A29"
Debug.Print Trim(Range("C29")); " C29":

Expand|Select|Wrap|Line Numbers

这篇关于Excel to Access，SQL和VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！