Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43

    Convertion of queries to SQL statements


    An easy way to convert all queries in an access database to SQL statements so as to use the same inside forms and reports as data source .

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can just use the saved query as the record source for any form or report?

    There is no magic way to convert all of them - you can just cut and paste the SQL view from a query into the row source property.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You can use my SQL to VBA converter to assist with the process
    https://www.access-programmers.co.uk...d.php?t=293372

    Use the latest version in post #5
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43
    Thank you for your reply.
    1.Please let me know whether an sql statement of an append query can be used in a macro ? If so give an example.
    2.The vba to be used instead of an append query.

    I want to remove queries from my database objects as far as possible so as to secure my application. Thank you in advance.

  5. #5
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you compile you database and save it as accde the end users can't get to anything much anyway.
    In VBA you simply use something like

    Code:
    Me.RecordSource = "SELECT Field1 from Table2"
    Or to execute an update qry
    Code:
    Dim sSql as string
    
    sSql = "UPDATE  YourTable SET StringField1 = 'Somestring', NumberField2 = 99 "
    
    currentdb.execute sSql, dbSeechanges
    

    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43
    Thank you very much.

  7. #7
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43
    sql2vba converter is extremely helpful. The converted vba works fine.

    Let me know the vba for the following also.

    Hourglass Yes/No
    Set warning No/Yes
    Echo No/Yes
    Requery
    MsgBox

    If you pl inform the vba for the above macro actions also my macro can be converted fully to vba.
    Your help should be so much appreciable. THANK YOU.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by sanal View Post
    sql2vba converter is extremely helpful. The converted vba works fine.

    Let me know the vba for the following also.

    Hourglass Yes/No
    Set warning No/Yes
    Echo No/Yes
    Requery
    MsgBox

    If you pl inform the vba for the above macro actions also my macro can be converted fully to vba.
    Your help should be so much appreciable. THANK YOU.
    I don't use macros - the following are for VBA

    DoCmd.Hourglass True / DoCmd.Hourglass False

    DoCmd.SetWarnings True / DoCmd.SetWarnings False

    Application.Echo True / Application.Echo False

    Me.Requery for whole form / Me.ControlName.Requery for individual control

    MsgBox "Some message",vbInformation+vbOKCancel,"Message Title" and various other possible types of message

    BTW - the Access Help file has all this info and much more - just click F1 for help
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43
    My vba on click event of a button(Add) is as follows. You may please examine and let me know the error in it. After the event execute the application is hanged.(ie. the command buttons are become inactive ) I suppose some declaration is needed at the beginning but I don't know. Please Examine and correct the code. Thank You.

    Private Sub Command27_Click()
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    Application.Echo True
    DoCmd.RunSQL "INSERT INTO [tblAppendCL-CN] ( ID, [CL-Case No] ) " & _
    " SELECT 2 AS ID, Right([F1],Len([F1])-4) AS [CL-CaseNo] " & _
    " FROM [tblCL-CaseNo] " & _
    " WHERE ((([tblCL-CaseNo].[F1]) Like ""WPC*""));"
    DoCmd.RunSQL "INSERT INTO [tblAppendCL-CCC] ( ID, [CL-Case No] ) " & _
    " SELECT 2 AS ID, Right([F1],Len([F1])-10) AS [CL-CaseNo] " & _
    " FROM [tblCL-CaseNo] " & _
    " WHERE ((([tblCL-CaseNo].[F1]) Like ""Con.CaseC*""));"
    DoCmd.RunSQL "INSERT INTO [tblAppendCL-OAEKM] ( ID, [CL-Case No] ) " & _
    " SELECT 2 AS ID, Right([F1],Len([F1])-8) AS [CL-CaseNo] " & _
    " FROM [tblCL-CaseNo] " & _
    " WHERE ((([tblCL-CaseNo].[F1]) Like ""OA*EKM*""));"
    DoCmd.RunSQL "INSERT INTO [tblAppendCL-OP] ( ID, [CL-Case No] ) " & _
    " SELECT 2 AS ID, Right([F1],Len([F1])-6) AS [CL-CaseNo] " & _
    " FROM [tblCL-CaseNo] " & _
    " WHERE ((([tblCL-CaseNo].[F1]) Like ""OPKAT*""));"
    DoCmd.RunSQL "INSERT INTO [tblAppendCL-WA] ( ID, [CL-Case No] ) " & _
    " SELECT 2 AS ID, Right([F1],Len([F1])-3) AS [CL-CaseNo] " & _
    " FROM [tblCL-CaseNo] " & _
    " WHERE ((([tblCL-CaseNo].[F1]) Like ""WA*""));"
    DoCmd.RunSQL "UPDATE [tblCL-CaseNo] SET [tblCL-CaseNo].F1 = ""0"";"
    Me.Requery
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
    Application.Echo False
    MsgBox "Causelist added", vbDefaultButton2, "CaseInformation System"
    End Sub

  10. #10
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You are confused with your quotes around the strings in your criteria.
    Personally I would create a string variable (sSql) and use CurrentDb.Execute sSql to avoid the need to set warnings on and off.

    Try this;
    Code:
    Private Sub Command27_Click()
        DoCmd.Hourglass True
        DoCmd.SetWarnings False
        Application.Echo True
        DoCmd.RunSQL "INSERT INTO [tblAppendCL-CN] ( ID, [CL-Case No] ) " & _
                     " SELECT 2 AS ID, Right([F1],Len([F1])-4) AS [CL-CaseNo] " & _
                     " FROM [tblCL-CaseNo] " & _
                     " WHERE ((([tblCL-CaseNo].[F1]) Like 'WPC*'));"
        DoCmd.RunSQL "INSERT INTO [tblAppendCL-CCC] ( ID, [CL-Case No] ) " & _
                     " SELECT 2 AS ID, Right([F1],Len([F1])-10) AS [CL-CaseNo] " & _
                     " FROM [tblCL-CaseNo] " & _
                     " WHERE ((([tblCL-CaseNo].[F1]) Like 'Con.CaseC*'));"
        DoCmd.RunSQL "INSERT INTO [tblAppendCL-OAEKM] ( ID, [CL-Case No] ) " & _
                     " SELECT 2 AS ID, Right([F1],Len([F1])-8) AS [CL-CaseNo] " & _
                     " FROM [tblCL-CaseNo] " & _
                     " WHERE ((([tblCL-CaseNo].[F1]) Like 'OA*EKM*'));"
        DoCmd.RunSQL "INSERT INTO [tblAppendCL-OP] ( ID, [CL-Case No] ) " & _
                     " SELECT 2 AS ID, Right([F1],Len([F1])-6) AS [CL-CaseNo] " & _
                     " FROM [tblCL-CaseNo] " & _
                     " WHERE ((([tblCL-CaseNo].[F1]) Like 'OPKAT*'));"
        DoCmd.RunSQL "INSERT INTO [tblAppendCL-WA] ( ID, [CL-Case No] ) " & _
                     " SELECT 2 AS ID, Right([F1],Len([F1])-3) AS [CL-CaseNo] " & _
                     " FROM [tblCL-CaseNo] " & _
                     " WHERE ((([tblCL-CaseNo].[F1]) Like 'WA*'));"
        DoCmd.RunSQL "UPDATE [tblCL-CaseNo] SET [tblCL-CaseNo].F1 = '0';"
        Me.Requery
        DoCmd.Hourglass False
        DoCmd.SetWarnings True
        Application.Echo False
        MsgBox "Causelist added", vbDefaultButton2, "CaseInformation System"
    End Sub
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree with Minty's response including the use of CurrentDB.Execute instead of DoCmd.RunSQL

    Two other things
    1. Recommend you add error handling to your code
    2. The message box code at the end is wrong. You have not specified a button so you will just get an OK button by default.
    Remove the vbDefaultButton2 and just have two commas with nothing between them.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Personally, I never use "SetWarnings " nor "DoCmd.RunSQL" commands.
    I use the "CurrentdB.Execute" command.

    My understanding:
    Using the "RunSQL" command causes Access to parse (not the best word to use) the SQL before it is sent to the dB engine.
    The execute method bypasses Access and sends the SQL directly to the dB engine (whichever one you are using)

  13. #13
    sanal is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Feb 2018
    Posts
    43
    My code for CurrentDb.Execute sSql creates errors. If you please edit my above code as you instructed and intimated me will be a great help.

  14. #14
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If your queries code are correct then simply change it to
    Code:
     Dim sSql             As String
    
        DoCmd.Hourglass True
    
    
        sSql = "INSERT INTO [tblAppendCL-CN] ( ID, [CL-Case No] ) " & _
               " SELECT 2 AS ID, Right([F1],Len([F1])-4) AS [CL-CaseNo] " & _
               " FROM [tblCL-CaseNo] " & _
               " WHERE ((([tblCL-CaseNo].[F1]) Like 'WPC*'));"
        Debug.Print sSql
        CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError
    
    
        sSql = "INSERT INTO [tblAppendCL-CCC] ( ID, [CL-Case No] ) " & _
               " SELECT 2 AS ID, Right([F1],Len([F1])-10) AS [CL-CaseNo] " & _
               " FROM [tblCL-CaseNo] " & _
               " WHERE ((([tblCL-CaseNo].[F1]) Like 'Con.CaseC*'));"
        Debug.Print sSql
        CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError
    
    
        sSql = "INSERT INTO [tblAppendCL-OAEKM] ( ID, [CL-Case No] ) " & _
               " SELECT 2 AS ID, Right([F1],Len([F1])-8) AS [CL-CaseNo] " & _
               " FROM [tblCL-CaseNo] " & _
               " WHERE ((([tblCL-CaseNo].[F1]) Like 'OA*EKM*'));"
        Debug.Print sSql
        CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError
    
    
        sSql = "INSERT INTO [tblAppendCL-OP] ( ID, [CL-Case No] ) " & _
               " SELECT 2 AS ID, Right([F1],Len([F1])-6) AS [CL-CaseNo] " & _
               " FROM [tblCL-CaseNo] " & _
               " WHERE ((([tblCL-CaseNo].[F1]) Like 'OPKAT*'));"
        Debug.Print sSql
        CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError
    
    
        sSql = "INSERT INTO [tblAppendCL-WA] ( ID, [CL-Case No] ) " & _
               " SELECT 2 AS ID, Right([F1],Len([F1])-3) AS [CL-CaseNo] " & _
               " FROM [tblCL-CaseNo] " & _
               " WHERE ((([tblCL-CaseNo].[F1]) Like 'WA*'));"
        Debug.Print sSql
        CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError
    
    
        sSql = "UPDATE [tblCL-CaseNo] SET [tblCL-CaseNo].F1 = '0';"
        Debug.Print sSql
        CurrentDb.Execute sSql, dbSeeChanges + dbFailOnError
    
    
        Me.Requery
        DoCmd.Hourglass False
    The Debug.print will display in the immediate window the actual query being run.
    Any that fail should be obvious.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you compile you database and save it as accde the end users can't get to anything much anyway.
    Queries, forms, tables (and I think reports and macros) can all be opened in an mde or accde. Queries, macros and tables can even be opened in design view. In the case of queries, you can even see the sql statement.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 12-31-2015, 12:05 PM
  2. Queries using IIF statements
    By jaarons in forum Queries
    Replies: 2
    Last Post: 12-30-2012, 03:21 AM
  3. Replies: 11
    Last Post: 10-24-2012, 03:17 PM
  4. IIF Statements and Update Queries.
    By nicknicknick in forum Queries
    Replies: 1
    Last Post: 06-05-2011, 12:08 PM
  5. IIF statements?
    By staceyo in forum Queries
    Replies: 15
    Last Post: 09-28-2010, 08:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums