在 Ms Office Access 中使用 VBA 从 SQL 查询动态创建数据表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23292972/
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
Dynamically create Datasheet from SQL query with VBA in Ms Office Access
提问by Nikunj Bhatt
I have Access database project connected with Ms SQL Server. Data (tables) is stored in the Ms SQL server and Forms and Reports are stored in the Access .ADP file. It is not possible to create Queries, Tables, Views using Design view but Tables and Views can be created using SQL queries and stored on the server. I don't have Ms SQL Server Management Studio and I can't install it in my computer in my office.
我有与 Ms SQL Server 连接的 Access 数据库项目。数据（表）存储在 Ms SQL 服务器中，表单和报表存储在 Access .ADP 文件中。无法使用设计视图创建查询、表、视图，但可以使用 SQL 查询创建表和视图并存储在服务器上。我没有 Ms SQL Server Management Studio，也无法将其安装在我办公室的计算机中。
So, what I want is to get a dynamically generated Datasheet of a SELECT SQL query to see results temporarily for data analysis. I have placed a textbox and a button in a form and want to display a datasheet containing the result of the SQL query written in the textbox when the button is clicked.
所以，我想要的是获取一个动态生成的 SELECT SQL 查询的数据表，以临时查看数据分析的结果。我在表单中放置了一个文本框和一个按钮，并希望在单击按钮时显示包含在文本框中写入的 SQL 查询结果的数据表。
I tried this but it is not working for me and doesn't seems what I want:
MS Access VBA - display dynamically built SQL results in datasheet subform
我试过了，但它对我不起作用，似乎不是我想要的：
MS Access VBA - 在数据表子表单中显示动态构建的 SQL 结果
I also tried by assigning query to Recordsource property of a form. It is showing blank datasheet, but the navigation pane below the datasheet is showing the actual number of records retrieved. So, it is working but not showing the data.
我还尝试将查询分配给表单的 Recordsource 属性。它显示空白数据表，但数据表下方的导航窗格显示检索的实际记录数。所以，它正在工作，但没有显示数据。
I tried (from http://www.pcreview.co.uk/forums/create-query-dynamically-vba-t3146896.html):
我试过（来自http://www.pcreview.co.uk/forums/create-query-dynamically-vba-t3146896.html）：
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = "select * from analysts"
Set qd = db.CreateQueryDef("NewQueryName", strSQL)
DoCmd.OpenQuery "db.NewQueryName"
It is showing run-time error 91, Object variable or With block variable not set on the line Set qd = db....
它显示运行时错误 91，对象变量或块变量未在行上设置 Set qd = db....
And also (from the same page):
还有（来自同一页面）：
Dim strSql As String
strSql = "select * from analysts"
CurrentDb.QueryDefs("qryExport").SQL = strSql
DoCmd.OpenQuery "qryExport"
Returning same error on the line CurrentDb.QueryDefs.....
在线返回相同的错误CurrentDb.QueryDefs....。
Any idea or workaround?
任何想法或解决方法？
回答by KevenDenen
Access ADPs are a whole different beast than the normal MDB that you are probably used to working with. DAO isn't generally used in ADPs, you probably had to add a reference to DAO to get any part of the code above to work. ADPs are designed around using ADO to interact with the source database. If you just want to open a recordset to your data, use something along these lines.
Access ADP 与您可能习惯使用的普通 MDB 完全不同。DAO 通常不用于 ADP，您可能必须添加对 DAO 的引用才能使上述代码的任何部分工作。ADP 是围绕使用 ADO 与源数据库进行交互而设计的。如果您只想打开数据的记录集，请使用以下内容。
Dim strSql As String
Dim rs as ADODB.Recordset
strSql = "select * from analysts"
set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.Source = strsql
rs.Open
You can then interact with that recordset. If you want to bind that recordset to a form so that you can view the data, you can use:
然后您可以与该记录集进行交互。如果您想将该记录集绑定到一个表单以便您可以查看数据，您可以使用：
Set Me.Recordset = rs
回答by Nikunj Bhatt
This doesn't seems efficient, but it is working and a bit satisfactory:
这似乎效率不高，但它有效并且有点令人满意：
DoCmd.RunSQL "IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS " & _
"WHERE TABLE_NAME = 'tv') DROP VIEW tv"
DoCmd.RunSQL "create view tv as " & txtQry
DoCmd.OpenView "tv"
Here I am creating a temporary VIEW (tv) in a button's click event. Before creating the view, I am checking that if a view with the same name exist or not; and if it exist then delete it so that a new view can be created with the same name with different query.
在这里，我在按钮的点击事件中创建了一个临时的 VIEW (tv)。在创建视图之前，我正在检查是否存在具有相同名称的视图；如果它存在，则将其删除，以便可以使用不同的查询创建具有相同名称的新视图。
回答by Nikunj Bhatt
This is working how I want:
这是我想要的工作方式：
Dim frm As Form ' create a form dynamically
Set frm = CreateForm
Dim rs As New ADODB.Recordset
rs.Open Replace(txtQry, vbCrLf, " "), CurrentProject.Connection ' replace "enters" (vbCrLf) with space because it was throwing error while executing query!
Dim c As Control
For Each f In rs.Fields
Set c = CreateControl(frm.Name, IIf(f.Type = 11, acCheckBox, acTextBox)) ' if field type is "bit", add checkbox; for other datatypes, add textbox
c.ControlSource = f.Name
c.Name = f.Name ' sets column header to the same name as of field's name
Next
rs.Close
frm.RecordSource = txtQry
DoCmd.OpenForm frm.Name, acFormDS ' open form in "DataSheet" view otherwise it will be in "Form" view
This code is placed in a button's click event.
此代码放置在按钮的单击事件中。
Other possible datatypes for rs.Fields(x).Type(here, f.Type) are (3 = int, 200 = varchar)
rs.Fields(x).Type(here, f.Type) 的其他可能的数据类型是 (3 = int, 200 = varchar)
