Did you duplicate the SOTAMAS90 DSN fully, including the Options tab?

With Advanced, are you using the CSODBC service?  If so, did you set up your DSN to use the service (Server tab)?

Did you try enabling ODBC logging to see if it gives a more detailed explanation on why the issue occurred?  

Steps to Enable ODBC Logging:

1. Open the ODBC Data Source Administrator:

   • Press Win + R, type odbcad32, and press Enter.
   • This opens the ODBC Data Source Administrator. If you're using a 64-bit system, you’ll see both 32-bit and 64-bit versions. Choose based on the application you’re troubleshooting.

2. Enable Tracing:

   • In the ODBC Data Source Administrator, select the "Tracing" tab.
   • You’ll see options for ODBC Tracing.
   • Click Start Tracing Now to enable tracing.
   • You can specify a different file path for the log file by editing the Log File Path field (default is SQL.LOG).
   • Click Apply or OK to confirm.

3. Generate Log Information:

   • After enabling tracing, perform the database operations you want to log.
   • This could include testing connections or running queries via the ODBC Data Source Administrator or your application.

4. Stop Tracing:

   • After you’ve generated the required information, return to the Tracing tab.
   • Click Stop Tracing Now to stop logging.

Where to Find the Log File

• The log file (default SQL.LOG) is typically created in the directory specified in the Log File Path. You can open it with a text editor to review the details of the ODBC operations.

No idea how the app is coded.

Does it use one odbc connection and leave the connection open all day (or runtime)?

If it has looping code, maybe the Access application has a memory leak and is unable to allocate memory for the

odbc call. Which could happen if the odbc connection isn't being closed before a new odbc connection is created..

If the program makes looped calls, then eventually it will run out of memory or allocation handles created to the ODBC driver.

The program should have a way to trap and write a log for this error.

We primarily use the boi to integrate with Sage, but here's a function that has an error handler in it, which writes the error details to file.

Wherever your program makes the odbc call, put together an error handler like function_error below. Just put in the odbc error object

and write out the extended error details Access provides. None of the code below applies to your specific issue, but

the concepts should map out for any error you want to trap.

Good times!!

'*************************************************************************************************
'Function boi_get_script_handle
'returns: boolean
'True - script has been successfully initialized
'False - script failed to initialize
'
'Arg 1: hScriptObject (provideX.Script)
'Revised: 12/18/2015
'Author : B Stern
'*************************************************************************************************
'
'This function initializes the boi script object against the com server
'located in the home directory or path of the target mas installation
'UNC path example: "\\MAS-SERVER\Sage\MAS90\"
'
'On success, the hScriptObject will return to the calling function initialized
'
'*************************************************************************************************

Function boi_get_script_handle(hScriptObject As Object) As Boolean
Set hScriptObject = CreateObject(Class:="ProvideX.Script")
On Error GoTo function_error

hScriptObject.Init (boi_com_connection.com_path) 'boi_com_path
If (hScriptObject.State) Then
boi_get_script_handle = True
Else
boi_get_script_handle = False
End If
Exit Function

function_error:
boi_error_msg hScriptObject.slasterrormsg, "script_init"
boi_get_script_handle = False

End Function

Hi Kevin,

Yes the app's DSN is setup as you've shown and we do have CS ODBC enabled on the Sage server although we have a few users who do not have the option enabled on their Sage user account due to some performance/technical issues.

What would be the good / bad with an app using CS ODBC? I'm not very familiar with the feature other than we were told not to use it on Sage 2016 but now it seems to be recommended (?) 

If your process runs on the Sage server don't use CS ODBC.  Otherwise, it speeds up queries because the remote process doesn't need to access raw data files over the network... where loading really large files over the network can slow things down significantly.