ODBC Connection/Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please forgive my ignorance if I say any of this wrong... I was in the wrong
place at the wrong time and managed to get myself in over my head in
programming this database. My front end is working great, but there are so
many obstacles on the backend, I feel like I'm drowning.

I am using linked tables (which I have no control over) through an ODBC
connection. I cannot even find the person that has any knowledge or control
over these tables, so I have to use them as is. Trying to find the most
efficient way to get the information from their place to mine. Using the
tables directly isn't an option because the connection is incredibly slow. I
figured replicating the tables locally with append query would work, but when
I run the query on these tables I get "ODBC Connection Failed". I can open
the tables with no problem, the first time I enter my login/password and
voila, open table. Thought maybe I had to connect once to a table to enter
password would work, but no. Even after entering my login/password I cannot
go back and run the query, still get "ODBC Connection Failed".

My problem in a nutshell is that I don't know the best way to get this
linked data, which I have no control over, to a place that I can utilize it
efficiently. I have to run updates on this data daily. I can run these
updates overnight, which is what I'm trying to do by using queries that are
run from a macro. The problem now is that if I can't be sure the queries
will work, this method isn't an option.

I'm open to any suggestions to make it work. If I'm going about this wrong,
please tell me.
 
Is it possible that your ODBC connection timed out in your query? In query
Properties, check what ODBC Timeout is set to. Changing it to 0. Will
prevent the timeout.
 
Chris, thanks so much for your response!

I just tried setting it to 0 with the same results. The message comes back
instantly, like it's not even trying to connect. I'm not sure if this means
that it's something on my side, or if the connection has just ceased to
exist. A little more oddness to this situation... I have three queries that
I run, all tied to these linked tables. This morning, I tried to run them
from the macro, no luck. Opened one of the tables on it's own, logged in, it
opened no problem, went back and ran one query on it's own, no problem. Ran
the second query, no problem. Third query, I'm getting the ODBC Connection
Failed error. Is it possible that something in that third query would be
keeping the ODBC connection from working? Each query is using a different
table.

Meredith
 
Meredith,
Can you open the third table on its own? If not, you may need to delete
the table and re-link it to your database.
 
Back
Top