[AccessD] Connect to SQL Server views

Yes, I can see that.  But why does it work (I can see data) using the "bad" connection string from 
my workstation, but not from the server machine?

John W. Colby
www.ColbyConsulting.com

On 1/26/2011 4:05 PM, Rusty Hammond wrote:
> 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.
> **********************************************************************
>