Making a Table from a Query

I have a Query named qryQuery1 from which I would like to make a Table named tblTable1. I need to do this within a VBA Microsoft Access module.
Can anyone help me with the proper code?
Thank you for any help.
JimCreate table x as
Select columns from table2|||r123456,

Thank you for your response. Unfortunately, I am relatively new to this and do not understand what you mean by "Create Table x as select columns from Table 2". How do these relate to the qryQuery1 and Table1 that I am working with? Would it be too much trouble to ask for the actual code required?

Thank you again for your help.

Jim|||Dim str1 As String
st1 = "Select columns from table 2"
... "Create table x as " & str1|||R123456,

Thanks much. I will try working with your suggestion.

Jim|||USE Northwind
GO

--Uh No

CREATE TABLE myTable99 AS SELECT * FROM Orders
GO

--Uh Yes

SELECT * INTO myTable99 FROM Orders
Go

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Brett,

Thanks much for the suggestion. Actually I am trying to incorporate this into Microsoft Access VBA code and am coming to the conclusion that I am in the wrong forum. True?

Thanks again for your help.

Jim|||So you're not talking to SQL Server?

Try here

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3|||tsk, tsk, brett, sending someone elsewhere is definitely not nice when this site has a perfectly good Access forum (http://www.dbforums.com/f84/) itself|||I've never scrolled down that far...I just thought they was dissing Access...|||Thanks again Brett,

I guess I don't know enough to even know if I am "talking to SQL Server". I am just learning VBA for Access and that is where I am writing all my code. I have written a couple of SQL lines like:

SQL = "SELECT strTable1.Field1, strTable2.Field2 . . ."

followed by a line like:

Set qdef = CurrentDb.CreateQueryDef("qryTest", SQL)

But when I get what looks like "pure" SQL", I don't know what to do with it.

Thanks again.

Jim|||Yeah, it looks like your building Query objects on the fly in Access

If you set your variable to my syntax it will create the query that will create a table when it's executed...

But why do that?

Why not create the table ahead of time and just do inserts?|||Brett,

I believe that I need to create them on the fly because my two source tables are User-selected from a collection of existing tables. In addition, new tables may be added to these source tables.

I also let the User select a name for the table to be created that can then be stored along with other previously created tables. The created tables can than be retrieved as different scenarios.

Jim|||Originally posted by jpgalasso
Brett,

I believe that I need to create them on the fly because my two source tables are User-selected from a collection of existing tables. In addition, new tables may be added to these source tables.

I also let the User select a name for the table to be created that can then be stored along with other previously created tables. The created tables can than be retrieved as different scenarios.

Jim
If am not mistaken it is possible to run Make-table query in MS Access:

SELECT oldone.id, oldone.code INTO newone
FROM oldone;|||Originally posted by snail
If am not mistaken it is possible to run Make-table query in MS Access:

SELECT oldone.id, oldone.code INTO newone
FROM oldone;

I seem to need some kind of VBA "Create" or "Execute" statement following the above "SELECT" statement. Do you have any idea what the statement would look like? I tried the following but received an error message saying "Data type conversion error":

Set tbldef = CurrentDb.CreateTableDef("tblTable3", SQL)

Thanks again.

Jim|||Look at the vb help for command.execute

Execute, Requery, and Clear Methods Example (VB)

This example demonstrates the Execute method when run from both a Command object and a Connection object. It also uses the Requery method to retrieve current data in a Recordset, and the Clear method to clear the contents of the Errors collection. (The Errors collection is accessed via the Connection object of the ActiveConnection property of the Recordset.) The ExecuteCommand and PrintOutput procedures are required for this procedure to run.

Public Sub ExecuteX()

Dim strSQLChange As String
Dim strSQLRestore As String
Dim strCnn As String
Dim cnn1 As ADODB.Connection
Dim cmdChange As ADODB.Command
Dim rstTitles As ADODB.Recordset
Dim errLoop As ADODB.Error

' Define two SQL statements to execute as command text.
strSQLChange = "UPDATE Titles SET Type = " & _
"'self_help' WHERE Type = 'psychology'"
strSQLRestore = "UPDATE Titles SET Type = " & _
"'psychology' WHERE Type = 'self_help'"

' Open connection.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

' Create command object.
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = cnn1
cmdChange.CommandText = strSQLChange

' Open titles table.
Set rstTitles = New ADODB.Recordset
rstTitles.Open "titles", cnn1, , , adCmdTable

' Print report of original data.
Debug.Print _
"Data in Titles table before executing the query"
PrintOutput rstTitles

' Clear extraneous errors from the Errors collection.
cnn1.Errors.Clear

' Call the ExecuteCommand subroutine to execute cmdChange command.
ExecuteCommand cmdChange, rstTitles

' Print report of new data.
Debug.Print _
"Data in Titles table after executing the query"
PrintOutput rstTitles

' Use the Connection object's execute method to
' execute SQL statement to restore data. Trap for
' errors, checking the Errors collection if necessary.
On Error GoTo Err_Execute
cnn1.Execute strSQLRestore, , adExecuteNoRecords
On Error GoTo 0

' Retrieve the current data by requerying the recordset.
rstTitles.Requery

' Print report of restored data.
Debug.Print "Data after executing the query " & _
"to restore the original information"
PrintOutput rstTitles

rstTitles.Close
cnn1.Close

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If rstTitles.ActiveConnection.Errors.Count >= 0 Then
For Each errLoop In rstTitles.ActiveConnection.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If
Resume Next

End Sub

Public Sub ExecuteCommand(cmdTemp As ADODB.Command, _
rstTemp As ADODB.Recordset)

Dim errLoop As Error

' Run the specified Command object. Trap for
' errors, checking the Errors collection if necessary.
On Error GoTo Err_Execute
cmdTemp.Execute
On Error GoTo 0

' Retrieve the current data by requerying the recordset.
rstTemp.Requery

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If rstTemp.ActiveConnection.Errors.Count > 0 Then
For Each errLoop In Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

End Sub

Public Sub PrintOutput(rstTemp As ADODB.Recordset)

' Enumerate Recordset.
Do While Not rstTemp.EOF
Debug.Print " " & rstTemp!Title & _
", " & rstTemp!Type
rstTemp.MoveNext
Loop

End Sub|||Originally posted by jpgalasso
I seem to need some kind of VBA "Create" or "Execute" statement following the above "SELECT" statement. Do you have any idea what the statement would look like? I tried the following but received an error message saying "Data type conversion error":

Set tbldef = CurrentDb.CreateTableDef("tblTable3", SQL)

Thanks again.

Jim

This works from Access and I guess will work with ADO (execute).

Public Sub DoSQL()
Dim SQL As String

SQL = "SELECT oldone.id, oldone.code INTO newone333 FROM oldone; "
DoCmd.RunSQL SQL
End Sub|||Originally posted by Brett Kaiser
Look at the vb help for command.execute

Thank you very much Brett. This will be extremely helpful for both my existing and future projects.

Jim|||Originally posted by jpgalasso
Thank you very much Brett. This will be extremely helpful for both my existing and future projects.

Jim

For some reason the DoCmd.RunSQL statement, while OK for most SQL statements, does not work with a "SELECT" SQL - I guess it would just make too much sense.

Jim|||What do you mean?|||Originally posted by Brett Kaiser
What do you mean?

Sorry this was in response to "Snail" 's comment who suggested that I try DoCmd.RunSQL with the SQL "SELECT" statement. I do not believe that DoCmd.RunSQL works with the "SELECT" statement.

Jim|||Originally posted by jpgalasso
Sorry this was in response to "Snail" 's comment who suggested that I try DoCmd.RunSQL with the SQL "SELECT" statement. I do not believe that DoCmd.RunSQL works with the "SELECT" statement.

Jim

As you can notice there is 'select *** into from ***'. I tested it in Access modules/new and paste, run - it works.

Public Sub DoSQL()
Dim SQL As String
SQL = "SELECT oldone.id, oldone.code INTO newone333 FROM oldone; "
DoCmd.RunSQL SQL
End Sub|||Snail,

You have my apologies and my thanks. I tried your code and it worked great. Since I am creating a Table from a Query, I do get a warning that I am about to add XXX records to a Table. Do you know how I might be able to turn off the warning message.

Thanks again.

Jim|||I think you want the docmd.RunSQL command.|||Originally posted by blindman
I think you want the docmd.RunSQL command.

blindman,

Thank you. Yes, as Snail also suggested, the docmd.RUNSQL command works fine. The only issue I have is that since I am creating a Table from a Query, I get a warning message that XXX records are about to be added to a Table. Do you know how I might turn off that warning?

Jim|||Originally posted by jpgalasso
blindman,

Thank you. Yes, as Snail also suggested, the docmd.RUNSQL command works fine. The only issue I have is that since I am creating a Table from a Query, I get a warning message that XXX records are about to be added to a Table. Do you know how I might turn off that warning?

Jim

Just add DoCmd.SetWarnings False:

Public Sub DoSQL()
Dim SQL As String
DoCmd.SetWarnings False
SQL = "SELECT oldone.id, oldone.code INTO newone555 FROM oldone; "
DoCmd.RunSQL SQL
End Sub|||Originally posted by snail
Just add DoCmd.SetWarnings False:

Public Sub DoSQL()
Dim SQL As String
DoCmd.SetWarnings False
SQL = "SELECT oldone.id, oldone.code INTO newone555 FROM oldone; "
DoCmd.RunSQL SQL
End Sub

Snail,

Thanks so much. Very, very helpful!!

Jim|||Whoa! Remember to issue a Docmd.setwarnings True at the end of your procedure, otherwise you turn off all the warnings for Access. Change a form and it won't prompt you to save. Run an update query and it won't prompt you to verify. Bad.

As a matter of fact, I often put a macro in my Access applications that just set the setwarnings status to true, so that if my code crashes halfway through a procedure I can easily restore the setting.|||Originally posted by blindman
Whoa! Remember to issue a Docmd.setwarnings True at the end of your procedure, otherwise you turn off all the warnings for Access. Change a form and it won't prompt you to save. Run an update query and it won't prompt you to verify. Bad.

As a matter of fact, I often put a macro in my Access applications that just set the setwarnings status to true, so that if my code crashes halfway through a procedure I can easily restore the setting.

Thanks for the additional tip. Yes, I will reset to True right after I need the "False" setting.