[AccessD] Connect to SQL Server views

Good detective work.

Jim



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, January 26, 2011 12:58 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Connect to SQL Server views

I have a strange something happening and I don't know why

 From my client workstation, when I connect to (try to link tables to)
PSServer (SQL Server) I can 
pretty much only see a small set of views.  I discovered that in order for
my new views to be seen I 
had to set a DISCO user and a public Role for my new views, at which point I
could see them from my 
workstation.

When I try the same thing from the server that holds all of the Access
database files, I can not see 
those same set of views but I can see a single tab which says tables, and it
looks like I am only 
seeing tables in that tab.

Once I link the view from my workstation I can see the view's contents from
my workstation.

When I move back to the server I cannot see the view, I get an error that
the view is not a valid 
object or some such.

I have to fix up the connect string and then it works just fine.

Good:ODBC;Description=PS;DRIVER=SQL Server;SERVER=PSSERVER;APP=MicrosoftR 
Access;WSID=DISABILITYINS43;DATABASE=Millennium;Trusted_Connection=Yes

Bad:ODBC;Description=PS Server;DRIVER=SQL
Server;SERVER=PSSERVER;APP=MicrosoftR 
Access;WSID=DISABILITYINS89;Trusted_Connection=Yes

I just replace the connect string I labeled Bad with the one I labeled good
and (from the server) I 
can see the view data.

And finally, if all that ain't strange enough, I can see the data from my
workstation AND DiscoSvr 
with the original connect string.

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com