Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: proper way to write SQL code for docmd.runsql

    (INSERT INTO Sdt_directory ( dir_SID, dir_Name )SELECT [List - Students].SID, [List - Students].Name FROM [List - Students] LEFT JOIN Sdt_directory ON [List - Students].SID = Sdt_directory.dir_SID WHERE ((([sdt_directory.dir_SID]) Is Null))
    how do I write that for: docmd.RunSQL? Thanks! (and yes, fieldname "name" is a bad choice, I'm fixing that now...) =D

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Code:
    Dim strSQL as String
    strSQL = "(INSERT INTO Sdt_directory ( dir_SID, dir_Name ) SELECT[List - Students].SID,[List - Students].Name FROM[List - Students] LEFT JOIN Sdt_directory ON[List - Students].SID = Sdt_directory.dir_SID WHERE ((([sdt_directory.dir_SID]) Is Null)));"
    DoCmd.RunSQL strSQL
    Guessing though... I tend to use CurrentDb.Execute myself.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jun 2008
    Posts
    163
    thanks ST! what's currentdb.execute? I haven't seen that in the stuff I've read. Do you recommend it over docmd? =D

    I asked this question because i originally had in a "on form load" event:

    Dim stDocName As String
    stDocName = "upd_Sdtdir"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    Each time the form is opened, a couple of dialogue boxes would appear to confirm that a query will be run. Not a big deal, but I figured that running the SQL from the form itself would remove the dialogue boxes (again, thanks for the solution!). Well, it worked for one of the dialogue boxes. There's another box that simply confirms the number of records updated (even when 0 records are updated). LOL
    Last edited by coffeecat; 08-13-08 at 23:43.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by coffeecat
    thanks ST!
    You're welcome

    what's currentdb.execute? I haven't seen that in the stuff I've read. Do you recommend it over docmd? =D
    Mnn.... well Execute is a method of executing action queries... but I don't know the differences between it and DoCmd.RunSQL.

    Each time the form is opened, a couple of dialogue boxes would appear to confirm that a query will be run. Not a big deal, but I figured that running the SQL from the form itself would remove the dialogue boxes (again, thanks for the solution!). Well, it worked for one of the dialogue boxes. There's another box that simply confirms the number of records updated (even when 0 records are updated). LOL
    You can DoCmd.SetWarnings False to avoid these confirmation messages.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by coffeecat
    (and yes, fieldname "name" is a bad choice, I'm fixing that now...) =D
    fieldname "name" is fine

    table name [List - Students] is terrible

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    You can DoCmd.SetWarnings False to avoid these confirmation messages.
    Noes!!!!!1 Dangeranger! If you ever use DoCmd.SetWarnings False then always reset to True straight after, and have an error trap and reset to true there too.

    currentdb.Execute is DAO. Specify the dbFailOnError argument when using it too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2008
    Posts
    163
    table name [List - Students] is terrible
    Yeah, it is. *shame*

    Quote:
    Originally Posted by StarTrekker
    You can DoCmd.SetWarnings False to avoid these confirmation messages.
    Noes!!!!!1 Danger! Danger! If you ever use DoCmd.SetWarnings False then always reset to True straight after, and have an error trap and reset to true there too.

    currentdb.Execute is DAO. Specify the dbFailOnError argument when using it too.
    Uh.. wait, you guys totally lost me. The dialogue that appears is the (will append ## records etc Y/N?). I guess I need something that automatically chooses 'yes' (sorry I didn't clarify that earlier, my bad). What's the best thing for me to do? =D

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by r937
    fieldname "name" is fine
    In Access it's a reserved word, so it's not advisable. Sure it works, so long as you remember square brackets in the right places.

    Quote Originally Posted by coffeecat
    The dialogue that appears is the (will append ## records etc Y/N?). I guess I need something that automatically chooses 'yes' (sorry I didn't clarify that earlier, my bad). What's the best thing for me to do? =D
    DoCmd.SetWarnings False
    CurrentDB.Execute strSQL
    DoCmd.SetWarnings True


    Besides the error trapping stuff, which is another topic really.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    name is a reserved word in access?

    thanks, didn't realize that

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2008
    Posts
    163
    I totally fail at naming.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The reason you get the confirmation messages is because of the settings in Options -> Edit\ Find. You can change these programmatically if you really don't want to use CurrentDB:
    Code:
    Application.SetOption "Confirm Action Queries", False
    DoCmd.RunSQL strSQL
    Application.SetOption "Confirm Action Queries", True
    SetWarnings can cause some dramatic problems (we've had a few people on here that lost a lot of work because of it - and I've lost a few days over the years).

    From help re dbFailOnError:
    In a Microsoft Jet workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^ Thanks for the extra details on that. I totally agree that dbFailOnError should be used every single time with Execute... I'll make sure mine all read:

    CurrentDb.Execute strSQL, dbFailOnError

    From this point on

    Thanks!

    Quote Originally Posted by r937
    name is a reserved word in access?

    thanks, didn't realize that

    You're welcome It comes about from the Name property for almost everything.

    Quote Originally Posted by coffeecat
    I totally fail at naming.
    Agreed ^.^

    You'll soon get the hang of it. Especially after you run into a confusing problem that is caused by a bad name.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StarTrekker
    Quote Originally Posted by r937
    name is a reserved word in access?

    thanks, didn't realize that
    You're welcome It comes about from the Name property for almost everything.
    Access appears to be the only database where "name" is a reserved word, hence my surprise

    see http://www.petefreitag.com/tools/sql...ker/?word=name

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, I can believe that.

    That site needs Jet added
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jet is dead; long live jet!! ©

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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