Updatable passthrough queries

[Jake]

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?

 

[Jezza0]

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

 

[Rick Brandt]

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.

 

[Dale Fye]

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

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

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.

--
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Jake]

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.

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

 

[Dale Fye]

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).

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Jake]

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.

 

[Rick Brandt]

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.

I'm assuming by "updatable" that the OP wants to update the data in the
result set, not the SQL of the querydef.

 

[Rick Brandt]

yes, exactly.


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.

Hmm, One table link versus one passthrough query is the same number of
objects isn't it?