Why is my recordset not updatable?

[Waynest]

I'm using access 2000 as a front end to SQL server.<br><br>I've got a number of tables containing brand, style, shade codes which are used to fetch descriptions from corresponding brand, style, shade tables thru joins in my queries.<br><br>Its not possible at present to enforce integrity between the tables (I've got to import all the data from an old application) so my queries mostly contain left outer joins so that I get all the records in say, an order table, regardless of whether there are missing records in the brand, style, shade tables.<br><br>My problem is that when a query contains an outer join my form displays 'recordset not updatable' whenever I try to change the data.  If I change the joins in the underlying query to INNER then the recordset becomes updatable but it doesnt contain the records with missing brand, style, shade codes.<br><br>Can I make the recordset updatable or is my database design at fault?<br><br>

 

[DougP]

I have found that working with SQL server you can't use your "normal" Access methods. Yes you can see data in a tabla nd update or make changes.<br>But SQL server is picky about mixing tables together and so forth.<br><br>Everthing has to be done in VBA code.<br>ADO is preferred today.<br><br>Thats my experience with it so far.<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[cmmrfrds]

In properties for the form under data, there is an option for dynaset inconsistent updates that may allow you to update the data. I think this is risky and it would be better to loop through in VBA code and update the data you want that way you have exact control over the data.<br><br>Jerry

 

[Waynest]

This is a .adp project, you dont get the dynaset stuff.&nbsp;&nbsp;I'm pretty miffed about this, the whole point of using access was because I thought it was&nbsp;&nbsp;quicker to develop in than VB.&nbsp;&nbsp;I seem to be writing more and more code to get around things you would expect access to be able to do but doesnt.&nbsp;&nbsp;No doubt these problems are all specific to SQL server so ignore my whinging if you're developing mdb apps.<br><br>If this is of use to anyone else, I'm going to stick with inner joins so I dont have to code up all the forms, and use a stored procedure to create the missing brand, style, shade records.&nbsp;&nbsp;Orders data is imported daily so I can run the sp&nbsp;&nbsp;until I get the integrity sorted out properly, but this approach wouldnt cut it for a real time app, but you could achieve the same thing with triggers I suppose.

 

[CarlyA]

I link my tables through an ODBC connection and encountered a similiar problem once.&nbsp;&nbsp;I learned that when linking SQL tables to an access 97 front-end, if SQL prompts you to choose a unique field -- choose one.&nbsp;&nbsp;I didn't, even though I had one, and the table was not updatable in Access.&nbsp;&nbsp;Once I chose the unique field, viola - problem fixed.&nbsp;&nbsp;

 

[jimmythegeek]

Doug is right. The most efficient way to add and edit data is ADO. All your writing to SQL Server should be done through ADO. <br><br>We had 110 user system with an Access Front-End, SQL Server back-end, and had wonderful performance. Everything was unbound, and all writing to tables was done through ADO. All reading of data was done through either ADO or Pass Through Queries. We did not link ANY tables, as this defeats the purpose of having SQL Server doing your processing instead of the Jet Engine. <br><br>We also used Stored procedures when needed, but tried to keep all the code and functionality in the Access Front-End. If your app is unbound, and uses the ADO and PT Query technologies correctly, you should have no problem with adding, editing and viewing data, and your performance should be like night and day from an Access Back-End.<br><br>Don't know if this necessarily answers your question, but don't give up on an Access/SQL Server app, it can be a great product.<br><br> <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development

 

[Waynest]

Eh?&nbsp;&nbsp;What's Jet got do with anything?&nbsp;&nbsp;The connection to the database is through OLE DB for SQL Server.&nbsp;&nbsp;As I understand it you can access data programatically using ADO but ultimately you are still using OLE DB.&nbsp;&nbsp;I cant see how populating forms through code instead of by defining a query record source would give any performance advantage.&nbsp;&nbsp;Sure, there'll be times when a form gets so fiddly it'll be better to manage it with code, but it would be time consuming to do this by default.&nbsp;&nbsp;My gripe is just that Acess seems to have been developed so that recordsets derived from queries with any type of outer join are non-updatable, period.&nbsp;&nbsp;