Query on the fly?

[chris lynch]

Hi i want to create a query on the fly. Meaning i have a query that contains, name,class,group,attend,date.... What i want to do is select a certain date from this query and put them into another query(temperary if possible). can this be done.
I'm using MS Access,VB6...

Thanking you in advance
Chris lynch

[RobDog888]

How are you connectiong to your db? ADO? You can create a query dynamically and pass that in an
ADO Recordset .Open method call.

[chris lynch]

Yes I'm using ADO sorry for not mentioning it..... HAve you an example of what you said or a link... Something to get started with?

Thanks
ChrisLynch

[Rufen]

Set your report database's datasource to a recordset.
(Ex: Report.Database.SetDataSource YourRS)

[chris lynch]

where in vb6 and then this will do what?

[Hack]

Something like

Dim sSQL As String
sSQL = "SELECT name,class,group,attend,date FROM yourtable "
adoRs.Open sSQL, cnobject
 
'then you could insert the results of your recordset into a temp
'table to which your crystal report was bound

[chris lynch]

I'm using an access database, is that a problem?

[Hack]

I'm using an access database, is that a problem?

Nope...for years I worked on an application that used Oracle as the backend. With the code, we shipped an Access Database. This Access database had a boat load of tables in it, and each table had a Crystal Report bound to it.

When I ran a SQL query against the Oracle database, I created a recordset and feed the recordset into the appropriate Access Table, and the bound Crystal Report resulted.

Of course, prior to each run, the contents of the table was deleted before the new recordset data was inserted.

[chris lynch]

I have that done and it just runs throught the code and nothing happens.... Is there something else i'm missing?

HI again, i have been gone awhile, i've been looking for some cod eto work with this....

Have i got it right that the following code won't show anyresults. What can i do to show the recordset on a form?

Please help please

Dim rs As New ADODB.Recordset
        Dim myconn As New ADODB.Connection
        Dim sql As String
        
        myconn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MS Access Database;Initial Catalog=D:\Documents and Settings\christopher.lynch\Desktop\Complete Manual program\Manualrecords.mdb;" & " Pwd= " & Txtpassword.Text & "  "
    
        myconn.Open 'connection string'
 
        sql = "SELECT * FROM [Total]"
        
        If Len(TxtManualdate.Text) > 0 Then
        
            sql = sql & " WHERE [Date] = #" & TxtManualdate.Text & "# "
            Debug.Print sql
            
        End If
        
       
                 rs.Open sql, myconn