0

I have a subform within a main form, that is set to datasheet view and extracts data from a SQL Server database, based on the forms supplied parameters.

This means that selecting a different team from the combobox or date from the datepicker, pulls the relevant information into the datasheet.

The user needs to be able to manipulate a boolean field within the data, and so far the only way I have found that I can make this data load and keep it updateable is to write the query in the .RecordSource property in VBA fully like below:

Set mf = mainFrm
S = " SELECT t.Date, t.Team, s.Username, t.Reference, t.Status, t.Reason, t.Completed " & _
    " FROM [ODBC;DRIVER=SQL Server;SERVER=<SERVERNAME>;Integrated_Security=SSPI;DATABASE=<DBNAME>].testTbl as t " & _
    " INNER JOIN [ODBC;DRIVER=SQL Server;SERVER=SRVFOSABESQL01;Integrated_Security=SSPI;DATABASE=MO_Productivity].staffTbl as s ON t.emp_id = s.emp_id " & _
    " WHERE t.Team = '" & tmName & "' AND t.Date = #" & wkEnd & "# " & _
    " ORDER BY t.Reference; "
mf.subFrm.Form.RecordSource = S

Obviously the huge issue here is that the provided variables are open to SQL injection, aren't going to be escaped and is obviously exposing the connection string. All of which can be worked around, but certainly doesn't feel 'best practice'.

I have tried using the .Recordset property to get data from the server with a pass-through parameterised query / stored procedure, but this seems to be a one-way read-only operation of placing the data in and making it 'unlinked' or not updateable.

What is the correct and more secure way to retrieve this data from SQL Server so it can be placed in the RecordSource to make it updateable?

braX
  • 11,506
  • 5
  • 20
  • 33
RazorKillBen
  • 561
  • 2
  • 20
  • I believe that `JOIN`ed data source in MS Access can be read-only. The `JOIN` between tables could cause your issue if the joins don't take place on keyed fields – Martin Oct 25 '19 at 09:04
  • 1
    Not sure what is meant by 'any' JOINed data source is read-only. I am certainly able to edit data in many queries that are joining tables. But those are local tables or links to Access backend, not connections to SQLServer. Is that what is meant? – June7 Oct 25 '19 at 09:08
  • 1
    [Relevant prior question](https://stackoverflow.com/q/58409734/7296893). You can generally parameterize a query using DAO as linked in my answer there, and it should remain updateable if it was updateable using string concatenation. – Erik A Oct 25 '19 at 09:11
  • I have tried this with a non-joined table @Martin and unfortunately, it doesn't work as it recognises any data as an "expression" field an therefore can't update. I think this is what happens when setting the `Recordset` property of the subform rather than the `RecordSource` property, but you seemingly cannot assign a ADO or DAO Recordset to a `RecordSource` either. – RazorKillBen Oct 25 '19 at 09:59

1 Answers1

1

I found this on fmsinc.com:

There are many reasons why a Query or Recordset may not be updateable. Some are pretty obvious:

  • The query is a Totals query (uses GROUP BY) or Crosstab query (uses TRANSFORM), so the records aren't individual records
  • The field is a calculated field, so it can't be edited
  • You don't have permissions/rights to edit the table or database
  • The query uses VBA functions or user defined functions and the database isn't enabled (trusted) to allow code to run

Some reasons are less obvious but can't be avoided:

  • The table being modified is a linked table without a primary key. For certain backend databases (e.g. Microsoft SQL Server), Access/Jet requires the table to be keyed to make any changes. This makes sense since Access wants to issue a SQL query for modifications but can't uniquely identify the record.

Less obvious are these situations:

  • List item
  • Queries with some summary fields linked to individual records and the individual records still can't be edited
  • Queries with multi-table joins that are not on key fields
  • Union queries

If the query is updateable, then the recordset must be updateable. I solved this by using ADO object.

See MS Access 2019 - SQL Server 2017 - Recordset cannot be updated here on Stack Overflow.

ComputerVersteher
  • 2,638
  • 1
  • 10
  • 20
andarvi
  • 110
  • 1
  • 9