Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create / Make Table in VB from existing query

Status
Not open for further replies.

Maturi

IS-IT--Management
Oct 9, 2003
81
GB
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

 
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.
 
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?
 
What about using a variable in the VBA code to edit the SQL statement according to need?

Example to follow...
 
Example:
Code:
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
 
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
 
Try:

Code:
sub btn1_click()
docmd.runsql "SELECT qry1.MyIdCopy , no AS SelectFlag INTO aaa FROM qry1;"
end sub
 
Sorry if my example was too fruity! [wink] Try pwise's suggestion, and if that doesn't work, we'll go from there.
 
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
 
Rather than a MAKE TABLE (which not only creates the table but also copies records to it) try SQL like
Code:
CurrentDb.Execute _
"CREATE TABLE myTable " & _
"([MyIDCopy] Text (50), " & _
" [SelectFlag] Bit ) "
If you then need to populate the new table then
Code:
CurrentDb.Execute _
"INSERT INTO myTable (myIDCopy, SelectFlag) " & _
"Select myIdCopy, True As [SelectFlag] " & _
"From qry1 " & _
"Where ... Some condition to select the records to copy ..."
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top