Microsoft Access - ODBC - Call Failed [Microsoft][ODBC Driver Manager] Function sequence

What is this linked to?  Can you tell us a little more about the environment you are running this in?

Apologies.

A2007 links to Oracle tables.  The update part is to local (Access) tables, though they are linked to an Access BE.

This may be a timing issue.  The QueryDefs collection may not have gotten refreshed and so the second query isn't "seeing" the first.

Why are you creating querydefs on the fly?  It is far more efficient to use static SQL (saved querydefs) than dynamic (SQL strings in code that you either run or save as a querydef and then run).  In the vast majority of cases, the SQL string doesn't need to be dynamic at all.  It just needs to take an argument at runtime that provides a value to search for.  So, one time you run the query looking for companyID 487 and another time you are looking for 8892.  The structure of the query is the same, just the argument value changes.

I see this meassage has been out there for a while but I thought I would add
my 2 cents since I do this type of importing/exporting on a daily bases.

When ever I have this problem it's usualy because of a password change or a
server name change since I'm pulling from a clustered envornment. Try
updating your ODBC connections in your PC's administrative tool>ODBC set up
in windows. Update your password or server name then re-establish your link
again.

Hope this helps someone.

I wasn't aware of querydefs need for refreshing -- that suggests I might be able to stall the routine or call a refresh method to keep things rolling.

Regarding the approach, in the first place, I am adapting a legacy method which stores query strings in a table, and is liked by managment.  And by me, because I see that it's easily scalable from the perspective of the user.  Each query is unique - that's why I create them on the fly. Each sql statement is unique enough, that is, that finding a general case would probably still exclude a majority.  It's a tradeoff.  I understand that the best performance in overall process management -- including development time, maintenance and scalability, among other things, might call for a small sacrifice in speed, or vice-versa ad infinitum.  On the other hand, I also have an alternative method which does use fixed queries, which is faster (I posted the results a week or so ago) but which suffers something in the way of performance and convenience when a persistent ODBC connection fails.

I'll investigate the timing angle - - thanks for bringing it up. I thought maybe there was something about the second query being a local query, calling the first, which has an ODBC connection, somehow wigging something out.  But it works sometimes, others, not, so...

It also works consistently when the second query is a make-table query, which might lend credence to the timing theory, if making a table stalls things long enough for that refresh to take place....?

When I did a search on "querydefs refresh", I came across this, which seems to be a similar (no identical) issue. Unsolved, of course.

http://www.office-forums.com/dao-querydef-refresh-t680802.html

This bit about Jet "storing the connection in cache" and not being able to refresh it unless the whole app is taken down is pretty ominous. However, I'm using the same connection each time.  I wonder if Jet thinks each on is unique.  If so, then we're back to "persistent connections" again.  Oy.

And this seems to be close too but talk of "schemas" makes my eyes cross:

http://www.accessmonster.com/Uwe/Forum.aspx/databases-ms-access/42960/ODBC-Call-Failed-Weird-Problem

refresh didn't work...

This query functions in "View" mode, but not in "Run" mode...does that offer any clues?

[edit]

OK, for posterty's sake:

I'm going with this: http://www.utteraccess.com/forum/Function-Sequence-Error-t1799368.html&pid=1799742&mode=threaded

...because I know the query runs when it's a make-table query, but not when it's an append query, so...