Access ADP - Ado question: fill recordset and show

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Access ADP - Ado question: fill recordset and show

Author

Topic

giovi2002
Starting Member

46 Posts

Posted - 2005-07-22 : 18:48:47

Workbench : access adp / sql server
script : Ado
Goal: Pass a value from a form (1) to a stored procedure (2) and retrieve and show the recordset within the application (3)
Status: 1 and 2 work with the written code, for number 3 'receive the recordset' I had to use a stupid workaround by having my SP to fill a table, after table is being filled my ado code will open a view based on this table. It would be more efficient to have my sp only to perform a parameterized query.

Can you extend my ado code?

I've used Dim rstKoppeltabel As ADODB.Recordset but don't know how to fill the recordset and display the results within the access adp

Private Sub SYSLIJNNUMMER_DblClick(Cancel As Integer)

On Error GoTo Err_Form_DblClick
Dim myado As ADODB.Command
Dim rec As Single
Dim StrBronrec As Variant 'parameter 1
Dim StrReprec As Variant 'parameter 2
Dim nmbSyslijnnummer As Variant 'parameter 3
Dim stDocName As String
Dim rstKoppeltabel As ADODB.Recordset 'recordset declaration
Set myado = New ADODB.Command
myado.ActiveConnection = CurrentProject.Connection
myado.CommandType = adCmdStoredProc
myado.CommandText = "dbo.Ap_Rapportagegegevens_Bronrecords"
StrBronrec = "%"
StrReprec = "%"
nmbSyslijnnummer = Me.SYSLIJNNUMMER
myado.Parameters.Append myado.CreateParameter("Bronrecord", adVariant, adParamInput, 12, StrBronrec)
myado.Parameters.Append myado.CreateParameter("Reprecord", adVariant, adParamInput, 12, StrReprec)
myado.Parameters.Append myado.CreateParameter("Syslijnnummer", adVariant, adParamInput, 12, nmbSyslijnnummer)
myado.Execute

DoCmd.OpenView "dbo.Vw_Koppeltabel_inzien", acViewPreview, acReadOnly
'this part opens the view and should be replaced by fill the recordset 'and display the results

Exit_Form_DblClick:
Exit Sub

Err_Form_DblClick:
MsgBox Err.Description
Resume Exit_Form_DblClick

End Sub

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-08-09 : 13:48:19

It seems you have already used ...

Dim rstKoppeltabel As ADODB.Recordset 'recordset declaration

...so you just need to replace your ...

myado.execute

... with ...

set rstKoppelTabel = myado.Execute

... This results in a filled recordset.

~ Shaun Merrill
Seattle, WA

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2005-08-09 : 19:06:07

Is the input parameter and recordset display on the same form? If so, you can do the following:
-Insert your subform and make it's recordsource equal to the name of your stored procedure
-Name your parameter controls the same as the stored procedure input parameter (e.g. if your stored proc has a varchar parameter called @SearchCriteria, then you name your input text box SearchCriteria.)

This is done in much the same way as Access has traditionally done things with its queries. It can be a pain in the butt to use sometimes (what with naming conventions and the like), but it can be a quick and easy way around your problem.
I'm not sure that this is a documented feature of Access ADP's, though. I found it out more by accident....

HTH,

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources