MS Access - ADO 记录集，使用 SQL 语句检索数据并建表
假设我有一些如下代码来从另一个访问文件中提取数据：
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
所以在示例中是我的注释。基本上想知道如何根据记录集包含的数据创建表。我想创建一个tabledef？但这是 DAO 对吗？我真的不能在例程中同时使用 DAO 和 ADO，对吧？
谢谢 贾斯汀
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?
Thanks
Justin
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以在同一过程中将 ADO 和 DAO 用于不同的对象。
您可以创建一个 DAO.TableDef 并检查记录集的 Fields 集合，创建与每个 rs.Fields(i).Name 和 rs.Fields(i).Type 匹配的新 TableDef 字段
创建表结构 (TableDef) 后，您可以循环遍历记录集行以构建并执行 INSERT 语句，以将行值存储在新表中。
但这对我来说似乎工作量太大了。我更喜欢 Raj 的 SELECT INTO 建议。但是，由于您已经知道 MDB 的表名称和路径，因此我将首先使用 DoCmd.TransferDatabase，并仅将 ADO 留给 DAO 根本无法执行或无法执行的任务，如下所示与 ADO 一样方便。
最后，如果您对此的主要兴趣是探索可能性，请查看记录集的 Save 方法。您可以使用
adPersistXML进行保存，然后将保存的 XML 作为新表导入到当前数据库中。请参阅保存方法 (ADO)You can use both ADO and DAO for different objects within the same procedure.
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
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.
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.
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)我用一种丑陋的方式完成了这个任务 - 解析传入的 ADO 记录集，构建 CREATE TABLE 语句并执行它，然后通过 ADO 数据集进行 RBAR 插入到本地表中。
您还可以创建一个直通查询，然后使用该查询
SELECT * INTO MyNewTable FROM MyPassThroughQueryI 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.You can also create a passthrough query which you can then use to
SELECT * INTO MyNewTable FROM MyPassThroughQuery您可以尝试ADOX。
You could try ADOX.