ADO recorset as a forms recordsource

[joebickley]

Hi

I have an ADO recorset that i would like to use on a report. Is there any way to do this or do i need to create a temporary table first?

Thanks

Joe

 

[tomschafer]

I believe you'd have to create the table first. Or create the report on the fly.

 

[vr]

Try creating a table from the ADO recordset in the report module.

Remember to delete the table to clear out old information.

 

[cmmrfrds]

I was trying to do the same thing a couple of weeks ago, but there does not seem to be a way to equate an ADO recordset to the recordsource on a Form. Since I was using SQL Server as the backend, I wrote the ADO recordset out as a temp table in SQL Server, then used a stored procedure to read the temp table as the recordsource on the form. You need to use the same ADO connection to SQL Server to access the temp table and then do a drop on the temp table as the last part of the stored procedure.

 

[joebickley]

Actually mate if u set the results from ur temp table to the return value from your stored procedure then put this in your recordset then u can attach this to your form if u need the code i can furnish u with it

 

[cmmrfrds]

Are you saying that there is a way to equate an ADO recordset to the recordsource on a Form or Report. If so, I would like to know the syntax. I tried to equate a recordset to the recordsource on a Form and got an error which I interpreted as that a resultset was needed and a recordset does not qualify as an SQL resultset, but I am interested in how it is done..

 

[joebickley]

Hi yeah i got a ADO.recorset as the source for a form and it is extremely quick to execute. This does not howver seem to work the same on a report. The code is at work and im at home but its something like this:

dim xx as form
set xx = forms!formname
set xx.recordset = ADOrecordsetname

i think you may have used xx.recordsource instead this does not work. If this dont work post something and i will send you the correct code from work.

My recorset takes its data from a stored procedure on my SQL server and i am v impressed with the speed.Can help with this also if you like.

Joe

 

[cmmrfrds]

I tried for this recordset after filling the recordset with data and got an error on the set statement. I am using reference library ActiveX object 2.6.

Public rs1 As New ADODB.Recordset

set xx.recordset = rs1
'error this is not a valid recordset property

 

[joebickley]

Hi as promised here is the code i have working. This is in access 200 if that makes a differance to you. Also it is setting the recordset onto a subform. Hope it works for you
[tt]

Function showjobcard()
Dim db As ADODB.Connection
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;driver={SQL Server};server=solihullweb;uid=sa;pwd=soldata;database=rcs_l322;"
Dim adoPrimaryRS As ADODB.Recordset
Set adoPrimaryRS = New ADODB.Recordset
adoPrimaryRS.Open "jobcard " & Forms!frmupdatereworks!VIN, db, adOpenStatic, adLockOptimistic

Dim yy As Form
Set yy = Forms!frmupdatereworks![Child73].Form
Set yy.Recordset = adoPrimaryRS
Forms!frmupdatereworks!Child73.Visible = True
End Function
[/tt]

 

[joebickley]

hmmm
also heres a tip dont give out your sa password on a forum DOH!!!!!

 

[Ed2020]

You can set a recordset as the source of a form (in Access 2000 or later) but you can't do the same for a report, so you will have to write the results to a temporary table first.

HTH,

Ed Metcalfe.

 

[cmmrfrds]

Joe, thank you for the example. It works for the original recordset returned but I get an error when trying to use a fabricated recordset. I needed to add some additional data to the original recordset so I created another recordset with the new data and tried to equate to the Form recordset and the error I get is:
The object you entered is not a valid recordset property.

Here is my code in case you can see a problem with my thought pattern on this.

Option Compare Database
Option Explicit
Public recCount As Integer
Public rs1 As New ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)
Dim cn As New ADODB.Connection, Sql1 As String
Dim rs As New ADODB.Recordset

Set cn = CurrentProject.Connection
'--- Get recordset from the database
Sql1 = "select * from IDTable order by ID, startdate"
rs.Open Sql1, cn, adOpenStatic, adLockOptimistic
rs.MoveFirst
recCount = rs.RecordCount
'--- Build an internal recordset to manipulate data
Call CreateRecordset(rs)

Dim yy As Form
Set yy = Forms!frm_IDTable.Form
Set yy.Recordset = rs
'--
Debug.Print "form name = "; yy.Name
Debug.Print "rs1 = "; rs1.RecordCount

Set yy.Recordset = rs1
''--------ERROR ON rs1 AT THIS POINT. rs will work okay.
rs.Close
Set rs = Nothing

End Sub

Public Function CreateRecordset(rs As ADODB.Recordset)
'-- create a fabricated recordset
With rs1.Fields
.Append "ID", adInteger
.Append "startdate", adDBDate
.Append "enddate", adDBDate
.Append "gap", adInteger, adFldIsNullable
.Append "gapNum", adInteger, adFldIsNullable
End With

Dim arrFields As Variant
arrFields = Array("ID", "startdate", "enddate", "gap", "gapNum&quot

Dim indx As Integer
'''''''''Debug.Print "in Createrecordset "; recCount
rs1.Open
rs.MoveFirst
For indx = 0 To recCount - 1
rs1.AddNew arrFields, Array(rs!ID, rs!startdate, rs!enddate, 0, 0)
rs.MoveNext
Next '- end of recordset

Dim theGap As Integer
rs1.MoveFirst
rs1.MoveNext '-- start with 2nd record
While Not rs1.EOF
theGap = FindRecord(rs1!ID, rs1!startdate, rs1)
rs1!gap = theGap
rs1!gapNum = 1
'''''''Debug.Print "gap update"; rs1!gap; " , "; rs1!gapNum
rs1.Update
rs1.MoveNext
Wend

End Function

Public Function FindRecord(prmID As Integer, prmSdate As Date, rs1 As Recordset) As Integer
''-- Get the prior enddate and subtract and return difference
Dim tempenddate As Date, aGap As Integer
rs1.MovePrevious
tempenddate = rs1!enddate
aGap = DateDiff("d", tempenddate, prmSdate)
rs1.MoveNext
''''Debug.Print "the gap = "; aGap
FindRecord = aGap

End Function