如何使直通/直通查询可编辑? [英] How to make a passthrough / passthru query editable?

问题描述

在带有SQL Server后端的Microsoft Access 2007中，我们通常从SQL Server中获取一个链接表作为可编辑表单的Form.RecordSource，以进行单个表数据修改.本地查询用于跨表版本，该版本将多个链接表中的字段组合在一起.本地查询本身必须是可更新的，才能修改编辑表单上的数据.

In the Microsoft Access 2007 with an SQL Server Backend, we usually take a linked table from the SQL Server as Form.RecordSource of an editable form for a single table data modification. A local query is used for cross tables editions that combines fields from several linked tables. The local query must be updatable itself in order to modify data on the editing form.

现在，我们计划将所有本地查询替换为直通查询，以便直接使用本机SQL Server表.

Now we are planning to replace all local queries by Passthrough queries in order to use native SQL Server tables directly.

我尝试使用以下SQL字符串创建一个名为qrySelProductsPassThroughEditable的非常简单的passthru查询:

I've tried to create a very simple passthru query named qrySelProductsPassThroughEditable with the following SQL string:

SELECT dbo.Products.ID, dbo.Products.Name FROM dbo.Products;

ID字段是在SQL Server中定义为主键的IDENTITY字段，作为定义:

The ID field is the IDENTITY field defined as Primary Key in the SQL Server as the definition:

CREATE TABLE [dbo].[Products](
    [ID] [int] IDENTITY(1,1) NOT NULL,
        ....
)

但是Access传递查询返回的数据表根本不可编辑.因此，它也不能作为.RecordSource用于编辑表单.

But the Datasheet returned by Access pass-through query is not editable at all. So it's not usable as .RecordSource for an editing form neither.

与链接相反，表示如果通过查询包含所有涉及表的所有主键，则该查询将是可编辑的.

This is in contrary with the link that says if a passthru query contains all Primary Keys of all involved tables, the query will be editable.

通过以下讨论，Microsoft Access 2007 .accdb，.accde或.accdr(访问运行时)中的传递查询始终为只读状态，永远不可编辑.您应将其用作最终列表或报表的.RecordSource，而不用于必须使用链接表的表单，或用于数据IO的涉及链接表的可写Normal查询.

Through the discussions below, a passthrough query in a Microsoft Access 2007 .accdb, .accde or .accdr (Access runtime) is always readonly, it's never editable. You should use it as a final list, or as the .RecordSource of a report, not for a form for which you must use a linked table, or a writable Normal Query involving linked tables for data IO.

推荐答案

根据我上面的评论和Yawar的回答，我不知道传递查询曾经是可编辑/可更新的.在您可以编辑保存的通过查询"对象的意义上，它们是可编辑的，但是我认为通过查询"不可能产生可编辑的记录集.

In line with my comment above and the answer by Yawar, I'm not aware that Pass Through Queries are ever editable/updateable. They are editable in the sense that you can edit a save Pass Through Query object, but I don't believe it's possible for a Pass Through Query to produce an editable recordset.

基本上有两种方法可以将Access连接到非Access数据源.

There are basically two methods to connect Access to a non-Access data source.

第一种方法，也是最流行的方法，是使用某种形式的链接表，通常是ODBC链接表.通过MS Access使用ODBC链接表的方法有很多种，但是大多数开发人员更喜欢使用DSN-Less连接，这些连接在应用程序启动时会刷新或重建(删除并重新连接).请注意，当您使用ODBC时，您仍在使用DAO. DAO是MS Access内置的默认数据访问对象，即使您没有专门编写任何DAO代码，MS Access仍在使用DAO来将表单，报表和查询链接到数据源.在ODBC的情况下，您实际上最终要使用两个数据访问层，即DAO和ODBC.但是您可以使用具有相当不错性能的ODBC/DAO，而无需编写代码(除了维护ODBC链接表).

The first method, and most popular, is to use some form of linked tables, generally ODBC linked tables. There are a variety of methods of using ODBC linked tables with MS Access but by what most developers prefer is to use DSN-Less connections that get refreshed or rebuilt (deleted and reconnected) at the time that your application starts. Be aware that when you use ODBC, you are also still using DAO. DAO is the default data access object built into MS Access and even when you don't specifically write any DAO code, MS Access is still using DAO under the hood to link your forms, reports and queries to your data source. In the case of ODBC, you actually end up having two data access layers at work, DAO and ODBC. But you can use ODBC/DAO with pretty decent performance and without writing code (other than to maintain the ODBC linked tables).

第二种方法是使用ADO.与流行的看法相反，这并不意味着您必须使用未绑定的表格.但这确实意味着您必须编写比使用JET/DAO/MSAccess或DAO/ODBC/SSQL Server更多的代码.您必须编写代码以将数据库中的记录导入到ADO Recordset中，然后使用代码将表单绑定到该Recordset.您必须编写更多代码以使子表单与父表单保持同步，在创建新记录时在子表单中插入外键，还需要进行多种其他操作，例如作为表单的内置过滤和排序的过滤和排序选项通常不适用于ADO记录集. ADO是与SQL Server进行通讯的一种好方法，因为它确实为您提供了很多控制权，但是由于它的代码密集，并且因为ODBC链接表工作得如此好，所以除非没有其他方法可以使用，否则大多数开发人员不建议使用ADO.你想做.这样的一个例子是调用存储过程.我相信传递查询可用于调用存储过程，但我也认为那里存在一些限制(例如使用参数).我相信在大多数情况下，开发人员都使用ADO来调用存储过程.我使用ADO的次数很多，但是(没有)使用存储过程的次数很多(因此)，因此我没有太多的信息.

