Microsoft Access Report with Stored Procedure as Record Source

Code below is to assign recordset returned from SQL Server stored procedure to an Access Report record source. SQL Server’s T-SQL code is more powerful and having database code centralized on the SQL Server is a better alternative.  The procedure below is the main Report_Open procedure of an Access Report.

‘Change myServerAddress to the address or FQDN of your SQL Server
‘Change myDatabase to desired database in the SQL Server
‘Change dbo.StoreProcedure to existing or recently created stored procedure
‘Add parameters as needed examples are below
‘Add this procedure to an existing report

Private Sub Report_Open(Cancel As Integer)

‘Declarations
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command

‘Initialization

Set cnn = New ADODB.Connection
‘Connection String
cnn.ConnectionString = “DRIVER=SQL Server;Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;”
cnn.Open
Set cmd = New ADODB.Command

‘Setup the Command Object

Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = “dbo.StoredProcedure”
‘AddRequired Store Procedure Parameters Examples are below
cmd.Parameters.Append cmd.CreateParameter(“@Pool”, adInteger, adParamInput, , Value)
cmd.Parameters.Append cmd.CreateParameter(“@Date”, adChar, adParamInput, 10, Value)
‘Assign Recordset returned from stored procedure to Report.Recordset Object
Set Me.Recordset = cmd.Execute

‘Clear Memory for Command Object and Connection Object
Set cmd = Nothing
cnn.Close
set cnn = nothing

End Sub

One thought on “Microsoft Access Report with Stored Procedure as Record Source

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: