sql statement help

[Guest]

I am attempting to use an sql statement to create a table (I realize creating
a table is not the most efficient means). Here is the code:

Dim rst As DAO.Recordset
Dim dbs As Database
Set dbs = CurrentDb
Dim strSQL As String
strSQL = "CREATE tblTEMP(HN, Name) " & _
"SELECT HN, Name " & _
"FROM tblP1 WHERE HN = 1"
MsgBox ("SQL statement " & strSQL)
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

I receive a syntax error message upon execution. Could you please aid me in
understanding the proper syntax for such a command. Also, how can I
retrieve/handle such an SQL statement through user defined variables?

Thanks so much for your time in reviewing my question(s).

 

[Squirrel]

Hi Erik,

Try something like this:

Dim strSQL As String

strSQL = "SELECT ID, MyDate INTO tblNew FROM Table1;"
DoCmd.RunSQL strSQL

HTH -Linda

 

[Guest]

Worked great and I now have the idea. Off to learn more. Thank you