One table is "mine", and it is linked from FE to BE (FE is on my "personal" network drive, BE is on a shared network drive). That table is 30accounts. The other table in the query is just a link odbc view to my company's data warehouse.

I'm wanting to run an update query every day, and just catch records that have a CLOSE_CODE of "1", (in data warehouse), and have a Null value for UserNotified and PaidOff (in my table), and update those records in my table.

I get an error about needing an updateable query. But I'm not trying to update a field in the data warehouse table ??

Unfortunately, Access has a habit of considering queries to be non-updatable if any part of the query isn't updatable.

Try:

Doesn't matter Isaac, if you connect your table to a non-updateable query the whole thing will tend to become unupdateable. Some ways around it involve Left and Right joins. Try making the join so it returns all records from the main table and only those that match from the locked one and see if it lets you update anything.

Thanks to both of you. It sounds like I could benefit from learning sub queries, typed all in SQL and not the design view.
Darn you, Design Grid! ... My addiction to your drag-and-drop laziness has long kept me from learning SQL !!

Don't forget to learn about Left and Right joins too! They can help and take less processing power (I think) than sub queries. That being said make sure you do learn sub queries at some point.

This is also interested to me because it seems like there is a enormous overhead cost in this approach (just in my particular situation I mean). The subquery is a pretty large affair - probably returning a couple hundred thousand records at least. It seems like for Access to process all of that, and then process the second part of it is taking forever.

Whereas, if I just did a simple select query with an inner join on the 2 tables, it runs in about 10 seconds.

So I am thinking that a simple block of VBA code to do a sequence like:

1. Maintain a table tbl_TEMP
2. Delete all records from tbl_TEMP
3. Maintain a tbl_NOTIFIED
4. Run select query against datawarehouse
5. Append that select query to temp table
6. then append from temp table into tbl_NOTIFIED table, only where the record isn't already in tbl_NOTIFIED

might be faster. I know it seems fragmented, jumbled and rough, but this is part of why I keep learning more VBA but not relationships and SQL. It is frustrating. In this case no matter what I do, the time it would take this query to run is just not workable, so I go for more off-the-wall thing using VBA.

I am familiar with left and right joins, at least in the design grid. But yes - definitely all of it and using sql, I will work on it.

If you want a good book on SQL, look for SQL Queries for Mere Mortals, 2nd Edition, by John L. Viescas and Michael J. Hernandez (ISBN No. 0-321-44443-4)

I will, appreciate the tip!

Have you actually tried changing your inner join to a left or right join though? That can often make a non-updateable query updateable. No fuss, no bother, no code.

During today, I started to realize that I needed to code it anyway (I actually kind of 'prefer' code in certain instances if that make sense? I find it sometimes easier to troubleshoot than queries which is sometimes guesswork..) because I wanted to change the process a little (run a Dcount on some select queries first, then possibly give user option to Update), so I adjusted the entire process a little bit.

I will keep this in mind for next time though. thank you for the tip!