I'm having trouble maintaining a linked table to SQL Server using a file DSN. When I first create the linked table I can view the data, but if I close the DB I lose the connection. Any further attempts to view data result in a ODBC-Connection failure (3151). The only way to re-establish a connection is to refresh the links either programmatically or with the linked table manager. The only time I don't have an issue with file DSNs is if I "use a trusted connection", but in this case I need to use SQL Authentication.

I think it may have something to do with the DSN not remembering the password. If you look at the .dsn file it does not mention the password at all.

If anyone know how to deal with this without using a function to re-link or re-create the tables I would be most appreciative.

I use a system dsn instead of a file dsn to maintain the connection. If you do not select trusted connection, sql will prompt you for the username and password each time you make your first connection to the sql server.

You can use code to establish a system dsn, and disconnect it, from within the database if you want.


mmcdonal

So will it prompt for a password when you use a sytem DSN (not trusted connection) or is there a way around that.

I guess I just don't understand why it won't maintain password information even though you specify it when you create the dsn.