I am working from within an SQL database trying to get data from an MS Access Database as a linked server. I can "successfully" add the linked server, but when I try to open it from SQL EM I get "Error 7399. . . " from Microsoft Jet. When I try to query it from QA I get "Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. [OLE/DB provider returned message: Not a valid account name or password.]"
I tried both Access 2000 databases and '97. Same results for each. Big project pending . . . any help is appreciated!
The advice you both have given has been helpful - but still no success.
XP and 2000 servers are not having this problem (and don't need the ODBC connection). Our primary NT data server has permissions throughout the network, but won't connect properly to outside databases. We can connect from other servers to this NT server and query the data, but not vise-versa. This holds true for querying both SQL tables and MS Access.
Using gvphubli's comments, our best success has been that we can see the tables through EM via the ODBC connection, but Query Analyzer still gives "invalid Schema" error--can't see into the tables.