Multiple queries require many logins to same database

[Walt Palmer]

I have several Excel workbooks that use Access as their
data source (Access is linked to SQL server). Each
workbooks uses a number of the Access queries to get its
data; Excel forces me to login to SQL Server for each
query, even though they are all through the same Access
database.

Is there way to override this behavior? Can't I login
once and refresh all of my queries with a single database
login?

TIA.

 

[Jamie Collins]

I have several Excel workbooks that use Access as their
data source

Are you sure about that? The MS Access UI element of the .mdb file can
store the SQL Server login details, however this is not available to
the Jet elements. Jet comprises the data and schema, among other
elements. Because it is the Jet database, rather than the MS Access
elements, you are accessing from Excel, the stored login details are
unavailable to you.

(Access is linked to SQL server). Each
workbooks uses a number of the Access queries to get its
data; Excel forces me to login to SQL Server for each
query, even though they are all through the same Access
database.

Is there way to override this behavior? Can't I login
once and refresh all of my queries with a single database
login?

A couple of thoughts:

1) Is there a particular reason for using a intermediate Jet database?
If not, You could get the data directly from SQL Server i.e. connect
to SQL Server and execute sql from Excel. Better still rewrite your
views/strored procedures in SQL Server and execute via your direct
Excel connection.

2) You could rewrite the Jet views/stored procedures to use embed the
SQL Server login details e.g.

SELECT lname, fname
FROM
[ODBC;Driver={SQL
Server};SERVER=<<snip>>;DATABASE=pubs;UID=<<snip>>;Pwd=<<snip>>;].Employee
;

You'd need to test whether the performance hit is within acceptable
limits.

Jamie.

--