Can't open ODBC table on different computer (1 Viewer)

I want to connect to a table in a Firebird database on a server from each of the clients using the same Access front end. But after I connect the table via ODBC successfully on one computer (the development PC), it gives me an ODBC connection failed message on other computers when I try using the Access frontend there even though I set up the Firebird driver on those computers and gave the ODBC DSN the same name. Strangely, the table that I connected from the development PC will function if I use the problem computer to link another table using the same DSN. It doesn't matter if the new connection is to the same table or not, as long as it is to the same DSN. Furthermore, every time I close Access and restart, the first table I linked on the development computer will stop working again until I open the second table I linked on the current computer.

Somehow Access is not recognizing ODBC links to DSNs of the same name created on other computers until it is accessed using a link made on the current computer.

This is a pretty big problem since I need to be able to distribute new versions of the front end to every client and have them work immediately.

And yes, they are all using 64-bit ODBC. It isn't even possible for Access to see a 32-bit ODBC from any computer.

Have you looked at the ODBC error itself to see what the server is responding with?

Set up an error handler and when you get the VBA ODBC error (usually 3146, IIRC), check this:

?DAO.Errors(0).Description

That's where the server errors are reported (there may be more than one, but usually it's only the first that is particularly helpful).

You might also try DSN-less connections (building the connection via code at startup) and try that. I tend to find it a more flexible approach myself, of course provided the correct driver is installed, etc.

hth

It seems the path to both the database file and the client DLL driver need to be the same on every computer. These are saved in the Connect value of the DAO TableDef for the linked table.

So far I have not found a workaround.