Access 2003 - ODBC --Call failed,

Zane80
Zane80 used Ask the Experts™
on

I use Access as a front end for reporting out of our ERP system. I have a load of tables setup via ODBC which connect to the data and help me run reports.

Recently I have been recieving the error:
ODBC --Call failed
          OK
MS Access cant open the table in datasheet view
          OK
A temporary solution I have found to this problem is to right click on a table --> Linked table manager and update the tables I plan to use. This will only work for a few minutes and I will start receiving this error again.
This solution also wont work for end users as all they do is click on the report they were after (Then complain when they recieve the "ODBC Call Failed" error)

I could also take copies of the tables (speed up report speeds) but this is only possible in certain situations (when the user doesn't require live data)

I have setup some software called Dataflex to access the information from our ERP system.
Under Administrative Tools --> Data Sources(ODBC) --> I have setup a datasource DataFlex Data 32 using hte dataflex driver linking to the path: k:\cba2000\data;k:\cba2000\data\co1
Dataflex has been working perfect for the last 4 years or so and I have no problem using CBA to access these files
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
when you link the tables, make sure uncheck "always prompt for new location"

Author

Commented:
That option is not available when linking tables.
It is only available when going to linked table manager to refresh the data.

Talking to one of my colleagues he thinks maybe linked to a very intermittent network error where the network is disconnecting for a short period of time and reconnecting without me knowing.

This makes sense in theory but I am not sure if it is correct and I am not sure on how I would test this

Commented:
if you think that network disconnection issue, can you ping the network path to see it is a case.

also, you can set how often you wan to refresh ODBC connection,  go to tools-->options-->Advanced.

Author

Commented:
refresh ODBC connection,  go to tools-->options-->Advanced is a good idea. I will test it now to see if it works.

The network issue is a theory from someone in my workplace - An unprooven theory.
If it does disconnect it is only for a very short period of time and apart from this error there is no other noticeable symptoms

Commented:
I've gotten this error in the past when it wasn't a connection issue, but more of a busy issue. You could try setting the ODBC Timeout in the query properties to 0, so that there is no timeout period.

Author

Commented:
After a bunch of googling to find how to do this in access 2003 it has not helped.

Thank you for the suggestion though.

Commented:
you can create a function to dynamic relink the tables when users open the database, that's way you can have referesh conection to link tables when open database.  

Author

Commented:
How would you suggest is the best way to do that?

Commented:
Currently I am not by pc
if you can to tomorrow I can create some examples for u

Commented:
I mean if u can wait tomorrow. I can send u examples

Author

Commented:
Okay, thankyou. I am currently copying the full table of live data to an offline table before i do each query... it takes a long time to update :)
Commented:
See my database example.

- Open the database.

1) Manually link your tables

2) In Relink modules,
   * you need to update your DSN_Name, user_id, password in the line below:
  DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=DSN_NAME;uid=USER_ID;pwd=PASSWORD;database=DSN_NAME;server=" & DSN, acTable, Source_Table_Name, Temp_Table_Name, , StoreLogin:=True

   * also, you need to change Current_dir to your database path.

3) That's all you have to do.

4) To test it, you can right click on Relink Macro, and click "Run

it works for you, you can apply this macro into your project. Maybe when users open database, you can add to run the relink Macro; just suggestion

Commented:
see an attachment
relink-example.zip

Author

Commented:
It is a good alternate solution of solving a problem which has no definite symptoms and is very idifficult to solve. I thank  pdd1lan for going out of his way to help out

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial