Show SQL query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have written a query that works fine. I am trying to duplicate this in VBA. I have written the code to create a recordset, and it matches the query.

Set strSQL = "the SQL code
Set db = CurrentD
Set rstDWP = db.OpenRecordset(strSQL

Now I want to (1) display the recordset and (2) save it as a table. Any ideas would be appreciated

Thanks!
 
Hi George,
Now I want to (1) display the recordset
Click to expand...
I think, for displaying a recordset you *always* need a
form with "datacomponents" on it. To change the
recordsource of the form at runntime you have to set the
recordsource of the form by.

Me.recordsource = strSQL

and (2) save it as a table.
I think the best way to save the result of a query to a
table was

SELECT * INTO NewTableName FROM SourceTable WHERE ...

Attention: NewTableName must not exist. The structure of
the new Table results from the query-structure.


To add the data to an existing table you can use:

INSERT INTO NewTableName(Field1, Field2) SELECT F1, F2
FROM SOURCETABLE

You have to change your Select-Statement to do so.

Hope it helps.
Niels
 
Now I want to (1) display the recordset
Click to expand...

You can't. Recordsets are for manipulating. You need to create a report,
and then open it using something like

DoCmd.OpenRecordset "rptMyReport", strSQL, ,, acViewNormal
and (2) save it as a table.
Click to expand...

I never understand why people want to make tables programmatically, but
anyway... You need a MakeTable query, which needs an INTO clause in the
SQL:

SELECT This, That, TheOther
INTO MyNewTable
FROM MyTable
WHERE TheOther > SomethingElse;

Hope that helps.


Tim F
 
Hi George,
i think you need a form. But you can set the
property "DefaultView" to "datasheet" -
that looks like the normal "tableview".

Niels
 
Back
Top