The second method is to use ADO. Contrary to popular belief, this does not mean that you have to use unbound forms. But it does mean that you have to write more code than using JET/DAO/MSAccess or DAO/ODBC/SSQL Server. You have to write code to bring in records from your database into and ADO Recordset and then use code to bind your form to that Recordset. You have to write more code to keep child forms in sync with parent forms, to insert foreign keys into child forms when new records are created, and for a variety of other things too like filtering and sorting as the form's built-in filtering and sorting options usually do not work with ADO recordsets. ADO is a great way to talk to SQL Server as it really gives you a lot of control, but because it's code intense, and because ODBC Linked Tables work so well, most developers do not recommend using ADO unless there's no other way to do what you want to do. One example of this is calling Stored Procedures. I believe Pass Through Queries can be used to call Stored Procedures but I also think there are some limitations there (such as using Parameters). I believe in most cases developers use ADO to call stored procedures. I use ADO a lot but I don't use Stored Procedures much (not yet) so I don't have a lot of information on that.

值得一提的另一件事是，带有ODBC的DAO使用延迟加载"，但是ADO强制您提取所有数据，这可能非常耗时，并且如果您具有>数百万的行，则会消耗大量内存.否则，您将需要实现某种分页.

One other thing worth mentioning is that DAO with ODBC uses "lazy loading" but ADO forces you to pull all of the data which can be very time consuming and consume a lot of memory if you have > millions of rows. Or else you will need to implement some kind of paging.

下面是我自己的用于创建单个DSN-Less ODBC链接表的函数.如果您不熟悉Access和VBA，那么对您来说可能就没有多大意义了.该代码删除了您要链接的表的任何表定义，这有点危险，因为我认为它可以删除您不想要的本地非链接表.这里的错误处理也不能真正加快速度，但是大多数在线示例代码由于涉及复杂性而没有良好的错误处理.并非总是需要在链接表上创建主键索引.我只是将它内置到我的函数中，因为我需要为一个特定项目使用它一次，所以现在我把它留在那里并使用它，不管是好是坏.

My own function to create a single DSN-Less ODBC Linked table is below. If you're new to Access and new to VBA this probably won't make a lot of sense to you. The code deletes any table definition that already exists for the table you're trying to link, which is a little dangerous because I believe it could delete a local, non-linked table which you wouldn't want. The error handling in here isn't really up to speed either, but most online example code doesn't have good error handling in it because of the complications that involves. The creation of Primary Key Indexes on a linked table isn't always necessary. I just have it built into my function because I needed it one time for a specific project so now I leave it in there and use it, for better or for worse.

要正确使用此代码，您确实需要在所有地方都有所有链接表的列表，然后遍历该列表并为每个表调用此函数.此功能允许您使用与SQL Server中的实际名称不同的名称链接表.您还需要一种构建有效的ODBC连接字符串的方法，该字符串也必须传递到此函数中.

To make proper use of this code you really need to have a list of all your linked tables somewhere and iterate through that list and call this function for each table. This function allows you to link the table up using a different name than it's actual name in SQL Server. You also need to have a way of building a valid ODBC connection string which must be passed into this function too.

Private Sub LinkODBCTable(sSourceTableName As String, _
                        sLocalTableName As String, _
                        sPrimaryKeyField As String, _
                        sConString As String)

    Dim dbCurrent As DAO.Database
    Dim tdfCurrent As DAO.TableDef
    Set dbCurrent = DBEngine.Workspaces(0).Databases(0)

    On Error Resume Next
    'Be Careful, this could delete a local, non-linked table.
    dbCurrent.TableDefs.Delete sLocalTableName
    If Err.Number <> 0 Then
        If Err.Number = 3011 Then
            'Table does not exist
        Else
            MsgBox "Error in LinkODBCTable" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description
        End If
        Err.Clear
    End If

    On Error GoTo 0

    Set tdfCurrent = dbCurrent.CreateTableDef(sLocalTableName)
    tdfCurrent.Connect = sConString
    tdfCurrent.sourceTableName = sSourceTableName
    dbCurrent.TableDefs.Append tdfCurrent

    On Error Resume Next
    If sPrimaryKeyField <> "" Then
        dbCurrent.Execute "CREATE INDEX __UniqueIndex ON [" & sLocalTableName & "] (" & sPrimaryKeyField & ")", dbFailOnError
        If Err.Number <> 0 Then
            If Err.Number = 3283 Then
                'Primary Key Already Exists
            Else
                MsgBox "Error in LinkODBCTable" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description
            End If
            Err.Clear
        End If
    End If

    Set tdfCurrent = Nothing
    Set dbCurrent = Nothing
End Sub

关于DAO，ADO，直通查询，SQL Server等，您应该检查一些非常好的资源:

There are a few really good resources you should check out concerning DAO, ADO, Pass Through Queries, SQL Server, etc:

http://technet.microsoft .com/en-us/library/bb188204％28v = sql.90％29.aspx
http://www.utteraccess.com/wiki/Choosing_between_DAO_and_ADO

这里是将表单绑定到ADO Recordset的示例.但是，这有点误导，因为最好是使全局连接对象在应用程序的运行期间保持打开状态.这使您可以使用可自动更新的ADO记录集.使用这种做法还可能使您的记录集成为一个表单级对象.

Here's an example of binding a form to an ADO Recordset. It's a little misleading though because it's best to have a global connection object that stays open during the runtime of the application. This allows you to use ADO recordsets that are automatically updateable. Using this practice might also make your recordset a form level object.

http://msdn .microsoft.com/en-us/library/office/bb243828％28v = office.12％29.aspx

这篇关于如何使直通/直通查询可编辑?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！