select query with VBA & exporting

[jme]

hola!

I've been going in circles on google msdn etc, looking for a solution to my brat of a problem. The process Im trying to build up is as follows:

-query 3 tables by joining
-export results into a readable fileformat (xls/csv/xml) not too fussed

So far I've created my query & got some vba. see snip below:

Sub getTimes()

Dim rsCCure As ADODB.Recordset
Set rsCCure = New ADODB.Recordset

strSQL = "SELECT CCM_VIEW_PERSONCARD.[PERSONID]," & _
                "CCM_VIEW_PERSONCARD.[FIRSTNAME]," & _
                "CCM_VIEW_PERSONCARD.[LASTNAME]," & _
                "CCM_VIEW_PERSONCARD.[CARDNUM]," & _
                "PUB_Door.[Door_Name]," & _
                "PUB_Journ.[Local_DT]" & _
             "FROM PUB_Door" & _
                "INNER JOIN (CCM_VIEW_PERSONCARD INNER JOIN PUB_Journ ON CCM_VIEW_PERSONCARD.[PERSONID]=PUB_Journ.[User_PID])" & _
                "ON PUB_Door.[Door_ID]=PUB_Journ.[Int_Data1]" & _
             "WHERE PUB_Journ.[Local_DT] > 558316801" & _
             "ORDER BY PUB_Journ.[Local_DT];"

rsCCure.Source = strSQL
rsCCure.ActiveConnection = Application.CodeProject.Connection
rsCCure.Open


End Sub

Thx, J

 

[Guus2005]

You're welcome!
What's the problem?

 

[jme]

to gather up my results and throw them into a readable file - but im lost

 

[DJkarl]

to gather up my results and throw them into a readable file - but im lost

If all you want to do is export that query then why are you creating a recodset object? Just save your SQL as a query object then use the TranserText or TransferSpreadsheet method to export the query to a file.

 

[jme]

dude, i took the assumption that in order to do a SELECT statement you had to create a recordset?

 

[jme]

got the export to work

rsCCure.Save c:\filename.xml, adPersistXML

sorted