AutoExec Macro problem with ODBC connection failure

[Guest]

I have an autoexec macro that exports 2 different queries using TransferText
to .txt files. The queries are built on tables that are linked via ODBC to
outside product. The first query always transfers/executes right away. The
second one fails and I get the ODBC connection-failed message. However, if i
run the macro 2-4 more times, it eventually does run and complete. What is
my problem? If I can get it to run by itself, i would then like to get it to
run automatically on Timer or Scheduler once a day.

Thanks,

 

[Guest]

Hi,
maybe the odbc connection needs to be refreshed at start up. Try to do that
manually and if that makes the export work right away you could implement
code like this to automatically do the refresh:
http://www.mvps.org/access/tables/tbl0010.htm
Then to schedule this with the scheduler check:
http://support.microsoft.com/?kbid=230575

HTH
Good luck

 

[Steve Schapel]

Melissa,

What happens if you try (just for an experiment) reversing the order of
the two TransferText actions?

It may be best to import the data from the external tables into
temporary tables in the local mdb first, and then base your queries for
export on this local data. Especially if the queries involve joined
tables, or include calculations or concatenations or the use of functions.

There may be a timing issue here as well, i.e. the macro is trying to
start processing the second query before the first export has been
completed. If so, we could look at putting a delay between them.

 

[Guest]

What do you mean by manually refreshed? I have tried having the macro open
and close one of the linked tables, and I've had it open and close the query.

Thanks again,
Melissa

 

[Guest]

Thanks, Steve. I'm going to try resequencing first before the temp tables.

Melissa

 

[Guest]

Steve,

I have tried reversing the order but to no avail. Next I tried converting
the 2 queries into make table queries and exporting from there. I still get
an ODBC fail message but this time there is more detail. It says [Informix
ODBC Driver]Timeout expired (#- 11094).....Statement interrupted by
user.sqlerrm (obs_result) (#-213). What does this mean? Is there a
reference for these error messages on this site?

Thanks,
MelissaS

 

[Steve Schapel]

Melissa,

By "to no avail", do you mean the 1st one (originally the 2nd) didn't
work and the 2nd one (originally the 1st) did?

If you structure the query as a simple Select Query based on the ODBC
linked data, does the query datasheet show the expected data?

Anyway, I'm afraid I don't know anything about that error message or how
to overcome it. It may be worth posting to the
microsoft.public.access.externaldata and the
microsoft.public.access.odbcclientsvr newsgroups, you will catch people
over there with more experience with this sort of thing.

 

[Guest]

Yes, the 2 actions continue to work the same as before, ( one succeeds and
the other fails w the ODBC fail mssg) regardless of the order in which they
are performed. They both do open up as select queries and show appropriate
data. And as always, if I continue to halt and rerun the macro it does
succeed on the 3-4th attempt. You mentioned pausing the macro before. How
is that accomplished?

Thanks,
Melissa

Melissa,

By "to no avail", do you mean the 1st one (originally the 2nd) didn't
work and the 2nd one (originally the 1st) did?

If you structure the query as a simple Select Query based on the ODBC
linked data, does the query datasheet show the expected data?

Anyway, I'm afraid I don't know anything about that error message or how
to overcome it. It may be worth posting to the
microsoft.public.access.externaldata and the
microsoft.public.access.odbcclientsvr newsgroups, you will catch people
over there with more experience with this sort of thing.

--
Steve Schapel, Microsoft Access MVP

Steve,

I have tried reversing the order but to no avail. Next I tried converting
the 2 queries into make table queries and exporting from there. I still get
an ODBC fail message but this time there is more detail. It says [Informix
ODBC Driver]Timeout expired (#- 11094).....Statement interrupted by
user.sqlerrm (obs_result) (#-213). What does this mean? Is there a
reference for these error messages on this site?

Thanks,
MelissaS