Problem with read-only ADO recordsets containing joins (1 Viewer)

Jaspos

New member
Local time
Today, 10:52
Joined
May 19, 2017
Messages
2
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!
 

static

Registered User.
Local time
Today, 09:52
Joined
Nov 2, 2015
Messages
823
Recordsets are only updateable if the joins are primary keys.
 

Minty

AWF VIP
Local time
Today, 09:52
Joined
Jul 26, 2013
Messages
10,353
It is normally easier to simply link to the SQL tables / views you want and do the querying and joining in Access unless you have a reason for not doing this?

You will still have the issue that only certain types of Join will give you an update-able recordset.
 

Jaspos

New member
Local time
Today, 10:52
Joined
May 19, 2017
Messages
2
To confirm, for the simple tests I have done the joins are definitely on PKs, but they remain read only in recordsets.

Minty - I am unsure how I would link to SQL tables and do the joins in Access - do you mean set up all the linked SQL tables, and then write Access queries that do the joins? My concern here is that I may end up pulling ALL the data from the SQL tables to do the necessary processing each time. Ideally I'd like to do ALL processing on the server and minimise Jet as much as possible purely to minimise data transfer.
 

Users who are viewing this thread

Top Bottom