Linked Tables - Do I Need DNS on end users PC?

[Andy Davidson]

Hi All,

I'm setting up an Access 2007 database as a reporting database from
feeds from Lotus Notes/Domino Server and I'm running into a ODBC
'DNSname' Connection Failed error.

Here's the details:

Server 1 Running Domino Server/Lotus Notes

Server 2 Running Access 2007 Database for reporting from Lotus etc.
I've installed the Lotus NotesSQL Driver and created System DNS's for
each odbc feed from Lotus. I've linked in several tables from Lotus
and this is working fine. I've created reports from these tables and
it's fine; tables refresh, reports open - all is good. I've also
created some Excel spreadsheets that link (via odbc) to the Access
database and refresh and pivot table the linked tables etc. Again,
this is working fine, no errors.

The Problem

As soon as i move the linked spreadsheets off the Access server PC and
put them on end users PC's, whenever I try to refresh the data I get
the error "ODBC 'DNSname' Connection Error". Where DNSname is one of
the DNS's created on the Access Server PC.

To recap its:

Server 1 > Lotus Notes

Server 2 Access backend with linked tables from Lotus using DNS's
Front End User PC with linked Excel spreadsheets pointing back to Server 2 Access backend

The front end PC's report an error with the DNS's on the server 2.
However the data open's fine on the Server 2/Access table.

As a test I imported a table into Access and it opened fine into Excel
on end user PC.

So the route should be: (Server 1) Lotus Data >> (Server 2) Data
linked into Access Backend >> (End User PC) Excel Report linked back
to Access backend - fails on any tables linked into Access / Works
fine with non-linked tables.

Should i need to setup the same DNS's on end users PC that are on the
Server 2?

I thought once the data was linked into Access, I would only need
Access odbc to sent it to Excel? So end users would only need Access
odbc? And not all the original DNS's that feed Lotus into Access
backend?

Any ideas or comments most appreciated.

Many thanks for your time,

Cheers,

Andy D.

 

[Stefan Hoffmann]

hi Andy,

I thought once the data was linked into Access, I would only need
Access odbc to sent it to Excel? So end users would only need Access
odbc? And not all the original DNS's that feed Lotus into Access
backend?

It should work DSN-less:

http://www.accessmvp.com/djsteele/DSNLessLinks.html
http://connectionstrings.com/lotus-notes


mfG
--> stefan <--