Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL sentence for make table

Discussion in 'Information Technology' started by yaniv d, Jul 28, 2006.

  1. yaniv d

    yaniv d
    Expand Collapse
    Guest

    hi all,
    i built SQL sentences that are making tables dinamicly,
    my problem is that everytime a table is going to be made,the is a
    message asking for approval of the make table.
    how can i make the table without this message?
     
  2. Loading...


  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: SQL sentence for make table -- use CurrentDb.Execute

    instead of using

    doCmd.RunSQL strSQL

    use

    CurrentDb.Execute strSQL, dbFailOnError

    why are you making a bunch of tables as opposed to using
    select queries?

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    yaniv d wrote:
    > hi all,
    > i built SQL sentences that are making tables dinamicly,
    > my problem is that everytime a table is going to be made,the is a
    > message asking for approval of the make table.
    > how can i make the table without this message?
    >
     
  4. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Yaniv,

    I assume you are using the
    DoCmd.OpenQuery
    method in your code, to run the make-table query.

    You can put this before...
    DoCmd.SetWarnings False

    .... and this after...
    DoCmd.SetWarnings True

    --
    Steve Schapel, Microsoft Access MVP

    yaniv d wrote:
    > hi all,
    > i built SQL sentences that are making tables dinamicly,
    > my problem is that everytime a table is going to be made,the is a
    > message asking for approval of the make table.
    > how can i make the table without this message?
    >
     
  5. yaniv d

    yaniv d
    Expand Collapse
    Guest

    Re: SQL sentence for make table -- use CurrentDb.Execute

    i need to make a table contain data from a table and a query when
    opening a form and delete the table after leaving the form

    thanks for your help

    strive4peace wrote:
    > instead of using
    >
    > doCmd.RunSQL strSQL
    >
    > use
    >
    > CurrentDb.Execute strSQL, dbFailOnError
    >
    > why are you making a bunch of tables as opposed to using
    > select queries?
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > yaniv d wrote:
    > > hi all,
    > > i built SQL sentences that are making tables dinamicly,
    > > my problem is that everytime a table is going to be made,the is a
    > > message asking for approval of the make table.
    > > how can i make the table without this message?
    > >
     
  6. Michael Gramelspacher

    Michael Gramelspacher
    Expand Collapse
    Guest

    In article <1150097288.394383.10020@y43g2000cwc.googlegroups.com>,
    yaniv.dg@gmail.com says...
    > hi all,
    > i built SQL sentences that are making tables dinamicly,
    > my problem is that everytime a table is going to be made,the is a
    > message asking for approval of the make table.
    > how can i make the table without this message?
    >
    >

    You should look for a Microsoft Access 2000 Technical Article,
    Fundamental Microsoft Jet SQL for Access 2000.

    An example of a Make Table query:

    Dim cmd As New ADODB.Command
    Dim strSQL As String

    cmd.ActiveConnection = CurrentProject.AccessConnection

    strSQL = "CREATE TABLE MediaOptions " & _
    "(media_type INTEGER NOT NULL PRIMARY KEY, " & _
    "description CHAR(10) NOT NULL, " & _
    "price DECIMAL(12, 4) NOT NULL, " & _
    "CHECK (price >= 0.00)); "
    cmd.CommandText = strSQL
    cmd.Execute
    'Debug.Print "Table MediaOptions created."

    strSQL = "CREATE TABLE Sales " & _
    "(sales_ticket INTEGER NOT NULL, " & _
    "CONSTRAINT validate_sales_ticket " & _
    "CHECK (sales_ticket LIKE '[0-9][0-9][0-9][0-9][0-9]'), " & _
    "media_type INTEGER NOT NULL " & _
    "References MediaOptions(media_type) " & _
    " ON UPDATE CASCADE, " & _
    "sale_date DATETIME DEFAULT Now() NOT NULL);"
    cmd.CommandText = strSQL
    cmd.Execute
    'Debug.Print "Table Sales created."
     
  7. yaniv d

    yaniv d
    Expand Collapse
    Guest

    thanks alot to all of you!!!

    Michael Gramelspacher wrote:
    > In article <1150097288.394383.10020@y43g2000cwc.googlegroups.com>,
    > yaniv.dg@gmail.com says...
    > > hi all,
    > > i built SQL sentences that are making tables dinamicly,
    > > my problem is that everytime a table is going to be made,the is a
    > > message asking for approval of the make table.
    > > how can i make the table without this message?
    > >
    > >

    > You should look for a Microsoft Access 2000 Technical Article,
    > Fundamental Microsoft Jet SQL for Access 2000.
    >
    > An example of a Make Table query:
    >
    > Dim cmd As New ADODB.Command
    > Dim strSQL As String
    >
    > cmd.ActiveConnection = CurrentProject.AccessConnection
    >
    > strSQL = "CREATE TABLE MediaOptions " & _
    > "(media_type INTEGER NOT NULL PRIMARY KEY, " & _
    > "description CHAR(10) NOT NULL, " & _
    > "price DECIMAL(12, 4) NOT NULL, " & _
    > "CHECK (price >= 0.00)); "
    > cmd.CommandText = strSQL
    > cmd.Execute
    > 'Debug.Print "Table MediaOptions created."
    >
    > strSQL = "CREATE TABLE Sales " & _
    > "(sales_ticket INTEGER NOT NULL, " & _
    > "CONSTRAINT validate_sales_ticket " & _
    > "CHECK (sales_ticket LIKE '[0-9][0-9][0-9][0-9][0-9]'), " & _
    > "media_type INTEGER NOT NULL " & _
    > "References MediaOptions(media_type) " & _
    > " ON UPDATE CASCADE, " & _
    > "sale_date DATETIME DEFAULT Now() NOT NULL);"
    > cmd.CommandText = strSQL
    > cmd.Execute
    > 'Debug.Print "Table Sales created."
     
  8. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Re: SQL sentence for make table -- use CurrentDb.Execute

    Yaniv,

    As Crystal intimated, this is a very unusual requirement, and I also
    would suspect there is an easier way.

    --
    Steve Schapel, Microsoft Access MVP

    yaniv d wrote:
    > i need to make a table contain data from a table and a query when
    > opening a form and delete the table after leaving the form
     
  9. yaniv d

    yaniv d
    Expand Collapse
    Guest

    Re: SQL sentence for make table -- use CurrentDb.Execute

    there is a simple way.but my problem for now is that i need to make it
    for a multi-user and that complicating the issue becaouse i need to
    make tables that will be attached for every user that will use it

    Steve Schapel wrote:
    > Yaniv,
    >
    > As Crystal intimated, this is a very unusual requirement, and I also
    > would suspect there is an easier way.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > yaniv d wrote:
    > > i need to make a table contain data from a table and a query when
    > > opening a form and delete the table after leaving the form
     
  10. John Vinson

    John Vinson
    Expand Collapse
    Guest

    Re: SQL sentence for make table -- use CurrentDb.Execute

    On 12 Jun 2006 22:48:36 -0700, "yaniv d" <yaniv.dg@gmail.com> wrote:

    >there is a simple way.but my problem for now is that i need to make it
    >for a multi-user and that complicating the issue becaouse i need to
    >make tables that will be attached for every user that will use it


    Why?

    What can you do with individual tables which cannot be done with a
    select query, filtering on the UserID field (which you might need to
    add to your table)? Storing data (a user's identity) in a tablename is
    probably a Very Bad Idea.

    Or, could you use a split database, with the source table in the
    backend, and the individual tables in each user's frontend .mdb file?

    John W. Vinson[MVP]
     
  11. yaniv d

    yaniv d
    Expand Collapse
    Guest

    Re: SQL sentence for make table -- use CurrentDb.Execute

    i wasnt to build virutal queries(based on SQL sentence of course) that
    will change the grouping depending on a code
    John Vinson wrote:
    > On 12 Jun 2006 22:48:36 -0700, "yaniv d" <yaniv.dg@gmail.com> wrote:
    >
    > >there is a simple way.but my problem for now is that i need to make it
    > >for a multi-user and that complicating the issue becaouse i need to
    > >make tables that will be attached for every user that will use it

    >
    > Why?
    >
    > What can you do with individual tables which cannot be done with a
    > select query, filtering on the UserID field (which you might need to
    > add to your table)? Storing data (a user's identity) in a tablename is
    > probably a Very Bad Idea.
    >
    > Or, could you use a split database, with the source table in the
    > backend, and the individual tables in each user's frontend .mdb file?
    >
    > John W. Vinson[MVP]
     
  12. John Vinson

    John Vinson
    Expand Collapse
    Guest

    Re: SQL sentence for make table -- use CurrentDb.Execute

    On 13 Jun 2006 08:03:25 -0700, "yaniv d" <yaniv.dg@gmail.com> wrote:

    >i wasnt to build virutal queries(based on SQL sentence of course) that
    >will change the grouping depending on a code


    I do not understand. What does this have to do with a MakeTable query?
    What is a "virtual query"? What is the "code"?

    John W. Vinson[MVP]
     
  13. aaron.kempf@gmail.com

    aaron.kempf@gmail.com
    Expand Collapse
    Guest

    if you must; use Docmd.SetWarnings = False before this and it won't
    prompt you.




    Michael Gramelspacher wrote:
    > In article <1150097288.394383.10020@y43g2000cwc.googlegroups.com>,
    > yaniv.dg@gmail.com says...
    > > hi all,
    > > i built SQL sentences that are making tables dinamicly,
    > > my problem is that everytime a table is going to be made,the is a
    > > message asking for approval of the make table.
    > > how can i make the table without this message?
    > >
    > >

    > You should look for a Microsoft Access 2000 Technical Article,
    > Fundamental Microsoft Jet SQL for Access 2000.
    >
    > An example of a Make Table query:
    >
    > Dim cmd As New ADODB.Command
    > Dim strSQL As String
    >
    > cmd.ActiveConnection = CurrentProject.AccessConnection
    >
    > strSQL = "CREATE TABLE MediaOptions " & _
    > "(media_type INTEGER NOT NULL PRIMARY KEY, " & _
    > "description CHAR(10) NOT NULL, " & _
    > "price DECIMAL(12, 4) NOT NULL, " & _
    > "CHECK (price >= 0.00)); "
    > cmd.CommandText = strSQL
    > cmd.Execute
    > 'Debug.Print "Table MediaOptions created."
    >
    > strSQL = "CREATE TABLE Sales " & _
    > "(sales_ticket INTEGER NOT NULL, " & _
    > "CONSTRAINT validate_sales_ticket " & _
    > "CHECK (sales_ticket LIKE '[0-9][0-9][0-9][0-9][0-9]'), " & _
    > "media_type INTEGER NOT NULL " & _
    > "References MediaOptions(media_type) " & _
    > " ON UPDATE CASCADE, " & _
    > "sale_date DATETIME DEFAULT Now() NOT NULL);"
    > cmd.CommandText = strSQL
    > cmd.Execute
    > 'Debug.Print "Table Sales created."
     
  14. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    "aaron.kempf@gmail.com" <aaron.kempf@gmail.com> wrote:

    >if you must; use Docmd.SetWarnings = False before this and it won't
    >prompt you.


    I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
    command instead of docmd.runsql. For ADO use
    CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
    adCmdText

    If you're going to use docmd.setwarnings make very sure you put the
    True statement in any error handling code as well. Otherwise weird
    things may happen later on especially while you are working on the
    app. For example you will no longer get the "Do you wish to save your
    changes" message if you close an object. This may mean that unwanted
    changes, deletions or additions will be saved to your MDB.

    Also performance can be significantly different between the two
    methods. One posting stated currentdb.execute took two seconds while
    docmd.runsql took eight seconds. As always YMMV.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
     
  15. aaron.kempf@gmail.com

    aaron.kempf@gmail.com
    Expand Collapse
    Guest

    you should NEVER use currentDB.Execute.

    MAYBE you could use DBS.Execute

    come on Tony.. are you drunk today?

    I don't believe that ADO is that much slower than DAO.. i don't believe
    it.

    More importantly; DAO is DED and you should run away while you still
    can.

    Anyone that still uses MDB or DAO?
    you should spit on them.




    Tony Toews wrote:
    > "aaron.kempf@gmail.com" <aaron.kempf@gmail.com> wrote:
    >
    > >if you must; use Docmd.SetWarnings = False before this and it won't
    > >prompt you.

    >
    > I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
    > command instead of docmd.runsql. For ADO use
    > CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
    > adCmdText
    >
    > If you're going to use docmd.setwarnings make very sure you put the
    > True statement in any error handling code as well. Otherwise weird
    > things may happen later on especially while you are working on the
    > app. For example you will no longer get the "Do you wish to save your
    > changes" message if you close an object. This may mean that unwanted
    > changes, deletions or additions will be saved to your MDB.
    >
    > Also performance can be significantly different between the two
    > methods. One posting stated currentdb.execute took two seconds while
    > docmd.runsql took eight seconds. As always YMMV.
    >
    > Tony
    > --
    > Tony Toews, Microsoft Access MVP
    > Please respond only in the newsgroups so that others can
    > read the entire thread of messages.
    > Microsoft Access Links, Hints, Tips & Accounting Systems at
    > http://www.granite.ab.ca/accsmstr.htm
     
  16. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Aaron,

    why not?

    I know that using currentdb doesn't work when you are
    defining, for instance, a tdf object variable and have heard
    that if you use it too much, the number of databases open
    can exceed limits -- is this why?

    I use DAO -- but, please, don't spit on me! Old habits die
    hard and it is what I truly understand...

    as a teacher, I never protect anything I write because I
    want others to understand and take the reins someday... so
    I do the MDB thing too...


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    aaron.kempf@gmail.com wrote:
    > you should NEVER use currentDB.Execute.
    >
    > MAYBE you could use DBS.Execute
    >
    > come on Tony.. are you drunk today?
    >
    > I don't believe that ADO is that much slower than DAO.. i don't believe
    > it.
    >
    > More importantly; DAO is DED and you should run away while you still
    > can.
    >
    > Anyone that still uses MDB or DAO?
    > you should spit on them.
    >
    >
    >
    >
    > Tony Toews wrote:
    >
    >>"aaron.kempf@gmail.com" <aaron.kempf@gmail.com> wrote:
    >>
    >>
    >>>if you must; use Docmd.SetWarnings = False before this and it won't
    >>>prompt you.

    >>
    >>I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
    >>command instead of docmd.runsql. For ADO use
    >>CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
    >>adCmdText
    >>
    >>If you're going to use docmd.setwarnings make very sure you put the
    >>True statement in any error handling code as well. Otherwise weird
    >>things may happen later on especially while you are working on the
    >>app. For example you will no longer get the "Do you wish to save your
    >>changes" message if you close an object. This may mean that unwanted
    >>changes, deletions or additions will be saved to your MDB.
    >>
    >>Also performance can be significantly different between the two
    >>methods. One posting stated currentdb.execute took two seconds while
    >>docmd.runsql took eight seconds. As always YMMV.
    >>
    >>Tony
    >>--
    >>Tony Toews, Microsoft Access MVP
    >> Please respond only in the newsgroups so that others can
    >>read the entire thread of messages.
    >> Microsoft Access Links, Hints, Tips & Accounting Systems at
    >>http://www.granite.ab.ca/accsmstr.htm

    >
    >
     
  17. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Tony,

    What is "YMMV" ?

    thanks for the performance comment -- I did not realize it
    could make that big of a difference!

    are all DoCmd actions inefficient?

    On a different note, someone was having trouble importing
    data into Access using TransferText -- USING an import spec
    that defined one of the columns to be text (although, until
    row 35, the data was purely numeric) -- Access STILL
    wouldn't bring those fields in without a dummy row at the
    top to set data types -- any thoughts (without writing code
    to do it?)

    Thanks, Tony

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Tony Toews wrote:
    > "aaron.kempf@gmail.com" <aaron.kempf@gmail.com> wrote:
    >
    >
    >>if you must; use Docmd.SetWarnings = False before this and it won't
    >>prompt you.

    >
    >
    > I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
    > command instead of docmd.runsql. For ADO use
    > CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
    > adCmdText
    >
    > If you're going to use docmd.setwarnings make very sure you put the
    > True statement in any error handling code as well. Otherwise weird
    > things may happen later on especially while you are working on the
    > app. For example you will no longer get the "Do you wish to save your
    > changes" message if you close an object. This may mean that unwanted
    > changes, deletions or additions will be saved to your MDB.
    >
    > Also performance can be significantly different between the two
    > methods. One posting stated currentdb.execute took two seconds while
    > docmd.runsql took eight seconds. As always YMMV.
    >
    > Tony
     
  18. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    "aaron.kempf@gmail.com" <aaron.kempf@gmail.com> wrote:

    >you should NEVER use currentDB.Execute.
    >
    >MAYBE you could use DBS.Execute


    Why?

    >I don't believe that ADO is that much slower than DAO.. i don't believe
    >it.


    I never said that. I said that docmd.runsql can be slower.

    >More importantly; DAO is DED and you should run away while you still
    >can.


    Rubbish.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
     
  19. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    strive4peace <"strive4peace2006 at yahoo dot com"> wrote:

    >What is "YMMV" ?


    Your mileage can vary.

    >On a different note, someone was having trouble importing
    >data into Access using TransferText -- USING an import spec
    >that defined one of the columns to be text (although, until
    >row 35, the data was purely numeric) -- Access STILL
    >wouldn't bring those fields in without a dummy row at the
    >top to set data types -- any thoughts (without writing code
    >to do it?)


    I always do my text/CSV file imports using code and input #.

    Sample code is

    Dim TextLine
    Open "TESTFILE" For Input As #1 ' Open file.
    Do While Not EOF(1) ' Loop until end of file.
    Line Input #1, TextLine ' Read line into variable.
    Debug.Print TextLine ' Print to the Immediate window.
    Loop
    Close #1 ' Close file.

    But the above assumes that there is only one text data per line.

    At least I think that's the function I used the last time I coded
    something like that.

    Tony
    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
     
  20. strive4peace

    strive4peace
    Expand Collapse
    Guest

    thanks, Tony!

    Oh! don't you mean MAY vary? Now I know a new acronym -- yeah!

    Yesterday, I decided it would be a good idea to learn one
    new word a day ... this counts! :)

    on the TransferText ...just wondered if there was a problem
    with the macro action in this situation -- there certainly
    seems to be -- I tried to get it to work for longer than I
    care to admit! Having to open each text file and insert a
    dummy row at the top should not be necessary ... Personally,
    I use code too... but this poster was not a coder...

    Thanks again!

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Tony Toews wrote:
    > strive4peace <"strive4peace2006 at yahoo dot com"> wrote:
    >
    >
    >>What is "YMMV" ?

    >
    >
    > Your mileage can vary.
    >
    >
    >>On a different note, someone was having trouble importing
    >>data into Access using TransferText -- USING an import spec
    >>that defined one of the columns to be text (although, until
    >>row 35, the data was purely numeric) -- Access STILL
    >>wouldn't bring those fields in without a dummy row at the
    >>top to set data types -- any thoughts (without writing code
    >>to do it?)

    >
    >
    > I always do my text/CSV file imports using code and input #.
    >
    > Sample code is
    >
    > Dim TextLine
    > Open "TESTFILE" For Input As #1 ' Open file.
    > Do While Not EOF(1) ' Loop until end of file.
    > Line Input #1, TextLine ' Read line into variable.
    > Debug.Print TextLine ' Print to the Immediate window.
    > Loop
    > Close #1 ' Close file.
    >
    > But the above assumes that there is only one text data per line.
    >
    > At least I think that's the function I used the last time I coded
    > something like that.
    >
    > Tony
     
  21. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    strive4peace <"strive4peace2006 at yahoo dot com"> wrote:

    >thanks, Tony!
    >
    >Oh! don't you mean MAY vary? Now I know a new acronym -- yeah!


    Details, details.

    >Yesterday, I decided it would be a good idea to learn one
    >new word a day ... this counts! :)


    <smile> Glad I could help.

    >on the TransferText ...just wondered if there was a problem
    >with the macro action in this situation -- there certainly
    >seems to be -- I tried to get it to work for longer than I
    >care to admit! Having to open each text file and insert a
    >dummy row at the top should not be necessary ... Personally,
    >I use code too... but this poster was not a coder...


    No idea. I've never used TransferText. I'd suggest starting a new
    thread with TransferText in the subject.

    The first, and most complex, situation where I had to import lots of
    data had about 15 different types of lines contained within the text
    file. So I had to programmatically read three lines containing about
    30 pieces of data describing the assembly to be welded. Then I would
    read between 1 and 70 lines each describing a weld. Then between 1
    and 70 lines describing each item in the assembly including type of
    steel, diameter, wall thickness, length and so froth. Then between 1
    and 30 lines of this, another bunch of lines describing that and so
    forth.

    A simple assembly only had 30 or 40 text lines I had to import. A
    complex assembly could have 200 or 300.

    The shop would be welding about 20 or 100 of these assemblies per day.
    They employed a couple of hundred welders and others.

    TOny
    --
    Tony Toews, Microsoft Access MVP
    Please respond only in the newsgroups so that others can
    read the entire thread of messages.
    Microsoft Access Links, Hints, Tips & Accounting Systems at
    http://www.granite.ab.ca/accsmstr.htm
     

Share This Page