ADO Recordset/DAO queryDef recordsource 
Author Message
 ADO Recordset/DAO queryDef recordsource

HELP! I need to dynamically set a recordsource for a report at run-time to
an ADO recordset created from a SQL database. The RecordSource property of
the report will only accept a "Select" string that queries either an
existing Jet table or query, so I've attempted to create a querydef object,
but can't figure out how to get the querydef object created without
referencing an existing Jet table.

Here's the code, all of which works, except for that:
(I have also tried using a pass-through query with the connect property of
the querydef object, but am having so many ODBC problems that I want to
avoid that altogether)

Private Sub Report_Open(Cancel As Integer)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb()

'loop thru queries, delete the one used
For Each qdf In db.QueryDefs
    If qdf.Name = "TempQry" Then
        db.QueryDefs.Delete qdf.Name
    End If
Next
    Set cn = New ADODB.Connection

    cn.Open "Provider=SQLOLEDB;Server=KWAVEWKS1;" & _
        "Integrated Security=SSPI;database=KWaveEvals"

    Set rs = New ADODB.Recordset
    rs.Open "Select * from IndivEvals", cn

 '*****Recreate query and use WHAT as the SQLText??????
Set qdf = db.CreateQueryDef("TempQry", SQLText)

    Me.RecordSource = "TempQry"



Mon, 08 Mar 2004 07:16:37 GMT  
 ADO Recordset/DAO queryDef recordsource
Susan:

You are not going to get your code to work because you are trying to mix DAO
and ADO which are two different data access methods.

In ADO you need to address the Catalog object (similar to the old DB object
sort of) to get the Views collection and then append a view and set its SQL
source.  This is covered in the help file pretty well.   Go to the Active X
Data Object section and then to the ADOX Programmers reference and look
under the catalog object.

HTH
--
Steve Arbaugh
ATTAC Consulting Group
http://ourworld.compuserve.com/homepages/attac-cg


Quote:
> HELP! I need to dynamically set a recordsource for a report at run-time to
> an ADO recordset created from a SQL database. The RecordSource property of
> the report will only accept a "Select" string that queries either an
> existing Jet table or query, so I've attempted to create a querydef
object,
> but can't figure out how to get the querydef object created without
> referencing an existing Jet table.

> Here's the code, all of which works, except for that:
> (I have also tried using a pass-through query with the connect property of
> the querydef object, but am having so many ODBC problems that I want to
> avoid that altogether)

> Private Sub Report_Open(Cancel As Integer)
> Dim db As DAO.Database
> Dim qdf As DAO.QueryDef

> Set db = CurrentDb()

> 'loop thru queries, delete the one used
> For Each qdf In db.QueryDefs
>     If qdf.Name = "TempQry" Then
>         db.QueryDefs.Delete qdf.Name
>     End If
> Next
>     Set cn = New ADODB.Connection

>     cn.Open "Provider=SQLOLEDB;Server=KWAVEWKS1;" & _
>         "Integrated Security=SSPI;database=KWaveEvals"

>     Set rs = New ADODB.Recordset
>     rs.Open "Select * from IndivEvals", cn

>  '*****Recreate query and use WHAT as the SQLText??????
> Set qdf = db.CreateQueryDef("TempQry", SQLText)

>     Me.RecordSource = "TempQry"



Mon, 08 Mar 2004 10:33:22 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. DAO/ADO recordset as form recordsource??

2. ADO Recordsets and Forms (ADO vs DAO)

3. Using ADO Recordset with Forms (ADO vs DAO)

4. DAO.Recordset vs ADO.Recordset

5. QueryDef problem - converting DAO to ADO

6. Assigning a created ado recordset to a sub forms recordsource

7. Use ADO recordset as form's recordsource

8. Microsoft Access Project 2000-- set a ado recordset as a recordsource

9. using ADO recordset as recordsource

10. ?Using a stored procedure or an ADO recordset as a report recordsource


 
Powered by phpBB® Forum Software © phpBB Group