Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2004
    Posts
    112

    Unanswered: Converting a Query to SQL to Code

    I have wrote an access application with lots of forms, querys, reports. Instead of my forms looking at Querys I would like to convert them to SQL and from there put them in code behind my forms. I know how to view the Querys as SQL but how do insert the SQL in to code and from there get my form to look at that code as the source of data??

    Any body help?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SQL is a type of programming langauge. Queries are written in SQL. Please leave all the technical jargon at the wayside and explain what you need to do and why.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    open your query, switch to sql view, copy the SQL code, paste it to your form's record source property. becareful about if your query needs other queries to work correctly though.
    ghozy.

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    You can creat an Event action to set the forms record source. For example you can set the record source query at the "On Open" Event, then you would define the query string and set the "RecordSource" property from code.

    Like this:

    strSQL = "SELECT tblTest.* FROM tblTest;"
    Me.RecordSource = strSQL


    You should note this is doing the exact same thing as having the query defined as the forms Record Source, So there's no advantage to doing it this way unless for some reason you need to dynamically set or change the forms Record Source. For example using VB to change the query to show different data on the form ie: change query for 3rd quarter results or 1st quarter.


    Bill
    ~

    Bill

  5. #5
    Join Date
    Sep 2004
    Posts
    112
    Yes I know that I can copy/paste the statement to the forms record source but then the form is still looking at the original Query. What i am looking to do is get rid of all my querys and use SQL statements only. basically to reduce the size of my application and to try and make it run a bit smoother. I was thinking that I could copy the SQL statements in to VB code to run the forms/reports.

  6. #6
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by ghozy
    open your query, switch to sql view, copy the SQL code, paste it to your form's record source property. becareful about if your query needs other queries to work correctly though.
    Note on this Ghozy: If you configure the query in the query wizard (activated from the 'Record Source' of the forms property sheet) when you close the query wizard you get a prompt to save the query in the form. Selecting "Yes" will put the query string into the Record Source Field as is, with no chance of messing up by the copy and paste method.

    If you save the query first and give it a name first, when you close the query wizard it will save just the name of the Query.

    I generally save the query first and use the query name as the record source. I've read this is more efficient than using the query string in the forms record source. But besides more efficient there is some advantages to saving the query. You can set permissions on queries. You can edit the query indepent of the form if you need to.

    Saltman: If you Save the query string to the form as per Ghozy's suggestion, once the query string is in the forms record source, it will run standalone from the query. Delete the query and it will still work as long as you are using a valid query string. That said there is not any real advantage to deleting the query, or using the query string vs. referring to a query by name as the record source. Access is designed to use queries. It won't make you're app smaller or run smother you would just be adding to the size of the forms. Compacting your database will do more to reduce size than removing queries.
    Last edited by savbill; 09-21-04 at 12:22.
    ~

    Bill

  7. #7
    Join Date
    Sep 2004
    Posts
    112
    Thanks very much for your thoughts.

    Darren

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Here's some SQL code in action:

    Code:
    Function PopulateMyTables() As Integer
        On Error GoTo Err_PMT
        
        Dim HavePermission As Boolean
        Dim TrgRecSet As ADODB.Recordset
        
        HavePermission = False
        ' Check for permission to use this interface ...
        SQLString = "SELECT BillingAdmin FROM UserBillingSettings WHERE (UserID='" & MyUserID & "');"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            If MyRecSet.Fields(0).Value = True Then HavePermission = True
        End If
        MyRecSet.Close
        If Not HavePermission Then
            MsgBox "You are not authorized to access this interface.", vbExclamation, "System Monitor"
            PopulateMyTables = -1
            Exit Function
        End If
       
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
       
        ' Create the Work Order table
        WO_Tbl = "CP_WO_" & MyUserID
        SQLString = "CREATE TABLE " & WO_Tbl & "(WorkOrderNumber TEXT(8), [Project Title] TEXT(34));"
        CurrentProject.Connection.Execute SQLString, , adCmdText
        
        ' Work Order table
        SQLString = "SELECT WorkOrderNumber, [Project Title] FROM [Work Orders II];"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & WO_Tbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, CurrentProject.Connection
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Work Order #
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value & ""
                ' Project Title
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value & ""
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        ' Payment Accounts
        AccountComboBox.RowSource = ""
        SQLString = "SELECT * FROM Accounts WHERE ((AccountID=217) OR (AccountID=219));"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            While MyRecSet.EOF = False
                AccountComboBox.RowSource = AccountComboBox.RowSource & MyRecSet.Fields(0).Value & ";" & MyRecSet.Fields(1).Value & ";"
                MyRecSet.MoveNext
            Wend
            AccountComboBox.RowSource = Left(AccountComboBox.RowSource, Len(AccountComboBox.RowSource) - 1)
        End If
        MyRecSet.Close
        
        Set TrgRecSet = Nothing
        
        LoadingDelay
        
        ' Bind the W.O. ComboBox to the Work Order table
        WO_ComboBox.RowSource = WO_Tbl
        WO_ComboBox.Requery
        
        PopulateMyTables = 1
    Exit_PMT:
        Exit Function
        
    Err_PMT:
        
        MsgBox Err.Number & ": " & Err.Description
        PopulateMyTables = 0
        Resume Exit_PMT
    End Function
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    One thing you have to keep in mind, is if you remove all the queries from the query window, you will have to open each report then from there you'd have to edit the query/record source then reload it.
    Ryan
    My Blog

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    as savbill said, it wouldn't make your program faster, or shorter. it only makes your forms independent.
    ghozy.

  11. #11
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    I think this is an excellent thread. As one who is very slowly moving from macros and GUI queries to VBA I have had exactly the same ideas, i.e. that changing my queries to VBA would somehow be an advantage (which may not always be the case) or at least a good starting point to learning the code (which is). Thanks for the input everyone.
    wazz

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SQL in code does not use access' optimiser... this can be good or bad depending on what you are trying to do.

    before committing to code/saved, compare execution times on your real data... i'm told by Mr Gates (I have not experimented personally) that in some circumstances saved queries can be dramatically faster.

    .MDx size differences between code/saved are trivial.

    izy
    currently using SS 2008R2

Posting Permissions

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