We help IT Professionals succeed at work.

How to get View source by linking Access to SQL Server?

mlagrange
mlagrange asked
on
Hello - if I have Access (2003) linked to SQL Server (2008), is there a way to get the source for the views?
Comment
Watch Question

Leigh PurvisDatabase Developer

Commented:
Hi

Linked how?  
The source... as in the T-SQL definition?

Author

Commented:
Linked by OBDC; and yes, the T-SQL
Database Developer
Commented:
Easiest way (certainly the easiest to describe ;-) would be to use those same ODBC connection credentials in a passthrough query.
You could then either query using:

SELECT OBJECT_DEFINITION (OBJECT_ID('YourViewName'))

or

EXEC sp_helptext ''YourViewName''

Naturally - if you're doing this in code, it's standard practice to alter the definition of the passthrough at runtime to facilitate a varying object name.
e.g.
CurrentDb.QueryDefs("PassthroughName").SQL = "SELECT OBJECT_DEFINITION (OBJECT_ID('" & strViewName & "'))"
Debug.Print CurrentDb.OpenRecordset("SELECT * FROM PassthroughName")(0)

Author

Commented:
Ok, I think I understand the first part; what is actually returning the T-SQL from  OBJECT_DEFINITION (OBJECT_ID()) ?

Leigh PurvisDatabase Developer

Commented:
I'm afraid I don't follow what you're asking...

Author

Commented:
I was hoping there was a way to get the T-SQL from the Views in a SQL Server database
Once you do the "SELECT OBJECT_DEFINITION (OBJECT_ID('" & strViewName & "'))"
in the PassThrough query, can you get at the SQL from there?
Leigh PurvisDatabase Developer

Commented:
Yes - the T-SQL definition of the view will be returned as the single column result of the query.
Leigh PurvisDatabase Developer

Commented:
(Permissions depending.)

Author

Commented:
Geez, I thought I was up early...

Works like a charm!

Thanks very much!

Author

Commented:
For the benefit of those that find this in the future:

I was having some trouble with this until I realized that I had to take off the "dbo_" qualifier.
   SELECT OBJECT_DEFINITION (OBJECT_ID(N'dbo_vwMyViewName'));  <--- no works
   SELECT OBJECT_DEFINITION (OBJECT_ID(N'vwMyViewName'));  <--- works

In the process of figuring THAT out, I found these helpful things:

How to create an SQL pass-through query in Access
http://support.microsoft.com/kb/303968

OBJECT_DEFINITION (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms176090.aspx

Leigh PurvisDatabase Developer

Commented:
Yes it's absolutely required that the object names you provide are as they appear on the server.  This is the server you're querying directly.  There is no functionality for Access to know such definitions - hence the passthrough.  The Access application is presented with what the server makes available as a resultset and schema information (to be re-interpreted in terms of Jet/ACE datatypes).

IMO local front end tables would be much better off renamed without the owning schema prefix anyway (i.e. no "dbo_").
That's not an issue when you create linked tables yourself in code.

Cheers.