How to store a query into a temporary table

[Ben_Entrew]

Dear all,

I want to store a query into a table, which I will delete later on.
But somehow it shows me an error: Data type conversion error at the
qdf = CreateTableDef assignment line.
Thanks in advance guys.

Regards,
Ben

Public Sub LF_Query()
Dim i As Integer
Dim strSQL As String
Dim qdf As TableDef
strSQL = "SELECT LF.*,TRP_2013.[new_TRP_2013_in_EUR],TRP_2013.[old_TRP_2013_in_EUR],TRP_2013.[Margin],MRP_CODES.[Division], " & _
         " MRP_CODES.[MRP_Description],Customers.[Customer_Split] " & _
         " FROM " & _
         " ((LF LEFT JOIN TRP_2013 ON LF.[ID] = TRP_2013.[ID]) LEFT JOIN Customers ON LF.[Customer] = Customers.[Customer_NO]) " & _
         " LEFT JOIN MRP_CODES ON LF.[MRPC] = MRP_CODES.[MRP_Controller]"

    With CurrentDb
                
                Set qdf = CurrentDb.CreateTableDef("FC_TEMP", strSQL)
                .Close
    End With

End Sub

 

[CJ_London]

you don't do it that way.

modify your query so it is a make table query then just run the query using currentdb.execute

 

[pr2-eugin]

If you change your Query by adding INTO statement it will auto create the table for you..

strSQL = "SELECT LF.*,TRP_2013.[new_TRP_2013_in_EUR],TRP_2013.[old_TRP_2013_in_EUR],TRP_2013.[Margin],MRP_CODES.[Division], " & _
         " MRP_CODES.[MRP_Description],Customers.[Customer_Split] [COLOR=Red][B]INTO FC_TEMP[/B][/COLOR] " & _
         " FROM " & _
         " ((LF LEFT JOIN TRP_2013 ON LF.[ID] = TRP_2013.[ID]) LEFT JOIN Customers ON LF.[Customer] = Customers.[Customer_NO]) " & _
         " LEFT JOIN MRP_CODES ON LF.[MRPC] = MRP_CODES.[MRP_Controller]"
CurrentDb.Execute strSQL