 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008Updatable passthrough queries
Is it possible to make a passthroguh query updatable? I think that it normally isn't because it doesn't know the primary key, but is there a way to specify it? I'm not too sure what you're trying to do, but if I need to manipulate the result of a pass-through query, I'll store them in a temporary table. The code to do that is simple enough: <code> DoCmd.RunSQL "INSERT INTO [QryName Temp] SELECT * FROM [QryName];" </code> I'm no expert though. Joe > Is it possible to make a passthroguh query updatable? > > I think that it normally isn't because it doesn't know the primary key, but > is there a way to specify it? > Is it possible to make a passthroguh query updatable? > > I think that it normally isn't because it doesn't know the primary > key, but is there a way to specify it? No.  SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Rick, Are you talking about an ADP? I've got an mdb open at the moment. just created a PT query to a SQL Server database and ran the query. Then I saved it with the connection string and password. I then wrote a couple of lines of code to change the querydefs sql property (as indicated in my earlier post), and ran that code. It successfully changed the SQL of the query. SignatureDale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. > > Is it possible to make a passthroguh query updatable? > > > > I think that it normally isn't because it doesn't know the primary > > key, but is there a way to specify it? > > No. > Rick, > [quoted text clipped - 5 lines] > (as indicated in my earlier post), and ran that code. It successfully > changed the SQL of the query. Dale, I am not looking to update the query definition. I am looking to update the data on the server that I am accessign through a passthrough query. Jacob Jake, I think I understand, you have a select query that returns values to a bound form or some other form of recordset, and you want to be able to change the values that are returned by that query in your form? Is that correct? If so, I would defer If so, why aren't you using linked tables? You can create links to the SQL Server tables and just use the linked table in your bound forms. You could also use unbound forms, and with very little effort write a query that issues an UPDATE command to the backend database when you change records (after checking to determine which fields have changed). SignatureHTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. > > Rick, > > [quoted text clipped - 10 lines] > > Jacob > Jake, > > I think I understand, you have a select query that returns values to a > bound form or some other form of recordset, and you want to be able to change > the values that are returned by that query in your form? Is that correct? > If so, I would defer yes, exactly. > If so, why aren't you using linked tables? I am using linked tables now. However I am trying to get away from them because I need to compile it into an mde and I have too many objects to do so. so, I am trying to reduce the number of objects. > You can create links to the SQL Server tables and just use the linked table > in your bound forms. You could also use unbound forms, and with very little > effort write a query that issues an UPDATE command to the backend database > when you change records (after checking to determine which fields have > changed). yes, that is also a posibility. I don't know that I agree about it being very littel effort. >> Jake, >> [quoted text clipped - 10 lines] > them because I need to compile it into an mde and I have too many > objects to do so. so, I am trying to reduce the number of objects. Hmm, One table link versus one passthrough query is the same number of objects isn't it? SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com > Rick, > [quoted text clipped - 5 lines] > property (as indicated in my earlier post), and ran that code. It > successfully changed the SQL of the query. I'm assuming by "updatable" that the OP wants to update the data in the result set, not the SQL of the querydef. SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Jake, Have not done this in quite a while, but as far as I can remember, there is nothing that prevents the passthru query from being updateable. Try something like: Dim qdf as DAO.Querydef set qdf = currentdb.querydefs("PT_query_name") qdf.sql = "SELECT Field1 FROM yourTable" qdf.close then use the query SignatureHTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. > Is it possible to make a passthroguh query updatable? > > I think that it normally isn't because it doesn't know the primary key, but > is there a way to specify it?
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.