July 15, 2009 at 2:27 pm
I don't know if this is the right forum group, forum, web site for this question as it is a Front End (user interface) question.
I use MS Access as my front end.
The back end is SQL Server. Within SQL Server I have a database with tables, views, etc... as weill as a Linked Server to an IBM iSeries (formally known as AS-400) with our Entprise Software running.
If I link Access to both a SQL Table and a SQL View (which is in reality an OpenQuery to the iSeries) everything displays perfect.
BUT Access will not let me edit the SQL table from the Access Query because it requires both tables to be writable. Only the SQL table is, the iSeries is not.
I tried creating a new view joining both tables and it worked fine in Server Management Studio allowing me to change the data. When I linked to it in Access... no joy!!! Viewed okay with no problems. Update, no good.
How do other people solve this problem???
If this is not the right place for this question any ideas where I should look?
Thank you,
July 20, 2009 at 6:31 am
Sorry about bumping this up.
Does anyone else have this problem?
How do they solve it?
Thank you,
July 20, 2009 at 9:34 am
Are you trying to update the records directly from an Access form or grid? Typically what I do is have some unbound text boxes on a form, then with a command button, run a DoCmd.runsql to run your UPDATE statement. I am thinking if your SQL Server can do the update in EM, then this method should work for you. One word of caution, make sure you are not testing on production data, as I have updated entire tables before where I meant to only update one record.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 20, 2009 at 12:09 pm
I use both form and datasheet.
I can see how this might work with a form. I could either have one update statement for each text/check box for one for the entire record. I would have to leave the objects unbound and use the On Current event which will slow that data retreival. But this could work.
However, I am not sure how this will work in a datasheet. In a datasheet if I leave the objects unbound that all of them will show either nothing or identitical data (using the On Current Event).
If I bind them then, because they are not updatable, the user will not be able to enter any data into the text boxes.
I would love to be able to use this in both datasheets and single form view. Am I missing something?
Thank you,
July 20, 2009 at 1:11 pm
Ken, if you have to use datasheet view, you may have to leave it locked, in a subform, then on a different form, possibly a popup, have some unbound text boxes that can be referenced by the DoCmd.RunSQL statement. You can also format continuous forms to look like a datasheet, but you would be able to have an "Update" button on each record. Are you updating more than one record at a time? I would recommend you check out AccessMonster.com for some Access specific help. You can feel free to send me a message, since we seem to be deviating from the scope of the forum.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply