How can I create an updateable recordsource for an MSACCESS subform datasheet with a parameterised query or stored procedure in ADO?

Question

I have a subform set to the datasheet view, that I am trying to update with data from SQL Server, based on parameters specified by the users selection.

I usually achieve this with stored procedures and have the VBA call them through ADO, but this data is different in that the RecordSource needs to be able to be amended/edited directly within the data table.

I have a data table that I currently use to display data and this uses the below code:

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sp As ADODB.Command

Set db = New ADODB.Connection
Set rs = New ADODB.Recordset
Set sp = New ADODB.Command
Set ps = frmName
    db.Open dbString
    db.CursorLocation = adUseClient

    With sp
        .ActiveConnection = db
        .CommandText = "x04_ch_sl_ptsTable"
        .CommandType = adCmdStoredProc
        .Parameters.Append sp.CreateParameter("@tmName", adVarChar, adParamInput, 4, tmName)
    End With

    sp.Parameters.Append sp.CreateParameter("@wkEnd", adDBTimeStamp, adParamInput, , wkEnd)
        Set rs = sp.Execute
        Set ps.subFormDataViewer.Form.Recordset = rs
        Exit Sub
    End If
db.Close

Can this be amended to make the dataset updateable?

This is so far the only way I have been able to achieve what I want to do, but due to the majorly obvious security and injection issues, I do not want to go down this route:

ps.subFormDataViewer.Form.RecordSource = " SELECT p.ps_dateClaim as [Date], p.ps_id as [ID], p.ps_empid as [Employee ID], e.hc_fullName as [Employee], p.ps_pts as [Claimed] " & _
                                            " FROM (([ODBC;DRIVER=SQL Server;SERVER=<REDACTED>;Integrated_Security=SSPI;DATABASE=<REDACTED>].prod_pts as p " & _
                                            " INNER JOIN [ODBC;DRIVER=SQL Server;SERVER=<REDACTED>;Integrated_Security=SSPI;DATABASE=<REDACTED>].hc_employee as e " & _
                                                " ON p.ps_empid = e.hc_empid) " & _
                                            " WHERE (p.ps_dateClaim = #" & wkEnd & "#" & _
                                                " AND (e.hc_teamName = '" & tmName & "'); "

I have tried the following ADO methods with no success:

    With sp
        Set .ActiveConnection = db
        .CommandText = " SELECT t.Date, t.TeamName, t.Reference, t.Status, t.Reason, t.Checked " & _
                        " FROM testTbl as t" & _
                        " WHERE t.TeamName = @tmName "
            .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 4, tm)
        Set rs = .Execute
    End With

    With sp
        Set .ActiveConnection = db
        .CommandText = " SELECT t.Date, t.TeamName, t.Reference, t.Status, t.Reason, t.Checked " & _
                        " FROM testTbl as t" & _
                        " WHERE t.TeamName = ? "
        Set rs = .Execute(, Array(tm))
    End With

(the 2nd example does produce data, but the recordset is not updateable as it recognises each field as an "expression")

I have been able to achieve this with DAO:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim qd as DAO.QueryDef
Dim dbSQL as string

dbSQL = " SELECT t.Date, t.TeamName, t.Reference, t.Status, t.Reason, t.Checked " & _
                        " FROM testTbl as t" & _
                        " WHERE t.TeamName = tmName1 "
Set qd = db.CreateQueryDef("", dbSQL)

With qd
    .Parameters!tmName1 = tm
End With

Set rs = qd.OpenRecordset
Set ps.subFormDataViewer.Form.Recordset = rs

Erik A · Accepted Answer · 2019-10-25T11:08:16.023

3

You can't. Recordsets returned from stored procedures are not updateable.

Of course, you can use a parameterized query using DAO if you want it to be updateable.

For how to create parameterized queries, see this answer, the Using DAO section. Note that I strongly warn against it, the last time I used that Access had some bugs in handling filters/sorts on parameterized queries.

You can also check the Using ADO section for how to do this via a passthrough query.

