[AccessD] server? based trusted connection for multi-front-ends. Acc 200x SQL Server 2000 ; OLEDB.10.0

Note that the trusted connection string below does not specify a database,
initially.

Using authentication on SQL Server, I was itching to get to the bottom of
how to query trusted connection (into or from an ADP) from Access to a
secure stored procedure running in a secure SQL Svr database - the
connection would be established to the server, while the Exec connects via
permissions within the sproc to the database (the db would be 'locked
down').

I'm not sure if this is the sole domain of Excel 2000+ pivot tables, or if a
similarly robust and secure connection can be obtained from Access.  The
premise is that a Pivot table is run over data that is refreshed frequently
- a copy of the raw data is needed behind the scenes to ensure it's
captured.

I was wanting to eventually get a server based query using 
Provider=Microsoft.Access.OLEDB.10.0;
Data Provider=SQLOLEDB.1

... somehow instead of the ODBC based connection utlized in the Fabrikami
sample on MSDN:

reportQuery = "EXECUTE Finance..xlExpenseReport"
reportConnection = "ODBC;DRIVER=SQL
Server;SERVER=FABRIKAM1;Trusted_Connection=Yes"

After having initially explored loading what would apparently be a secure
stored procedure (i.e. suser_sname() having exec, select on sproc) at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fabrikam/ht
ml/odc_ofexpense_report_solution_excel2003template.asp 

...and using various Excel examples, particularly...

http://www.excelkb.com/article.aspx?id=10132
and its companion article at http://www.excelkb.com/article.aspx?id=10131

... and at
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

I didn't get as far as I wanted to with the code, but I managed to hammer
out the Excel based code below - including the sproc - it should work for
the benefit of anyone having SQL server and Excel or Access wanting to
document schema quickly.  Any ideas on how to 'mirror' recordsets i.e.
connect a pivot table, then Access for raw underlying data via two
connections with the same exec string would be greatly appreciated.  The web
examples as cited above instruct on how to set up row level permissions
quickly (which I think/hope is the purpose of the painstaking focus on
trusted connections here and in Access).

Private Sub CommencePivot1()
''' Using trusted connection in front-end
    Dim startDate As String
    Dim endDate As String
    Dim currencyCode As String
    Dim pivotTableName As String
    Dim invalidDateErrorText As String
    Dim startDateErrorText As String
    Dim reportQuery As String
    Dim reportConnection As String
    Dim formatCast As String

    Application.ScreenUpdating = False

    'Data input validation
    SelectASheet ("Sheet1")
    pivotTableName = "PivotTable1"
    
    'Initialize variables
    strDatabase = CStr(Trim(InputBox("Enter Database Name to display
Schema", "Query Macro", "Test Source")))
    reportQuery = "EXECUTE pubs..pr_tmpSchema '" + strDatabase + "'"
    reportConnection = "ODBC;DRIVER=SQL
Server;SERVER=myServer;Trusted_Connection=Yes"

    On Error GoTo ErrorHandler

    With ActiveSheet.PivotTables(pivotTableName)
        .PivotCache.Connection = reportConnection
        ''  Careful not to mash this together with another Pivot table.
'' Keep ado and odbc separate (at least in Excel)
        .PivotCache.CommandText = reportQuery
        .PivotCache.Refresh
    End With

    'Save input parameters to spreadsheet
    ActiveSheet.Range("F2").Value = strDatabase
    ActiveSheet.Range("F3").Value = "Tap Pivot Source"
    
    SelectASheet("QTable")
    ''' would branch another connection rendering the same rst to
    ''' preferably Access if secure or 2nd front-end
    ''' ?? CreateQueryTable(reportquery)
    Application.ScreenUpdating = True

exithere:
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description
    Resume exithere
   
End Sub
Public Sub SelectASheet(ByVal strSheetName As String)
    Sheets(strSheetName).Select
exithere:
    Exit Sub
errhandler:
    MsgBox Err.Description & " " & "You may want to create or synchronize"
    Resume exithere
    
End Sub

'' -- The Stored Proc follows - may come in handy for documentation
'' -- Exec pr_tmpSchema NameOfDatabase //you want documented.
'' -- Simulates more or less restricted access
CREATE PROC pr_tmpSchema 
@strDatabaseName varchar(75)
As
Declare @SQL varchar(8000)
Declare @CleanupSQL varchar(8000)
Declare @tablename nvarchar(75)

IF @@ERROR <> 0 PRINT "ERROR IN DECLARATION"
-- I didn't have time to pull this @tablename 
-- structure out - it does nothing here
Select  @tablename = 'tmpTemp1'
Select @CleanupSQL = ('IF EXISTS (SELECT name FROM sysobjects ' + 
    ' WHERE name = ''' + @tablename + ''' AND type = ''U'') ' +
      'DROP TABLE ' + @tablename)
EXEC (@CleanupSQL)
IF EXISTS (SELECT name FROM sysobjects 
    WHERE name = 'tmpTemp1' AND type = 'U')
     EXEC ('DROP TABLE ' + @tablename)
-- SELECT @strGetDBSQL = 'USE ' + @DBName + ' GO'
  SET NOCOUNT ON
  BEGIN
  SELECT @SQL = ('USE ' +  @strDatabaseName + ' Select TABLE_CATALOG =
Left(TABLE_CATALOG, 75),' +
   ' TABLE_SCHEMA = Left(TABLE_SCHEMA, 30), TABLE_NAME = Left(TABLE_NAME,
75),' +
   'COLUMN_NAME = Left(COLUMN_NAME, 75),DATA_TYPE = Left(DATA_TYPE, 30),
CHARACTER_MAXIMUM_LENGTH  = Left(CHARACTER_MAXIMUM_LENGTH, 30),' +
   'IS_NULLABLE = Left(IS_NULLABLE, 10), COLUMN_DEFAULT =
LEFT(COLUMN_DEFAULT, 255)' + 
   'From Information_Schema.columns ' +  
   'Where Table_Name like ''%''' +
   ' And Table_Name in (Select Table_Name from Information_Schema.Tables
WHERE TABLE_TYPE = ''BASE TABLE'')' +
   ' And Table_Name not like ''dt%''') 
-- --------------
-- print 'Test4:'
-- SELECT @SQL
-- --------------
-- ran out of time to explore COLUMN_DEFAULT)
-- --------------
   EXEC(@SQL)
   END

* * *
Bill Thompson,
Washington State, U.S.