Running Access Query which calls Linked SQL Server tables

[RookPSU]

I have a standalone application that I need to call an access query. It is a make table query which calls about 20 other queries which in turn reference linked SQL Server tables.

Everytime I try to execute my command I receive an error:

ODBC--Connection to 'SQL ServerBOBRKSQL' failed.

Noteworthy.. every time I open the access database I have to refresh my linked tables first, otherwise I get an error message saying:

'Login failed for user '(null)'. Reason: Not associated with a trusted SQL server connection.

..when I try to open one of the linked tables or run the query.

It seems that for some reason access doesn't save the DSN to the linked tables. This may or may not have something to do with why my VB app doesn't work. Once I refresh my linked tables by reassigning the DSN the query works fine. However still, if even after I reinitialize the DSN in access and go back to VB and try to run the app, I get the same error message. I checked to make sure that the MSysConf table didn't exist in my SQLserver database. Im running an XP machine to SQL server 7. PLEASE HELP ME.

 

[Gwena]

Hi RookPSU,
I think the problem might be with you ODBC configuration:
Use SQL authentication and LoginID: sa