Note that obfuscating your SQL server address is futile (can trivially be discovered using internet traffic analysis tools), and since you appear to not use encrypted connections, your current obfuscation of other parameters is trivially worked around too. This might be enough to ward off curious users not experienced in SQL server security, but is by no means true security.

For an updateable ADO recordset:

With sp
    Set .ActiveConnection = db
    .CommandText = " SELECT t.Date, t.TeamName, t.Reference, t.Status, t.Reason, t.Checked " & _
                    " FROM testTbl as t" & _
                    " WHERE t.TeamName = ?"
        .Parameters.Append .CreateParameter(, adVarChar, adParamInput, 4, tm)
    Dim rs AS ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open sp, , adOpenDynamic, adLockOptimistic
End Wit

edited Oct 25 '19 at 11:08

answered Oct 16 '19 at 12:07

Erik A

31,639
12
42
67

Thanks for this response @ErikA - just to give context on the security side, this was put together purely for the example above and I chose the word [redacted] rather than [server_name] or something similar, but thank you. Correct me if I'm wrong, but the ADO side works in a similar fashion to above, but instead of the command type being a SP, it uses the query text provided in the VBA code. With that in mind, will it not retrieve the recordset in the sameway and make it non-updateable when I set the recordsource to the table as a 'one-way' transaction? – RazorKillBen Oct 23 '19 at 13:13
@RazorKillBen That highly depends on the query. ADO recordsets allow you to poll if the dataset is updatable (`recordset.Supports(adUpdate)`). As a rule of thumb, recordsets selecting data from multiple tables are not updateable when you use SQL server, but there are exceptions – Erik A Oct 23 '19 at 13:19
the reason I ask is I have a static table no joins, that I'm pulling in the way you mention in the ADO answer [here](https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access/49509616#49509616) although I can't seem to fully replicate it by naming the parameter in the query you have. The recordset remains updateable as it sees the fields that are bound to be "based on expressions" rather than field data. – RazorKillBen Oct 23 '19 at 13:43
@RazorKillBen Can you either post a new question, or edit what you're currently trying into this question? Sounds odd to me. – Erik A Oct 23 '19 at 13:59
Thanks @ErikA - i've posted a new question as I think I may have come from this at the wrong angle. For future readers the thread is [here](https://stackoverflow.com/questions/58555243/how-can-i-parameterise-a-recordsource-query-to-ensure-the-data-remains-updateabl) – RazorKillBen Oct 25 '19 at 08:54
I'm inclined to close that as a duplicate of the question I linked here. The _Using DAO_ section outlines how to create a parameterized query. That should just work for you. Then, you can set the form recordset equal to your obtained recordset. If you have a specific attempt that somehow doesn't work, that'd make a good new question. But now it looks like you didn't actually try. – Erik A Oct 25 '19 at 08:59
I think my question was misunderstood. Your linked answer is very helpful for retrieving data as read-only, but by using a `RecordSet` like all the answers specify, it instantly makes the query not updateable, which is what I am specifically trying to do. I believe I need to use the `RecordSource` property instead but this doesn't allow a recordset to be used with it. – RazorKillBen Oct 25 '19 at 09:02
@RazorKillBen That's plain untrue. Recordsets are generally updateable when properly created. As said, please share an attempt that didn't work. – Erik A Oct 25 '19 at 09:03
I used the ADO example you've given in your answer and by naming the parameter in the second example, it says 'Must declare the scalar variable @' which I can't do as you rightly pointed out, ADO will not pass the named parameter. The code executes when using the `?` parameter name, but doesn't return any data when executing using the array command. I have updated my question with the failed example and commented out the failing parameter code also. – RazorKillBen Oct 25 '19 at 09:33
If you want to join tables and keep the set updateable, you need to use the DAO example, though. SQL server doesn't do updateable joins, but if you process everything in Access it should be fine. – Erik A Oct 25 '19 at 09:40
there are no joins in the query. When the data is returned using the ADO method above that you provided in your answer, the dataset is not updateable as it says "this field is based on an expression" for every single field. I will try the DAO one now. – RazorKillBen Oct 25 '19 at 09:48
same issue with the DAO I'm afraid. The data pulls correctly, and ends up in the table correctly but can't be updated as it's based on an expression. I think as I mentioned in the second question, this may be to do with the `Recordset` property instead of using the `RecordSource` property. – RazorKillBen Oct 25 '19 at 09:57
That sounds like you've bound your fields in the wrong way (e.g. using `=Fieldname` instead of `FieldName` to bind it). Anyway, I've edited in an updateable RS example, you need to define a few more details than you have now. – Erik A Oct 25 '19 at 10:06
thanks, so to give some more context, the `RecordSource` of the form is set to return a default query and the fields are bound correctly (not as '=fieldname'). The recordset returns the same fields and therefore when set as the RecordSource, pick up just fine. The issue seems to be using a Recordset instead as that seems to make the data almost 'read-only' by nature. I'm keen to resolve this, so if you can let me know what I need to do/try or provide, I'll happily do so. – RazorKillBen Oct 25 '19 at 10:21
Well, I'd need to be able to reproduce the issue. I know for a fact I've got similar code in one of my databases that has produced editable forms for years, filtering on a primary key and using an ADO query, so that can't be it. You can try the setup I outlined [here](https://stackoverflow.com/a/52763115/7296893) to easily display recordsets on a standardized form, to make sure it's an issue with the recordset, not the form. You're setting your cursor location on the connection to `adUseClient`, right, btw? – Erik A Oct 25 '19 at 10:26
Progress! The good news is, I've managed to do this successfully using the DAO.QueryDef answer you posted in your first response "Using DAO". Thank you for this. However as this project is ADO mainly, it'd be useful to have this working for ADO, and I cannot seem to do this using ADO in anyway - once the Recordset is applied it simply constantly recognises every field as an expression. I have edited my original question to show the two examples I used that do not work - are you able to see this? Yes the cursor location is set to `db.CursorLocation = adUseClient` – RazorKillBen Oct 25 '19 at 10:35
@RazorKillBen I did see your examples, and have revised one of them in my answer (some comments ago). You should try that one. – Erik A Oct 25 '19 at 10:38
Thanks @ErikA - so when using the `WHERE t.TeamName = @tmName` SQL code, it fails as it says I must declare the scalar variable @tmName. However! Amended the `.CommandText` to `WHERE t.TeamName = ?` and then doing this, does indeed work, and has returned an updateable recordset! I think another key spot you made here, was that the Recordset does need to be opened rather than executed, which seemingly has resolved the updateable issue also. Thank you so much, and I do genuinely appreciate you giving your time to get this over the line. If you want to edit the code to the `?` instead I'll accept – RazorKillBen Oct 25 '19 at 10:51

score 0 · Answer 2 · answered Oct 16 '19 at 09:20

0

It seems like your field ps_dateClaim is not a timestamp but a DateTime field, so try with:

sp.Parameters.Append sp.CreateParameter("@wkEnd", adDate, adParamInput, , wkEnd)

answered Oct 16 '19 at 09:20

Gustav

53,498
7
29
55

Thank you @Gustav - this code is working however. If I change the code to `adDate` it still makes the recordset not updateable, I assume because it's just retrieving it one way rather than it being 'linked'. Ultimately I'm trying to populate it with data but retain the ability to manipulate the data in the table directly. Hope that's a little clearer! – RazorKillBen Oct 16 '19 at 09:41
As far as I know, a recordset retrieved from a stored procedure is always read-only. To update, use a pass-through query. – Gustav Oct 16 '19 at 09:53
Yes that's exactly what I'm trying to achieve - do you mind showing me how my code could be turned into a pass-through query? – RazorKillBen Oct 16 '19 at 09:59
Sorry, I don't use ADO. But lots of code out there; it should be snap to bing/google on the keywords. – Gustav Oct 16 '19 at 10:03

How can I create an updateable recordsource for an MSACCESS subform datasheet with a parameterised query or stored procedure in ADO?

2 Answers2

Linked