Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Unanswered: 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.

    Jim

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Create table x as
    Select columns from table2
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Posts
    67
    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

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Dim str1 As String
    st1 = "Select columns from table 2"
    ... "Create table x as " & str1
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Feb 2004
    Posts
    67
    R123456,

    Thanks much. I will try working with your suggestion.

    Jim

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Posts
    67
    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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    So you're not talking to SQL Server?

    Try here

    http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tsk, tsk, brett, sending someone elsewhere is definitely not nice when this site has a perfectly good Access forum itself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I've never scrolled down that far....I just thought they was dissing Access....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2004
    Posts
    67
    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

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2004
    Posts
    67
    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

  14. #14
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    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;

  15. #15
    Join Date
    Feb 2004
    Posts
    67
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •