How do I update a Table from a Pass-Through Query

[smc678]

I have a Pass-Through Query from a ODCB Connection. I need to take those results and update them to a table. How do I do that?

[Dal Jeanis]

You need to be as specific as possible in order to get the most useful help.

I'm going to assume that you have a query called qryMyODCBStuff that is returning some fields, and among the fields you're getting back are MyKey, MyNewName, and MyNewPhone.

I'm going to assume that you have a table called tblMyStuff, which has fields called TheKey, TheName, and ThePhone.

You can execute an update query that looks like this:

UPDATE tblMyStuff AS T1, qryMyODCBStuff AS Q1
SET TheName =  Q1.MyNewName,
    ThePhone =  Q1.MyNewPhone
WHERE T1.TheKey = Q1.MyKey;

There may be a reason you can't just bang them all together like that, and instead have to read the recordset and process each record in VBA, but that amounts to just coding an SQL string to update each one using the variables that you've set in the VBA loop. If you need a pointer to an example of that, please ask. There are usually about three examples a day in one thread or another.

strSQL = _
    "UPDATE tblMyStuff AS T1" & _
    " SET T1.TheName =  " & txtMyNewName & _ 
    ", T1.ThePhone =  " & txtMyNewPhone & _
    " WHERE T1.TheKey = " & longMyKey & " ;"

[smc678]

Thank you for your response. This is the first time I have messed with a pass through query. It is an SQL query that hits the back end of a corprate server. When I run the query it displays all the info I need to run my database off of. I just dont know the best way is to capture that data and put it into a table once the query has ran.

[Dal Jeanis]

The very best way to do anything in Access is this - "the way that you understand."

You can use that data pretty much any way you want. If you have really complicated things to do with it, you could insert it all into a local temporary table, and process from there. Or you can open the query as a record set and process it one record at a time. Or you can just use it as a source query in a query.

Pretend for a moment that it was a local table. What would you do then? How would you have to change that method to use this query?

Pretend for a moment that you could only get one record at a time from a table. what would you do then? How would you have to change that method to use this query?

Don't get intimidated by it, it's just a query. If the faucet looks a little funny, you can still get your water from it.

[vincent-leeway]

I am new into this forum, was not familiar with 'pass through query', now leaving this thread more enlightened. I am sure after few weeks into this forum, I will come across many such gems.

[Dal Jeanis]

Vincent - Welcome to the forum.

"Pass through query" means that Access is feeding the SQL to another database application - "passing it through" to be handled - and receiving back the results. This means that the SQL that is passed through may not conform to the Jet engine SQL standards - it has to conform to the other database's, whether that's dBase or Oracle or MySQL or whatever.
http://www.techonthenet.com/access/t...h/basics01.php

You can read the results as a query, or you can create (or append to) a table from the results.
http://office.microsoft.com/en-us/ac...005188053.aspx

A Pass through Query is not updateable, so if you heed update capability at the far end, you should consider using a "View" instead. You'll have to make sure that it provides access with a unique key for the desired update.
http://social.msdn.microsoft.com/For...hrough-queries

[vincent-leeway]

Thanks Dal! pass through query appears to be a great way to integrate Access with other more advanced databases. I think this has the great potential for the small and mid size businesses to better database systems, with out disturbing existing ones with access.