vba MS Access - ADO 记录集，使用 SQL 语句检索数据和建表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4090201/
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
MS Access - ADO recordset, using with SQL statement to retrieve data and build table
提问by Justin
So lets say I have some code like below to pull data from another access file:
所以可以说我有一些像下面这样的代码来从另一个访问文件中提取数据：
Sub ADO_Recordset_OpenTable()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim MyPath As String
MyPath = CurrentProject.Path
Set cn = New ADODB.Connection
cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn.ConnectionString = "Data Source=C:\Users\Justin\Desktop\ExampleFile.mdb"
cn.Open
Set rs = New ADODB.Recordset
rs.Open "Schedule", cn, adOpenDynamic, adLockReadOnly, adCmdTable
' I would like to at this point build a table within the currentdb file
' with the data in the recordset. Either some kind of create table or
' SQL INSERT?? Just trying to learn how to work with the data set
So within the example are my comments. Basically would like to know how to create a table out of the data contained with the recordset. I guess creating a tabledef? But this is DAO right? and I couldn't really use both DAO and ADO together in a routine right?
所以在例子中是我的评论。基本上想知道如何从记录集包含的数据中创建一个表。我想创建一个tabledef？但这是 DAO 对吗？我真的不能在例行程序中同时使用 DAO 和 ADO，对吗？
Thanks Justin
谢谢贾斯汀
回答by HansUp
You can use both ADO and DAO for different objectswithin the same procedure.
您可以对同一过程中的不同对象同时使用 ADO 和 DAO 。
You could create a DAO.TableDef and examine the recordset's Fields collection, creating new TableDef fields matching each rs.Fields(i).Name and rs.Fields(i).Type
您可以创建一个 DAO.TableDef 并检查记录集的 Fields 集合，创建与每个 rs.Fields(i).Name 和 rs.Fields(i).Type 匹配的新 TableDef 字段
Once you have created the table structure (TableDef), you can loop through the recordset rows to build and execute INSERT statements to store the row values in your new table.
创建表结构 (TableDef) 后，您可以遍历记录集行以构建和执行 INSERT 语句以将行值存储在新表中。
But that seems like waaaay too much work to me. I like Raj's SELECT INTO suggestion better. However, since you already know the table name and path to your MDB, I would reach first for DoCmd.TransferDatabase, and leave ADO only for tasks DAO can't do at all or can't do as conveniently as ADO.
但这对我来说似乎工作太多了。我更喜欢 Raj 的 SELECT INTO 建议。但是，由于您已经知道 MDB 的表名和路径，因此我会首先访问 DoCmd.TransferDatabase，并且仅将 ADO 留给 DAO 根本无法完成或无法像 ADO 那样方便地完成的任务。
Finally, if your primary interest on this one is exploring possibilities, take a look at the recordset's Save method. You could save with adPersistXML, then import the saved XML as a new table in your current db. See Save Method (ADO)
最后，如果您对此的主要兴趣是探索可能性，请查看记录集的 Save 方法。您可以使用 保存adPersistXML，然后将保存的 XML 作为新表导入当前数据库中。请参阅保存方法 (ADO)
回答by Raj More
I have done this the ugly way - parse the incoming ADO recordset, build the CREATE TABLEstatement and execute it, and then RBAR through the ADO dataset to insert into the local table.
我以丑陋的方式完成了此操作 - 解析传入的 ADO 记录集，构建CREATE TABLE语句并执行它，然后通过 ADO 数据集进行 RBAR 插入到本地表中。
You can also create a passthrough query which you can then use to SELECT * INTO MyNewTable FROM MyPassThroughQuery
您还可以创建一个直通查询，然后您可以使用它 SELECT * INTO MyNewTable FROM MyPassThroughQuery
