使用VBA将数据从Excel插入Access 2010 [英] Insert data form Excel to Access 2010 using VBA

问题描述

我正在寻找一种将Excel中的数据插入Access 2010的解决方案.Excel中的数据是一种形式。填写表单后，Excel中的VBA将数据作为新记录插入Access表。

I am looking for a solution to inserting data from Excel into Access 2010. The Data in Excel are in a form. After filling in the form, VBA in Excel will insert data to the Access table as a new record.

1. 我不想在Access中使用函数 - 从Excel导入。


2. 我不想将数据存储在Excel - 对象表中。

我看了几个主题，但没有一个是我想要的。有人可以帮我吗？

I looked at several topics, but none were what I was looking for. Could somebody help me with this?

推荐答案

使用DAO通过打开的Excel工作表避免内存泄漏（ http://support.microsoft.com/kb/319998 ），例如：

Use DAO to avoid memory leaks with an open Excel sheet ( http://support.microsoft.com/kb/319998 ), for example:

'Reference: Microsoft Office x.x Access Database Engine Object Library
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim sDb As String
Dim sSQL As String
Dim qdf As QueryDef

sDb = "Z:\Docs\Test.accdb"

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(sDb)

''A stored query would be better
sSQL = "Parameters p1 Text, p2 Datetime; " _
& "INSERT INTO Table1 (AText,ADate) Values ([p1],[p2])"

Set qdf = db.CreateQueryDef("", sSQL)

qdf.Parameters!p1 = "ABC"
qdf.Parameters!p2 = #2013/1/17#
qdf.Execute dbFailOnError
Debug.Print qdf.RecordsAffected

这篇关于使用VBA将数据从Excel插入Access 2010的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！