generate a table from a sql string VBA

[casey]

generate a table from a sql string VBA/DAO

Is there a way in VBA/DAO to create a table off of a query. I would like to reference a query and generate a table that's a direct copy of the data in the query. The ideal would be if a query could be selected and a table name could be generated "on-the-fly" and reused without having to use separate "make-table" queries.

Any ideas on the best way to do this?

Thanks.

 

[Bat17]

I have not tested this on anything but simple queries and there is no error checking but it should give you the idea

Sub MakeTableTest()
Dim strIn As String
Dim strOut As String
Dim strName As String
Dim strQuery As String

strQuery = "qryMyQuery"
strName = "tblMyTable"

strIn = CurrentDb.QueryDefs(strQuery).SQL
strOut = Replace(strIn, "From", " INTO [" & strName & "] FROM")

CurrentDb.Execute strOut
End Sub

Peter

 

[casey]

Bat17,

I've had a chance to look into this and the way the question is posed, your reply makes sense. Thanks. It seems I'm looking for something different, though.

What I need is to make a table that is a copy of the resultset returned by the query which is different from actually copying the sql statement from the query into the table.

What I want to do is...Once I run the query, I get the results in a recordset. I would be like to take that recordset and save it as a table in my database.

I'm well versed in making dao recordsets and think that this is probably what I need to do. Is there someway to take a recordset and create a table directly off of its results?

I can see a manual way to do this...by clicking on the upper-left corner of the results returned by the query, copying it, exporting it to a spreadheet and then importing it into my database as a table. There has to be a better way than that.

HTMS. Any ideas?

 

[Bat17]

The code does not copy the SQL into the table. it creates a maketable query using the SQL from the original query by inserting INTO with the chosen table name

So

SELECT tblProductDetails.ProductsID, tblProductDetails.PRODUCT_CODE
FROM tblProductDetails
WHERE (((tblProductDetails.LineID)="2m" Or (tblProductDetails.LineID)="3m"))
WITH OWNERACCESS OPTION;

Turns into

SELECT tblProductDetails.ProductsID, tblProductDetails.PRODUCT_CODE
INTO [tblMyTable] FROM tblProductDetails
WHERE (((tblProductDetails.LineID)="2m" Or (tblProductDetails.LineID)="3m"))
WITH OWNERACCESS OPTION;

and when run byExecute creates the table 'tblMyTable' with the same data set as the original query.

Peter

 

[casey]

Bat17,

A couple of things...

Does the strQuery and strName need to be the name of an actual table and a query that already exist in the database? Because I would need to define the query and specify the table name through VB at the time the code is run.

Also the query that I'm running is a very complex crosstab query that puts a TRANSFORM statement before the query and a PIVOT statement after the query. It acts kind of like a pivot table for the query. Because of the TRANSFORM and PIVOT statements, I'm not sure that this type of query will properly translate into a make-table query with the command provided.

I've been known to miss things from time to time. Does it still seem to you that the command you provided will still work? I guess that this post should have been titled "copy a recordset to a table"?

Thanks again. I really appreciate your help.

 

[Bat17]

Does the strQuery and strName need to be the name of an actual table and a query that already exist in the database? Because I would need to define the query and specify the table name through VB at the time the code is run.

The table does not have to exist but the query does, from the original question I thought that you wanted to choose an existing query

I'm not sure that this type of query will properly translate into a make-table query with the command provided.

I dont think it will either, but if the query is saved it can still be done using SQL looking at the saved query.

If the query is purley in memory then I dont know how to do what you want. It should be possible to do it the brute force way by looping through every field to create a table then looping through every record to add it to the table.
It would probably be easier to just save the QueryDef and delete it afterwards though.

Peter

 

[casey]

Peter,

Yes, that's exactly what I want. I apologize for not stating the original question more specifically. I am considering using the CreateTableDef method, but I'm waiting to see if there would be an easier way before I did all of that. I'll keep looking.

Thanks for your help.

 

[workmad3]

Just as a quick question. Why would you want to store the results of a query into a separate table? The point of a query is to not do this, and doing so breaks the fundamental idea of non redundant data that is central to a db. (I'm not saying that in some cases its not neccesarry, I'm just suggesting that perhaps saving the queries would be a more suitable alternative to having lots of data stored over and over again)

 

[casey]

Why would you want to store the results of a query into a separate table? The point of a query is to not do this, and doing so breaks the fundamental idea of non redundant data that is central to a db.

Perfectly valid argument. What will be created is a temporary table that is a necessary evil. I could create a query I suppose. Also an Excel spreadsheet would work, but I wanted to keep the data local (in the database) and not have to import it from a spreadsheet.

What gets me is that I can create a spreadsheet from a recordset, but I can't find a way to dump the data to a table or query.

 

[Summerwind]

What you could also do is to create your temp table with ADOX then use an INSERT INTO SQL clause to export the required data directly from your source table into the newly created temp table.

Admitted that the SQL code needs a bit of work but once you get it right the process is very quick.

When the life of the temp table is over, simply use ADOX to delete it.

I use this technique quite often where I want to retain the data in the temp table for use by several forms.

 

[casey]

Copy entire record and paste it???

Thanks Summerwind,

I broke down and developed an entire procedure to create tables and delete tables with the CreateTableDef method. Which is sort of the same thing. I have that code working (sort of), but now I need to populate the tables. Ohh boy.

Anyone know of a good way to copy an entire record and to paste it into the new table?

 

[thh]

Also the query that I'm running is a very complex crosstab query that puts a TRANSFORM statement before the query and a PIVOT statement after the query. ... Because of the TRANSFORM and PIVOT statements, I'm not sure that this type of query will properly translate into a make-table query with the command provided.

You want to dump the record set from a query into a table. Normally, a make-table query is used for this operation. But in the case of a complex TRANSFORM ... PIVOT query, a two step procedure will do the trick.

Elaborating on the point of Bat17:

... if the query is saved it can still be done using SQL looking at the saved query

I will suggest:

Step 1: Create the complex query (qryPivot)
Step 2: Run a make-table query where you select all fields from the complex query (into NewTable). It might be implemented like:

Sub SaveComplexQueryIntoTable()
   Dim qdf As DAO.QueryDef
   strSql = "TRANSFORM ... PIVOT query goes here "

'STEP 1:
    Set qdf = CurrentDb.CreateQueryDef("qryPivot", strSql)
    qdf.Close

'STEP 2:
    CurrentDb.Execute "SELECT * INTO NewTable FROM qryPivot;"
End Sub

This will take care of your stated need:

I would need to define the query and specify the table name through VB at the time the code is run.

thh