Create / Make Table in VB from existing query

[Maturi]

Hi

This should be really easy but....

I have a Query (qry1) which returns several fields including MyID (Integer)

At a click of a button I want to create a new table with the fields
- MyIdCopy (copied from qry1)
- SelectFlag (Y/N flag) - default set to zero

I could do the CREATE table to create a table and I can do SELECT... INTO to copy from qry1.

But I don't know how to combine the 2.

Any help appreciated

 

[kjv1611]

Have you tried making a "create table" query in Access, then look at the SQL there? That creates the table, and puts the data into the table - you should be able to copy and paste the SQL, and maybe edit a little bit of it.

 

[Maturi]

KJV

Thanks - I have tried that. I have a SELECT..INTO statement which allows me to create a table populated by an existing field in a query but I can't see how to a new field SelectFlag which has nothing to do with the qry1

Any other suggestions?

 

[kjv1611]

What about using a variable in the VBA code to edit the SQL statement according to need?

Example to follow...

 

[kjv1611]

Example:

Private Sub RunQuery()
  Dim strTreeName
  Dim strSQL As String
  strTreeName = cboTreeComboBox
  strSQL = "SELECT a.FruitID, a.FruitColor, a.FruitFlavor " & _
          "INTO NewTable " & _
          "FROM FruitTable a INNER JOIN " & _
               "TreeTable t ON a.FruitID = t.FruitID " & _
          "WHERE t.TreeName = '" & strTreeName & "'" 
  DoCmd.RunSQL strSQL
End Sub

 

[Maturi]

KJV

Thanks - I'm really sorry but I did not understand the example you gave - it's my inexperience

All I want to do is create a new table with 2 fields.
- MyIdCopy (Integer - copied from qry1)
- SelectFlag (Y/N flag) - default set to zero

Can you help me code this?

Thank you
M

 

[PWise]

Try:

sub btn1_click()
docmd.runsql "SELECT qry1.MyIdCopy , no AS SelectFlag INTO aaa FROM qry1;"
end sub

 

[kjv1611]

Sorry if my example was too fruity! Try pwise's suggestion, and if that doesn't work, we'll go from there.

 

[Maturi]

Pwise

Thanks again - It did work. I knew it would be something simple.

One final niggle - I want SlectFlag to be a Y/N field rather than the number field that it seems to default to.

Or am I asking for too much ??

Thanks guys

 

[Golom]

Rather than a MAKE TABLE (which not only creates the table but also copies records to it) try SQL like

CurrentDb.Execute _
"CREATE TABLE myTable " & _
"([MyIDCopy] Text (50), " & _
" [SelectFlag] Bit ) "

If you then need to populate the new table then

CurrentDb.Execute _
"INSERT INTO myTable (myIDCopy, SelectFlag) " & _
"Select myIdCopy, True As [SelectFlag] " & _
"From qry1 " & _
"Where ... Some condition to select the records to copy ..."