Making a Connection

[Privateer]

I need Access to link to tables in two different databases that are located on the same server. When the FE is opened, the connection to the first database works, all twelve tables connect. The problem is with the connection to the second database. This connection kind of works, the two tables connect but with my windows login credentials instead of the user name and password supplied in the connection string. When the client is using the FE, every time they run a report that pulls data from the two other tables, they get asked to re-enter credentials for the connection to those tables. So why are credentials passed in the string being overlooked, meaning no error message pops up, and why is the connection being created with my windows credentials? This is particularly annoying because I can't re-create the problem on my machine, all my connections work albeit with two different credentials, but the client is stopped for a password every time they run certain reports.
As far as security goes, the account is in both database security areas and the master security area and User Mapping for the account has both databases checked off. The FE is Access 2007 and the BE is SQL Server Express. As always, any help would be appreciated.

 

[Rx_]

Is this using the ODBC dialogue box to connect the tables?
A tried and ture method is to use VBA code to make the connection to the DB of your choice.
My process uses VBA code to first destroy all Linked Tables.
Then, the uses a Local Access Table - this table has the Linked Table Names plus other columns. One column can be the Database Name to connect. Another column name can be a description about the table or the date it was added (documentation).
Let me suggest a True/False column to determine if this Table should be connected at all. This one is very useful for testing or version control. (i.e. It couldn't hurt).

Next, the VBA reads one record at a time. It takes the table name, the DB name, and creates a DSN-Less Linked table. My current DB has over 150 linked tables. Takes bout 2 seconds to complete.
You might search on: http://www.access-programmers.co.uk/forums/showthread.php?t=271187&highlight=DSNLess
This one has some code examples along with a success story. Encourage you to share your final result for others too.
http://www.access-programmers.co.uk/forums/showthread.php?t=270468


It appears that DSN-Less (the dash) isn't searching again.
I found this on DSN_Less search.
Finding VBA code for the connection should be out there. Using a local table to link or hard coding the table names is up to you.

 

[Privateer]

In the interest of spreading the knowledge, I wanted to post the workaround to my own problem, especially since I did not really solve the posted issue. After speaking with a colleague at work, he gave me some possible options and views sounded like the best one.

On the first database DB1, I created views that run a query to the tables on the second database DB2. This is simple because all you need to do is fully qualify the target in the “from” clause, like SELECT * FROM DB2.dbo.tblEmployee. Now here is the really good part. The code that we used to link the tables from Access to DB1 looks like this select * from sys.objects where type = 'U' or type = 'V' and type <> 'sys %', meaning the result is user tables and views. After we looped through the recordset both the tables and the views showed up in Access under the table category and the views looked and acted like linked tables. The added bonus is speed, those views are rendered on the server side before they get passed to Access. And to complete the answer, the user account on DB1 had to include DB2 under its user mappings with the same user name and dbo schema. I hope that helps others. And RX_ thanks for the suggestion and links, I will look them over.
Cheers,
Privateer