[AccessD] Connect to SQL Server views

Your Bad connect string does not specify a Database ie
DATABASE=Millennium

 Rusty

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, January 26, 2011 2: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=Microsoft(r)
Access;WSID=DISABILITYINS43;DATABASE=Millennium;Trusted_Connection=Yes

Bad:ODBC;Description=PS Server;DRIVER=SQL
Server;SERVER=PSSERVER;APP=Microsoft(r)
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
**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review 
by, and/or disclosure to, someone other than the recipient.
**********************************************************************