[AccessD] Using the Report Writer Features of Access 2007 againstSQL Server Database Tables

If you pull the data in via ADO and a pass thru query you can specify
that always be "read only".

''  This code will populate an unbound MS Access form with a recordset
from SQL Server

'-----------------------------------------------------------------------
---------------------

Public Sub RefreshForm(strForm As String, strSql As String)

'==================================================================='
'                                                                   '
'   This procedure opens a recordset and                            '
'   binds the recordset to an open form                             '
'   The records will be READ-ONLY when displayed in the form        '
'                                                                   '
'   ARGUMENTS:    Form Name, sql String that returns a recordset    '
'                                                                   '
'                                                                   '
'==================================================================='
   
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
  

    On Error GoTo ErrHandler
    Application.Echo False, "Loading the data into the form "
   
    ' Open the connection
    cnn.Open DbADOConStr ' Pre built connection string

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseClient

    rst.Open strSql, cnn, adOpenForwardOnly, adLockReadOnly
    
    
    ' Bind the form to the recordset
    Set Forms(strForm).Recordset = rst
    

ExitHere:
    On Error Resume Next
    Set cnn = Nothing
    Set rst = Nothing
    Application.Echo True
    Exit Sub

ErrHandler:
    gstrErrMsg = "modSQLServer.RefreshForm: "
    gstrErrMsg = gstrErrMsg & Err.Number & " - " & Err.Description
    
    ErrHandle (gstrErrMsg)
    Resume ExitHere
End Sub

' --------------

Hope that helps a bit

Regards
Darryl. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Tuesday, 6 April 2010 10:27 AM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Using the Report Writer Features of Access 2007
againstSQL Server Database Tables

Background - Small company, small IT staff, small budget, no report
writer like Crystal Reports, etc.

 

We are thinking about using the report writer built into Access 2007 to
create reports from data that lives in SQL Server (purchased application
system).

 

We have done a little experimenting and things seems to work nicely in
our preliminary tests.  Do other firms do this (use Access just for
report writing against SQL-server data).  Are we missing a big "gotcha"?

 

 ~ ~ ~

 

Is it possible to force "Read Only" access in the Connection String?  We
want to ensure that no one ever updates any of the data in the SQL
Server tables.

 

Thanks,

Brad 

 

 

 

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

"This e-mail and any attachments to it (the "Communication") is, unless otherwise stated, confidential,  may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any of its related entities including ANZ National Bank Limited (together "ANZ"). ANZ does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication."