Geek Answers Handbook

ADODB RecordSet as RecordSource Access Report

I have a simple form, query, and report in Access 2003. I need to manipulate the query results in a recordset using VBA, and then pass it to the report as my RecordSource.

If I declare a recordset as a RecordSet and use its Name property as the RecordSource of the report, then it works. However, since I need to edit the record set, I would simplify the use of ADODB RecordSet, as shown below.

The record set is declared as Dim rs As ADODB.RecordSet in the global module. The rest of the code:

 Dim db As Database Set db = CurrentDb Dim con As ADODB.Connection Set con = CurrentProject.Connection Set rs = New ADODB.Recordset Set rs.ActiveConnection = con rs.Source = "Select * from XXX" rs.LockType = adLockOptimistic rs.CursorType = adOpenKeyset rs.Open 'manipulate rs here....'

I used to pass the RecordSource report as myReport.RecordSource = rs.Name. But ADODB does not have a Name property.

How to transfer this record set to the report as its record source?

thanks

+7
vba access-vba ms-access adodb recordset
source share
2 answers

You cannot bind a report to a set of ADO records in mdb, only in adp: http://support.microsoft.com/?id=287437

+5
source share

I do not have a copy of 2003 access to the hand, but from memory you just do

 Set Me.Recordset = rs

Just looked at Microsoft KB, and it looks like my memory is still working!

http://support.microsoft.com/kb/281998

0
source share

More articles:


All Articles