Create table from a recordset

[MikeKIA]

Hi,
I'm trying to create a new table from another existing two in vba. I'm using this code without success... Any help will be gratefully taken.
---------------------
Dim strSQL as string
Dim tablaSuma As DAO.TableDef
Dim rst As DAO.Recordset

strSQL = "SELECT * FROM Necesidades_TRS1, Pedidos WHERE Pedidos.Código=Necesidades_TRS1.Código"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set tablaSuma = CurrentDb.CreateTableDef("TablaSum")
For Each Campo In rst.Fields
tablaSuma.CreateField(Campo.Name, DB_SINGLE) = Campo
Next
----------------------
Mike

 

[neileg]

What's wrong with a make table query?

 

[MikeKIA]

Sorry but I'm still learning vba access. "Make table query"?????........

 

[boblarson]

You can create a make table query by going into the queries area in Access, clicking on new query and then creating a new query to have the data you want. Then, in design view of the query, go to QUERY > Make Table in the menu bar and follow the prompts. No VBA required, except maybe to call it.

 

[MikeKIA]

You can create a make table query by going into the queries area in Access, clicking on new query and then creating a new query to have the data you want. Then, in design view of the query, go to QUERY > Make Table in the menu bar and follow the prompts. No VBA required, except maybe to call it.

Ok, thanks. But what I really need is to automatize it in vba for the source tables will vary....

 

[boblarson]

If you create one in the query area, then go to SQL view, you will see the syntax required to pull it off. Then you can use that as the base from which you will modify it.

 

[MikeKIA]

Yes, the query is done as in my original post. The problem is that I need to write it in vba and this code doen't let me create a new table. It's a vba problem....

 

[boblarson]

No, I don't think you understand. You don't need to go into DAO to do this. Just create the SQL statement execute it using
CurrentDb.Execute strSQL
and the SQL statement you have listed is flawed as it doesn't have the INTO portion telling it what the table name will be.

A sample from my SQL window would be:

strSQL = "SELECT tblProducts.ProductID, tblProducts.ProductCode, tlkpProductCategories.ProductCategoryDesc INTO tblTest " & _
"FROM tlkpProductCategories INNER JOIN tblProducts ON tlkpProductCategories.ProductCategoryID = tblProducts.ProductCategory;"

CurrentDb.Execute strSQL

 

[MikeKIA]

I think I understand now.
Thank you very much for the quick reply.

Miguel