Opening a fabricated query from VB

[Slayboy]

I am trying to make a more complicated piece of code than this, but I have started to break it down and I think if I can get this bit to work I can sort out the rest.
Basically I want the code below to work but it is getting snagged on the DoCmd.OpenQuery rst1 bit, it seems to be fine with the SQLstring but then I'm doing something wrong when I try to turn it into an actual query.
Can someone please tell me what I need to make the code work?
Thanks.


Private Sub Command257_Click()
Dim dbs As Database, rst1 As Recordset
Dim SQLString As String

SQLString = "SELECT ELTDistributionReport.*" _
& " FROM ELTDistributionReport" _
& " WHERE (([Owner]='Andrew Cowan'));"

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset(SQLString, dbOpenSnapshot)

DoCmd.OpenQuery rst1

End Sub

 

[Guus2005]

Use MakeQueryDef

You have to save the query first in order to open and view it.

Public Function MakeQueryDef(strSQLname As String, strSQLdef As String) As Boolean

   Dim qdf As QueryDef
   Dim dbs As Database

   On Error GoTo Err_MakeQueryDef

   Set dbs = CurrentDb

   DeleteQueryDef strSQLname ' Query weggooien als die bestaat

   Set qdf = dbs.CreateQueryDef(strSQLname, strSQLdef)

   MakeQueryDef = True

Exit_MakeQueryDef:
   Exit Function

Err_MakeQueryDef:
   MakeQueryDef = False
   MsgBox "Fout in MakeQueryDef : " & Err.Number & ":" & Err.Description, vbExclamation, "Fout bij maken query definitie."
   Resume Exit_MakeQueryDef
End Function
'
Public Sub DeleteQueryDef(strSQLname As String)

   Dim dbs As Database

   Set dbs = CurrentDb

   On Error Resume Next
   dbs.QueryDefs.Delete strSQLname 'Genereert fout als query niet bestaat.

End Sub

HTH

 

[namliam]

LOL

Good try, but...

Your rst1 is allready open in memory, no need to (or even impossible to) open it using DoCmd.OpenQuery.

You have to either browse your query using
Do while not Rst1.eof
...
more code
...
Rst1.movenext
loop

Or if you want to open the query in access you have to assign it to a query first.
Create a dummy query and Call it qryDummy.
Now in code
Currentdb.Querydefs("qryDummy").SQL = SQLString
Docmd.OpenQuery qryDummy.

Either of above should resolve your problems.

Good luck!

 

[Slayboy]

What I'm actually trying to do is export the queries to html or excel, I wanted to loop through a list of names and filter a query on each name then export it to html or excel. So if I had 22 names I would be exporting 22 tables.

I've got this piece of code:

DoCmd.OutputTo acOutputQuery, "ELTDistributionReport", acFormatXLS, DLookup("[Path]", "[ReportPrintTableLocation]", "[PID]=1") & Grav & ".xls", False

which works but I basically want the "ELTDistributionReport" to filter on one field, and that other code was my way of doing it. Does anyone have any ideas?

 

[namliam]

You can make your queries, then use my " second version " to write the SQL/query into the query object.

Then use your OutputTo to export said query into any file you like.