QUESTION:
I have set up a system DSN that points to an MS Access database. This database contains tables and queries that are "linked" to a SQL database. When using Access, the first time one of these is accessed it asks for the SQL DB login, and after that these work just fine. When I use DemandTools MassEffect with this DSN, I can see all of the tables and queries, and can use the ones that are solely access-based. However, when I try to use as a data source one of the tables or queries that is linked to the SQL database, it gives me the following 2 errors:
- "Error Loading data file. Check to see that the file is not currently open and try again"
- "Error loading data: ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'SF10' failed." ("SF10" is the name of the SQL db).
I presume that this has something to do with the required login, but it's not clear. Is there a way to use the Access DB's linked tables and queries as a data source for MassEffect?
ANSWER:
DemandTools does not ask for authentication, therefore the only way to use these objects would be to:
- Use Window authentication to authenticate to the SQL server and not worry about passwords
- Include the password and userId in the DSN file (this will show the password in plain text so it may not be a good idea)
