vba 从 Excel 工作表填充 Access 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6047361/
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
Populate Access table from Excel worksheet
提问by Mr_Thomas
I found some code online that exports my Excel data to an Access table. The problem is that it creates the headers correctly but does not populate the data at all. How do I tell Excel to go through the data and export those fields?
我在网上找到了一些将我的 Excel 数据导出到 Access 表的代码。问题是它正确创建了标题，但根本不填充数据。如何告诉 Excel 浏览数据并导出这些字段？
Here's the code:
这是代码：
Sub Z_CreateTable()
'If this subroutine throws errors, remember to go to:
'Tools > References and select 'Microsoft ActiveX Data Objects #.# Library'
Dim dbConnectStr As String
Dim Catalog As Object
Dim cnt As ADODB.Connection
Dim dbPath As String
Dim strSQL As String
Dim rngDB As Range
Dim wbPath As String
Dim stSQL As String
Dim strTable As Object
'Set database name here
dbPath = "T:\Projects\testdata1.mdb"
dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
'Create new database
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create dbConnectStr
'Connect to database and insert a new table
Set cnt = New ADODB.Connection
cnt.CursorLocation = adUseServer
With cnt
.Open dbConnectStr
.Execute "CREATE TABLE tblSample ([FIELD1] text(50) WITH Compression, " & _
"[NAME] text(150) WITH Compression, " & _
"[BLANK] text(10) WITH Compression, " & _
"[CLASSID] text(10) WITH Compression, " & _
"[TYPE] text(5) WITH Compression, " & _
"[FIELD2] text(5) WITH Compression, " & _
"[FIELD3] text(5) WITH Compression, " & _
"[FIELD4] text(15) WITH Compression, " & _
"[START YEAR] text(15) WITH Compression, " & _
"[END YEAR] text(10) WITH Compression)"
End With
Set cnt = Nothing
End Sub
回答by Fionnuala
See: asp and ms-access db - how to import data from xls fileYou can update from Excel using fairly standard SQL without first creating a table and without iterating through rows, which is very slow.
请参阅：asp 和 ms-access db - 如何从 xls 文件导入数据您可以使用相当标准的 SQL 从 Excel 进行更新，而无需先创建表，也无需遍历行，这非常慢。
回答by Beth
Have you tried importing from the Access database? You can create your own table first and that gives you more control over the field data types and indexes.
您是否尝试过从 Access 数据库导入？您可以先创建自己的表，这样您就可以更好地控制字段数据类型和索引。
回答by Kit Z. Fox
It is true that this code will only create the table. To insert the records, you'll need to follow this up with an Execute that uses "INSERT INTO tblSample" and then each Excel row.
确实，这段代码只会创建表。要插入记录，您需要执行使用“INSERT INTO tblSample”和每个 Excel 行的执行。
I don't know how to do this in VBA, but I could do it in VB using a For Each row In {table}.Rows...Nextstatement, so maybe Googling For Eachsyntax in VBA would help you. Or maybe someone here can actually give you a good example.
我不知道如何在 VBA 中做到这一点，但我可以使用For Each row In {table}.Rows...Next语句在 VB 中做到这一点，所以也许For EachVBA 中的谷歌搜索语法会对你有所帮助。或者也许这里的某个人实际上可以给你一个很好的例子。
EDIT: Something close to this might work
编辑：接近这个的东西可能会起作用
For i = startRow to endRow
cnt.Execute "INSERT INTO tblSample VALUES (" & _
Cells(i, 1).Value & "," & Cells(i, 2).Value & _
--And so on
& ")"
Next i
You'd have to set the values for the start and end rows. This loop would go through each row in your range and insert the value of the cell in that row into the field you specify. You'll need to make the order the same as the columns in your table, so you might have Cells(i, 2).Valuefirst, for instance.
您必须设置开始行和结束行的值。此循环将遍历范围内的每一行，并将该行中单元格的值插入到您指定的字段中。例如，您需要使顺序与表中的列相同，因此您可能Cells(i, 2).Value首先拥有。
Try executing it immediately after your Withblock, before the Set cnt = Nothing.
尝试在With块之后立即执行它，在Set cnt = Nothing.
EDIT: Using SELECT INTO
编辑：使用 SELECT INTO
.Execute "SELECT * INTO tblSample FROM "[{Your version of Excel};
DATABASE={Your Excel spreadsheet}].[{Your sheet name}]"
Replacing your existing .Execute block.
替换现有的 .Execute 块。
