ODBC connection fails
Posted on 2014-02-02
I am having trouble with an ODBC connection to my front-end.
We receive orders on Excel spreadsheets, which my code imports to a SQL Server back-end.
The Excel code then opens an Access front-end which has ODBC links to the SQL tables but also to our accounting package (Sage). This front-end presents the order details to the user for them to check over. We use the link to Sage to check the customer's credit rating before completing the order.
The ODBC link to Sage was set-up using a DSN called "SageLink".
The link to Sage sometimes fails - "ODBC error - SageLink failed", but if we keep re-trying it eventually connects.
I have tried to debug this today and found:
- Opening the table directly failed
- Refreshing the link failed
- Trying to recreate the link failed
- When I closed the database and re-opened it worked first-time
Most of the time the SageLink works perfectly, so can anyone suggest what I should do to prevent this failure.
This is driving my users nuts, so any help would be really appreciated.