Hi all, hoping some of the experts here might be able to help
I am taking my first foray in Access with SQL Server as a back end. I am currently in testing, with my aim of moving most/all of the data processing to SQL Server rather than Access/JET.
The approach I am currently trying is binding Access forms to an ADO recordset from SQL. This is an approach that it at least partially recommended by Microsoft as well as by some users on this forum
(I would post links but currently I am not allowed!)
Now, I can get this to work fine, but I have noticed that, as soon as the query for the recordset has any joins, the recordset/data becomes read-only (but to re-iterate, it is fine if there are no joins).
Note the problem is definitely NOT with the query not being updateable. I have tested a simple query with joins – one way as an ADO recordset, and secondly with the query as a SQL Server view used as a linked table in Access. And the identical query IS updateable when the form is bound to the linked table, but not when the same form is bound to the same query as an ADO recordset.
Note also I have tried several different ways of getting the ADO recordset (eg using a SQL stored procedure, using SQL text etc) and I’ve also tried different providers, all with the same result – it works fine until I have a join, then the recordset becomes read only.
So, is anyone aware of making an ADO query with joins updateable when the recordset is bound to an Access form (and if so how)?
FYI, I am more than happy to post my ADO code if it helps, as well as the SQL queries if needed.
Thanks in advance for any pointers. Happy to consider other approaches, but ideally I am looking for:
- Access forms bound to SQL data
- editable data (so passthrough queries are out)
- all processing done on the server where possible
Please note I had previously asked this question on a linked-in group, but the responses I received all suggested using an ADO recordset as the source for a bound form is not a good idea (sorry again, cannot post the link)
Finally please note I am not a professional dev, I develop Access and Excel solution (and now trying SQL Server) for my own (non-IT) business (amongst many others roles), so please bear in mind I may not as technologically savvy as many users!
I am taking my first foray in Access with SQL Server as a back end. I am currently in testing, with my aim of moving most/all of the data processing to SQL Server rather than Access/JET.
The approach I am currently trying is binding Access forms to an ADO recordset from SQL. This is an approach that it at least partially recommended by Microsoft as well as by some users on this forum
(I would post links but currently I am not allowed!)
Now, I can get this to work fine, but I have noticed that, as soon as the query for the recordset has any joins, the recordset/data becomes read-only (but to re-iterate, it is fine if there are no joins).
Note the problem is definitely NOT with the query not being updateable. I have tested a simple query with joins – one way as an ADO recordset, and secondly with the query as a SQL Server view used as a linked table in Access. And the identical query IS updateable when the form is bound to the linked table, but not when the same form is bound to the same query as an ADO recordset.
Note also I have tried several different ways of getting the ADO recordset (eg using a SQL stored procedure, using SQL text etc) and I’ve also tried different providers, all with the same result – it works fine until I have a join, then the recordset becomes read only.
So, is anyone aware of making an ADO query with joins updateable when the recordset is bound to an Access form (and if so how)?
FYI, I am more than happy to post my ADO code if it helps, as well as the SQL queries if needed.
Thanks in advance for any pointers. Happy to consider other approaches, but ideally I am looking for:
- Access forms bound to SQL data
- editable data (so passthrough queries are out)
- all processing done on the server where possible
Please note I had previously asked this question on a linked-in group, but the responses I received all suggested using an ADO recordset as the source for a bound form is not a good idea (sorry again, cannot post the link)
Finally please note I am not a professional dev, I develop Access and Excel solution (and now trying SQL Server) for my own (non-IT) business (amongst many others roles), so please bear in mind I may not as technologically savvy as many users!