Create a table from SELECT SQL statement

[firefly2k8]

I have a query which works fine if you enter and run it from the Access query window.

However one element of it is variable so I want to run this from within vba.

It is a select query that creates a neat two column table from a range of different tables etc.

I tried do:

strSQL = "SELECT..."
DoCmd.RunSQL strSQL

But this is rejected with an error.

So what I want is to run the query and have it create a table called "Spot_Pairs_Query" which i can then refer to with:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Spot_Pairs_Query", filepath, True, "SpotDataDrop"

I suspect I need SELECT INTO <new table> FROM... , but i cant get this working with my query.

My full query is:

SELECT [Trade Date], [Currency Pair] FROM [SE2 Project Table] WHERE Not [Currency Pair] Is Null And [Currency Pair]<>'' And [Type Forward/Spot] = 'Spot' and  [Project ID] = 47 UNION SELECT   [Trade Date], [Cross Pair] FROM [SE2 Project Table] WHERE Not [Cross Pair] Is Null And [Cross Pair] <> " And [Project ID] = 47 UNION SELECT   [Trade Date], [Buy to Base Pair] FROM [SE2 Project Table] WHERE Not [Buy to Base Pair] Is Null And [Buy to Base Pair]<>" and [Project ID] = 47;

 

[vbaInet]

Can you possibly explain why you need to make a table? Sounds like your tables aren't normalized.

I notice you have a table called, SE2 Project table, do you have one called SE1 Project table too?

 

[firefly2k8]

There is one instance of the project table. SE1 Client Table is the SE1 one.

I want to export the result of my query to excel. I have done this successfully using:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Spot_Pairs_Query", filepath, True, "SpotDataDrop"

But only when "Spot_Pairs_Query" is a stored query in the Access query object list.

But "Spot_Pairs_Query" changes according to the particular projectID i am interested in. So I can;t have a static query. So i thought best to create a table which I can reference with DoCmd.TransferSpreadsheet.

I have tried to do this with:

SELECT [SE2 Project Table].[Trade Date], [SE2 Project Table].[Currency Pair] INTO NewTable FROM [SE2 Project Table] WHERE Not [SE2 Project Table].[Currency Pair] Is Null And [SE2 Project Table].[Currency Pair]<>'' And [SE2 Project Table].[Type Forward/Spot] = 'Spot' and  [SE2 Project Table].[Project ID] = 47 UNION SELECT   [SE2 Project Table].[Trade Date], [Cross Pair] FROM [SE2 Project Table] WHERE Not [SE2 Project Table].[Cross Pair] Is Null And [SE2 Project Table].[Cross Pair] <> " And [SE2 Project Table].[Project ID] = 47 UNION SELECT   [SE2 Project Table].[Trade Date], [SE2 Project Table].[Buy to Base Pair] FROM [SE2 Project Table] WHERE Not [SE2 Project Table].[Buy to Base Pair] Is Null And [SE2 Project Table].[Buy to Base Pair]<>" and [SE2 Project Table].[Project ID] = 47;

But i get error "Action query cannot be used as row source"

 

[vbaInet]

You can change the SQL of a query in code.

Aircode:

dim qdf as dao.database

set qdf = currentdb.querydefs("Spot_Pairs_Query")

qdf.sql = "SELECT ..."

set qdf = nothing

docmd.transferspreadsheet acExport, "Spot_Pairs_Query"

So as you can see, no need of making a table.

 

[firefly2k8]

This nearly worked - thank you!

I had to play around with the structure a bit for some reason. In the end I went with:

Dim DB As DAO.Database
Dim qdf As DAO.QueryDef
Set DB = CurrentDb

With DB
strSQL = "SELECT ... ;"
Set qdf = .CreateQueryDef("Spot_Pairs_Query", strSQL)
End With
Set DB = Nothing
Set qdf = Nothing

And that seems to do the trick.

 

[vbaInet]

No, don't create a querydef in code. Glad you took the initiative though

Create a blank new query and call it Spot_Pairs_Query, and use the code I provided.

If you try to run your code a second time it will fail in the CreateQueryDef() line. Yes you can check if the query exists and delete it then create it again but that's just bloating your db and double work.

 

[firefly2k8]

qdf.sql gives the error:

"Compile error:
Method or data member not found"

are you sure we want dim qdf as dao.database rather than dao.querydef ?

 

[vbaInet]

are you sure we want dim qdf as dao.database rather than dao.querydef ?

See why I said aircode

Yes querydef

 

[firefly2k8]

Great, works a treat.

Thanks

 

[vbaInet]

You're welcome!