Please excuse my lack of poper terminology when describing my issue to you, as I am very new to databases.
I have set up a SQL Server database which connects via MS Access. The application works fine, except when trying to connect through VPN.
Through System DSN, I changed the server name (FSERVER) to the server IP address, and test completed successfully. I am also able to ping the server when connected via VPN. However, when I try to start the application, I receive the following error after several seconds:
ODBC-- call failed.
That's it. No other information about the error. I read somewhere to make sure the MDAC versions are the same, which they are.
As I say, I am quite new to this, but would really appreciate any guidance to help me through.
Without knowing quite a bit about your network, I can't give you a 100% sure-fire answer. My first guess would be that the DSN is set to use some protocol other than TCP/IP in the DSN Client Configuration and your VPN doesn't route that protocol. My next guess would be that your Network/VPN aren't configured to cooperate for name resolution.
Getting more error information than just "ODBC-- call failed." would help. That's not much for me to go on!
That's a hard question to answer, it just begs for a smart-alec answer and that wouldn't help you at all.
The problem is most likely in the application/script itself. It apparently does a very poor job of reporting errors, and fails to give you critical information that would help you diagnose and fix this kind of problem.
I'd suggest that you start by solving a simpler problem, then use what yo learn from that process to help solve this problem. Use another application (with better diagnostic information) to open the ODBC DSN while using VPN. It should give you more information about the failure, which will help you (or us) to resolve the actual problem.
I'd start by using Excel, and use the Data | Import External Data | New Database Query menu item. This will allow you to open the DSN, which will probably throw some kind of error immediately. Just work through the process until you get an error message that tells you why the connection fails. I'd bet that the same problem is affecting your custom application, and once you fix the problem for one application, you'll fix it for them all.