Как сделать сквозной / passthru запрос редактируемым?
В Microsoft Access 2007 с бэкэндом SQL Server мы обычно берем связанную таблицу из SQL Server как Form.RecordSource редактируемой формы для модификации данных одной таблицы. Локальный запрос используется для кросс-таблиц, в которых объединены поля из нескольких связанных таблиц. Локальный запрос сам должен быть обновляемым, чтобы изменять данные в форме редактирования.
Теперь мы планируем заменить все локальные запросы на сквозные запросы, чтобы напрямую использовать собственные таблицы SQL Server.
Я попытался создать очень простой промежуточный запрос с именем qrySelProductsPassThroughEditable со следующей строкой SQL:
SELECT dbo.Products.ID, dbo.Products.Name FROM dbo.Products;
Поле ID - это поле IDENTITY, определенное как первичный ключ в SQL Server в качестве определения:
CREATE TABLE [dbo].[Products](
[ID] [int] IDENTITY(1,1) NOT NULL,
....
)
Но таблица данных, возвращаемая сквозным запросом Access, вообще не редактируется. Так что его нельзя использовать как.RecordSource для формы редактирования.
Это противоречит ссылке, которая говорит, что если промежуточный запрос содержит все первичные ключи всех задействованных таблиц, запрос будет редактируемым.
Заключение добавил апостериорный
В приведенных ниже обсуждениях сквозной запрос в Microsoft Access 2007 .accdb, .accde или.accdr (Access runtime) всегда доступен только для чтения, он никогда не редактируется. Вы должны использовать его как окончательный список или как.RecordSource отчета, а не для формы, для которой вы должны использовать связанную таблицу, или для обычного запроса с возможностью записи, включающего связанные таблицы для ввода-вывода данных.
4 ответа
В соответствии с моим комментарием выше и ответом Явара, я не знаю, что Pass Through Queries когда-либо редактировались / обновлялись. Они доступны для редактирования в том смысле, что вы можете редактировать сохраненный объект Pass Through Query, но я не верю, что Pass Pass Query может создать редактируемый набор записей.
Существует два основных способа подключения Access к источнику данных без доступа.
Первый и наиболее популярный метод - это использование некоторой формы связанных таблиц, обычно связанных таблиц ODBC. Существует множество способов использования связанных таблиц ODBC с MS Access, но большинство разработчиков предпочитают использовать соединения без DSN, которые обновляются или перестраиваются (удаляются и повторно подключаются) во время запуска приложения. Помните, что когда вы используете ODBC, вы все еще используете DAO. DAO - это объект доступа к данным по умолчанию, встроенный в MS Access, и даже если вы специально не пишете код DAO, MS Access все еще использует DAO для связи ваших форм, отчетов и запросов с вашим источником данных. В случае с ODBC у вас фактически есть два уровня доступа к данным: DAO и ODBC. Но вы можете использовать ODBC/DAO с довольно приличной производительностью и без написания кода (кроме поддержки таблиц, связанных с ODBC).
Второй метод заключается в использовании ADO. Вопреки распространенному мнению, это не означает, что вы должны использовать несвязанные формы. Но это означает, что вам нужно написать больше кода, чем с использованием JET/DAO/MSAccess или DAO/ODBC/SSQL Server. Вы должны написать код для ввода записей из вашей базы данных в ADO Recordset, а затем использовать код для привязки вашей формы к этому Recordset. Вы должны написать больше кода, чтобы синхронизировать дочерние формы с родительскими формами, вставлять внешние ключи в дочерние формы при создании новых записей и, возможно, для множества других вещей, таких как фильтрация и сортировка. ADO - это отличный способ общения с SQL Server, поскольку он действительно дает вам много контроля, но поскольку он интенсивно работает с кодом и поскольку ODBC Linked Tables работают так хорошо, большинство разработчиков не рекомендуют использовать ADO, если нет другого способа сделать то, что ты хочешь сделать. Одним из примеров этого является вызов хранимых процедур. Я считаю, что сквозные запросы можно использовать для вызова хранимых процедур, но я также думаю, что здесь есть некоторые ограничения (например, использование параметров). Я считаю, что в большинстве случаев разработчики используют ADO для вызова хранимых процедур. Я часто использую ADO, но я не использую хранимые процедуры (пока), поэтому у меня нет много информации об этом.
Моя собственная функция для создания одной связанной с DSN таблицы ODBC Linked ниже. Если вы новичок в Access и новичок в VBA, это, вероятно, не будет иметь большого смысла для вас. Код удаляет любое определение таблицы, которое уже существует для таблицы, которую вы пытаетесь связать, что немного опасно, потому что я считаю, что это может удалить локальную несвязанную таблицу, которая вам не нужна. Обработка ошибок здесь не очень быстра, но большинство примеров кода в Интернете не имеют хорошей обработки ошибок из-за связанных с этим осложнений. Создание индексов первичного ключа для связанной таблицы не всегда необходимо. Я просто встроил его в свою функцию, потому что он мне нужен был один раз для конкретного проекта, и теперь я оставляю его там и использую, к лучшему или к худшему.
Чтобы правильно использовать этот код, вам действительно нужно иметь список всех ваших связанных таблиц где-нибудь, перебирать этот список и вызывать эту функцию для каждой таблицы. Эта функция позволяет связать таблицу, используя имя, отличное от фактического имени в SQL Server. У вас также должен быть способ создания допустимой строки соединения ODBC, которая также должна быть передана в эту функцию.
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, Pass Through Queries, SQL Server и т.д.:
http://technet.microsoft.com/en-us/library/bb188204%28v=sql.90%29.aspx
http://www.utteraccess.com/wiki/index.php/Choosing_between_DAO_and_ADO
Вот пример связывания формы с набором записей ADO. Это немного вводит в заблуждение, потому что лучше иметь глобальный объект соединения, который остается открытым во время выполнения приложения. Это позволяет использовать наборы записей ADO, которые автоматически обновляются. Использование этой практики также может сделать ваш набор записей объектом уровня формы.
http://msdn.microsoft.com/en-us/library/office/bb243828%28v=office.12%29.aspx
Существует более простой, не документированный способ открытия любого оператора выбора SQL Server (таблицы, представления или sql-select со многими объединенными таблицами) в окне запроса MsAccess и его можно редактировать / обновлять:
Откройте окно запроса Access и введите свой оператор SQL. Замените имена таблиц полной строкой ODBC на SQL Server в квадратных скобках, за которыми следуют точка и имя схемы и таблицы, как в следующем примере:
До:
SELECT SOH.SalesOrderID, SOH.OrderDate
FROM Sales.SalesOrderHeader as SOH
После:
SELECT SOH.SalesOrderID, SOH.OrderDate
FROM [ODBC;Driver=SQL Server;Server=myServer;Database=AdventureWorks2012;Trusted_Connection=Yes;MarsConn=yes;].Sales.SalesOrderHeader as SOH
Запрос теперь обновляемый: 
Примечания:
- Не каждый оператор SQL делает таблицу или представление обновляемыми. Ограничения и ограничения см. В разделе "Обновляемые представления" в CREATE VIEW (Transact-SQL) ( https://msdn.microsoft.com/en-us/library/ms187956.aspx).
- Базовая таблица, которую вы хотите обновить в Access, должна иметь метку времени или столбец RowVersion.
Наборы результатов сквозных запросов не редактируются, но запросы Access, основанные на связанных таблицах, безусловно, являются.
Да, это верно для "Второго метода - использовать ADO", когда свойства LinkMasterFields и LinkChildFields не работают в мульти-форме, а набор записей ADO не работает в отчете Access 2013, поэтому я использую сквозной запрос. Я использую ADP+ ADPX.accde для имитации свойств LinkMasterFields и LinkChildFields в нескольких формах и нескольких отчетах.