Cannot connect to linked tables in MS Access datasource

[galtland]

I've been working on upgrading a small business's infrastructure from CF 8 to CF 2021. Their database is in MS Access and as a part of this upgrade, we've had to move some of the larger tables to SQL Server Express 2014 and add them as linked tables in Access. These are then manipulated using a combination of local access queries/forms/macros and passthrough queries to SQL. Everything works perfectly in Access, and utilizing the native Access tables in CF works as well, just the linked tables are causing errors. The issue is that whenever a linked table is referenced in CF, either through a query that includes it or the linked table itself, the microsoft access driver throws an error.

The error depends slightly on how SQL Server is linked to access.

When using the built-in method of linking SQL Server to access with a named pipe, this error is thrown:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'ODBC Driver 11 for SQL Server<ComputerName>/<Instance Name>' failed.

For some reason the slash gets reversed, it should be a backslash and it is a backslash in MS Access. There's no typo either, there's no space between Server and the computer name.

And when using ip, this is the error:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'ODBC Driver 11 for SQL Serverlocalhost' failed.

When using an ODBC system DSN (using the SQL Server Native Client 11.0 driver) to link the SQL Server tables in access, this is the error:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] ODBC--connection to '<DSN Name>' failed.

I appreciate that migrating to SQL Server would probably be a more robust and easier solution, however the company needs to maintain their system after I'm gone and is set on using access. We can't be the only ones using Access as a front-end for SQL Server, but the only info I'm finding on this online are posts with no responses or that don't apply. I'm thinking it's probably a permissions issue, I've tried connecting the linked tables in Access using both trusted connection and a SQL login, but I assume that there's an issue with CF not having the same permissions. I've also tried linking the access file in CF Admin as ODBC instead of the Access driver with the trusted connection as well, to no avail. 

[Dave Watts]

I don't think it's a permissions issue. I think JDBC isn't designed to support the somewhat peculiar concept of linked tables Access uses. I'm pretty confident there's no workaround, much less an easy one. I recommend you advise your client to bite the bullet and migrate to SQL Server or MySQL or any real client-server database - Access is not one of these.

Dave Watts, Eidolon LLC