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.
Now we are planning to replace all local queries by Passthrough queries in order to use native SQL Server tables directly.
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;
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,
....
)
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.
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.
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.
There are basically two methods to connect Access to a non-Access data source.
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).
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.
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.
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.
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
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
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