none
Access 2013 Rowsource Update Function to SQL Server 2008 Function

    Question

  • HI All,

    I was wondering if as an example you could help me write a SQL Function or Stored Proc to achieve the result of this access function...I Have already worked out how to create delete Stored Procs and Update Stored Procs but am unsure how to set up a function to update the rowsource "tblRelRowSrce" with related parties below. In Other words this is an access 2013 function that I would like to trigger run when a user accesses a specific form but run on sql server.


    Function RelatedRowSrce() As String
    On Error GoTo ErrorCom: If CDB Is Nothing Then InitGlobals
    Dim TPrtIDStr As String, TRelIDStr As String, RelDetStr As String, TStr As String
    Dim rsRRR As DAO.Recordset, rsChkRRR As DAO.Recordset, rsRel As DAO.Recordset, rsRelR As DAO.Recordset, rsCon As DAO.Recordset
    Dim tmpStr6 As String, tmpStr5 As String, tmpStr4 As String, TOrdNo As Long, TRelID As Long, TFileAs As String

        InsSQLQuery "DelUsrTblRelRowSrceRecs", sFrmFC!UsrPrtID
        
        
        TOrdNo = 0: TVal = 0
        TFileAs = GetTVal("FileAs", "tblParty", "ID=" & sFrmFC!PrtID)
        If TFileAs <> "" Then InsSQLQuery "SP_UI_tblRelRowSrce", "Null, " & sFrmFC!UsrPrtID & ", " & sFrmFC!PrtID & ", '" & TFileAs & "', " & TOrdNo & ";": TOrdNo = TOrdNo + 1
       
        'Set rsRRR = CDB.OpenRecordset("SELECT * FROM tblRelRowSrce;", 2, 512, 3)
        'rsRRR.AddNew: rsRRR!PrtID = sFrmFC!PrtID: rsRRR!FileAs = GetTVal("FileAs", "tblParty", "ID=" & rsRRR!PrtID): rsRRR!OrdNo = TOrdNo:: rsRRR.UpDate
       
        If InStr(1, Nz(sFrmFC!Var8, ""), "CallFromMedicalFund") > 0 Then
            Stop
            TVal = GetTVal("I1PrtID", "tblHoldings", "ID=" & Nz(sFrm1!HldgID, 0))
            If TVal <> 0 Then
                Set rsChkRRR = CDB.OpenRecordset("SELECT * FROM tblRelRowSrce WHERE PrtID=" & TVal & ";", 2, 512, 3): RecInit rsChkRRR
                If TRecCnt = 0 Then rsRRR.AddNew: rsRRR!PrtID = TVal: rsRRR!FileAs = GetTVal("FileAs", "tblParty", "ID=" & rsRRR!PrtID): rsRRR!OrdNo = TOrdNo: TOrdNo = TOrdNo + 1: rsRRR.UpDate
                Set rsChkRRR = Nothing
                Set rsRel = CDB.OpenRecordset("SELECT PrtID, relOrder, relPrtID FROM tblRelationships WHERE (PrtID=" & TVal & ") ORDER BY relOrder, relBirthDate;", 2, 512, 3): RecInit rsRel
            Else
                Set rsRel = CDB.OpenRecordset("SELECT PrtID, relOrder, relPrtID FROM tblRelationships WHERE (PrtID=" & sFrmFC!PrtID & ") ORDER BY relOrder, relBirthDate;", 2, 512, 3): RecInit rsRel
            End If
        Else
            Set rsRel = CDB.OpenRecordset("SELECT PrtID, relOrder, relPrtID FROM tblRelationships WHERE (PrtID=" & sFrmFC!PrtID & ") ORDER BY relOrder, relBirthDate;", 2, 512, 3): RecInit rsRel
        End If
       
       
        Do Until rsRel.EOF
            If Nz(rsRel!RelPrtID, 0) <> 0 Then
                'Set rsChkRRR = CDB.OpenRecordset("SELECT * FROM tblRelRowSrce WHERE PrtID=" & rsRel!RelPrtID & ";", 2, 512, 3): RecInit rsChkRRR
                TRelID = GetTVal("ID", "tblRelRowSrce", "PrtID=" & rsRel!RelPrtID)
                If IsEmpty(TRelID) Or TRelID = 0 Then
                    TFileAs = GetTVal("FileAs", "tblParty", "ID=" & rsRel!RelPrtID)
                    If TFileAs <> "" Then InsSQLQuery "SP_UI_tblRelRowSrce", "Null, " & sFrmFC!UsrPrtID & ", " & rsRel!RelPrtID & ", '" & TFileAs & "', " & TOrdNo & ";": TOrdNo = TOrdNo + 1
                    'rsRRR.AddNew: rsRRR!PrtID = rsRel!RelPrtID: rsRRR!FileAs = GetTVal("FileAs", "tblParty", "ID=" & rsRRR!PrtID): rsRRR!OrdNo = TOrdNo: TOrdNo = TOrdNo + 1: rsRRR.UpDate
                End If
                'Set rsChkRRR = Nothing
                'TPrtIDStr = TPrtIDStr & IIf(InStr(1, TPrtIDStr, rsRel!RelPrtID & ";") = 0, rsRel!RelPrtID & ";", "")
                If InStr(1, Nz(sFrmFC!Var8, ""), "CallFromMedicalFund") = 0 And TVal = 0 And sFrmFC!SelPrtBTp < 2 Then
                    Set rsRelR = CDB.OpenRecordset("SELECT PrtID, relOrder, * FROM tblRelationships WHERE (PrtID=" & rsRel!RelPrtID & " And relPrtID<>" & sFrmFC!SelPrtID & ") ORDER BY relOrder, relBirthDate;", 2, 512, 3): RecInit rsRelR
                    Do Until rsRelR.EOF
                        If Nz(rsRelR!RelPrtID, 0) <> 0 Then
                            TRelID = GetTVal("ID", "tblRelRowSrce", "PrtID=" & rsRelR!RelPrtID)
                            'Set rsChkRRR = CDB.OpenRecordset("SELECT * FROM tblRelRowSrce WHERE PrtID=" & rsRel!RelPrtID & ";", 2, 512, 3): RecInit rsChkRRR
                            If IsEmpty(TRelID) Or TRelID = 0 Then
                                TFileAs = GetTVal("FileAs", "tblParty", "ID=" & rsRelR!RelPrtID)
                                If TFileAs <> "" Then InsSQLQuery "SP_UI_tblRelRowSrce", "Null, " & sFrmFC!UsrPrtID & ", " & rsRelR!RelPrtID & ", '" & TFileAs & "', " & (10000 + TOrdNo) & ";": TOrdNo = TOrdNo + 1
                                'rsRRR.AddNew: rsRRR!PrtID = rsRel!RelPrtID: rsRRR!FileAs = GetTVal("FileAs", "tblParty", "ID=" & rsRRR!PrtID): rsRRR!OrdNo = 10000 + TOrdNo: TOrdNo = TOrdNo + 1: rsRRR.UpDate
                            End If
                            Set rsChkRRR = Nothing
                        End If
                        rsRelR.MoveNext
                    Loop
                End If
            End If
            rsRel.MoveNext
        Loop
      
        'rsRRR.AddNew: rsRRR!PrtID = sFrmFC!UOPrtID: rsRRR!FileAs = GetTVal("FileAs", "tblParty", "ID=" & rsRRR!PrtID): rsRRR!OrdNo = TOrdNo: TOrdNo = TOrdNo + 1: rsRRR.UpDate
        TRelID = GetTVal("ID", "tblRelRowSrce", "PrtID=" & sFrmFC!UOPrtID)
        If IsEmpty(TRelID) Or TRelID = 0 Then
            TFileAs = GetTVal("FileAs", "tblParty", "ID=" & sFrmFC!UOPrtID)
            If TFileAs <> "" Then InsSQLQuery "SP_UI_tblRelRowSrce", "Null, " & sFrmFC!UsrPrtID & ", " & sFrmFC!UOPrtID & ", '" & TFileAs & "', " & TOrdNo & ";": TOrdNo = TOrdNo + 1
        End If

        'RelDetStr = "FileAs"
        'TStr = "(INSTR(1,';" & TPrtIDStr & "',ID)>0)"
        'TStr = "(ID IN (SELECT PrtID FROM tblRelRowSrce ORDER BY OrdNo;))"
       
        Dim TRType As Long
        RelatedRowSrce = "SELECT FileAs As RelatedDetail, PrtID FROM tblRelRowSrce ORDER BY OrdNo;"

    ErrorCom:   If ErrCon(Err.Description, Err.Number, "Setup Functions : RelatedRowSrce") Then Resume
                Set rsRel = Nothing: Set rsRelR = Nothing: Set rsChkRRR = Nothing: Set rsRRR = Nothing
    End Function


    Rhett Cawood Insuria CRM Financial Planning Access Developer

    Wednesday, January 22, 2014 2:19 PM

Answers

  • Hi, I am back

    let's start creating function on SQL Server :-) fro the start to the final quey that you need :-)... Get Ready.. GO:

    There are basically 3 main functions types in SQL Server: (1)Scalar Functions, (2)Table-Valued Functions, and (3)System Functions.

    There are sub type of those functions. for example "Scalar Functions" can be (1.1) CLR function or (1.2) TSQL Function, "Table-Valued Functions" can be (2.1) CLR function, (2.2) inline Table-Valued Function or (2.3) multi-statement Table-Valued function.

    1. First step is to understand the different and to chose the type of function that you need. please go over this link and tell us which function is fit to your need (if can't chose please tell us and we will do it together):

    http://technet.microsoft.com/en-us/library/ms191007.aspx

    After you understood the types basic, Go over this simple examples and make sure you can write your own simple function using the type that you chose. Comeback with the basic knowledge an we will go over your code and move to the next step of writing your function.

    http://technet.microsoft.com/en-us/library/ms191320.aspx

    * At that time I will go over your VBA code, and will prepare something for the next step according your needs


    [Personal Site] [Blog] [Facebook]signature

    Thursday, January 23, 2014 7:59 AM
    Moderator

All replies

  • Hi Rhett ,

    This is an SQL Server forum. Your question is more fit to ACCESS forum or VBA language forum. I will recommend One of the Moderators to move this thread to one of those forums, Or just that you try to ask there, and in the meantime wish you good luck there :-) 


    [Personal Site] [Blog] [Facebook]signature

    Wednesday, January 22, 2014 7:02 PM
    Moderator
  • I am asking how to make a sql 2008 function that will do the same as the attached - why would you move my question to access - I am sure the access guys

    Rhett Cawood Insuria CRM Financial Planning Access Developer

    Thursday, January 23, 2014 5:42 AM
  • I am asking how to make a sql 2008 function that will do the same as the attached - why would you move my question to access - I am sure the access guys

    Rhett Cawood Insuria CRM Financial Planning Access Developer

    True. Can you describe the goals of the function more? Instead of converting it, maybe what do you want to accomplish?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!


    Thursday, January 23, 2014 6:16 AM
    Owner
  • OK Thank - let me see if I can simplify what I am trying to do. I have a few tables that make up what I am trying to do - tblParty the main table that has fields for our example - ID INT, UGID INT , FileAs NVARCHAR (255) - (Other fields but will leave those out for now) - Then a Party Relationships Table for Relationships between parties where the relationship Type is kept ie spouse child business etc - called tblRels with fields - ID INT, PrtID INT (Enforced Referential Integrity to tblParty ID), relPrtID (Enforced Referential Integrity to tblParty ID), RelTp INT (References a Constant Relationships Table that has Fields ID and Descr for the population of the dropdown List to Populate RelTp) - (Have left other fields out).

    OK that's the structure - IN Access what I do Is loop through the Currently Selected Party's Relationships and then the Relations Relationships and create a Table that gives me  recordsource for selected possible Relationships up to tier 2 of the selected party's relationships but including the Current User's Party ID and other constant party ID (ie Estate relationship).

    I need to Create a function for this rowsource in SQL Server 2008 as it takes 2 long for access to process this to a SQL Server in a cloud hosted scenario.


    Rhett Cawood MS Access Developer

    Thursday, January 23, 2014 6:44 AM
  • sorry My BAd, I have not read the entire question probably have no excuses :-)
    i was thinking you need the ACCESS solution, I will check it in 45 min and post something (let me get to the office an drink a Coffee first)

    [Personal Site] [Blog] [Facebook]signature

    Thursday, January 23, 2014 7:25 AM
    Moderator
  • Hi, I am back

    let's start creating function on SQL Server :-) fro the start to the final quey that you need :-)... Get Ready.. GO:

    There are basically 3 main functions types in SQL Server: (1)Scalar Functions, (2)Table-Valued Functions, and (3)System Functions.

    There are sub type of those functions. for example "Scalar Functions" can be (1.1) CLR function or (1.2) TSQL Function, "Table-Valued Functions" can be (2.1) CLR function, (2.2) inline Table-Valued Function or (2.3) multi-statement Table-Valued function.

    1. First step is to understand the different and to chose the type of function that you need. please go over this link and tell us which function is fit to your need (if can't chose please tell us and we will do it together):

    http://technet.microsoft.com/en-us/library/ms191007.aspx

    After you understood the types basic, Go over this simple examples and make sure you can write your own simple function using the type that you chose. Comeback with the basic knowledge an we will go over your code and move to the next step of writing your function.

    http://technet.microsoft.com/en-us/library/ms191320.aspx

    * At that time I will go over your VBA code, and will prepare something for the next step according your needs


    [Personal Site] [Blog] [Facebook]signature

    Thursday, January 23, 2014 7:59 AM
    Moderator
  • Hi (again)  IB Cawood, 

    I will write some comments which suppose to help you. Pleas read all and try to implement what you need, and if you are unable to, then try explain your need and we will start without using you VBA Code.

    Translating a logic from one technology to another is a very bad idea most of the time, as Each technology works completely differently from the base. For example in your code you are using "Do Until" loop for each record. This can be implement in SQL Server using a "While" loop and/or using cursor looping, but most of the time this will be very bad idea, as SQL Server work with records Set and can implement most loop operation without any explicit loop at all.

    * The next part of the explanation that I am writing, I'm hiding in white color :-)
    This is not an exam but for your own good!
    therefor i give you the answer here but using it before you learn will give you no good!
    Please do not take a look before you learn the links I posted in the above post 
    and when you comes to the conclusion what type of function you need!

    ============ some helpful comment [hidden in white color] ===========

    * In order to select the correct type of function, we need to understand what will the function bring back in the end. As i can see your function suppose to bring back a String, This is like a simple Scalar Function on SQL server.

    * I can see in your code the use of a function named GetTVal. I cant find in the code the configuration of GetTVal function. but i do see that (1) it get several parameters in like "ID", "tblRelRowSrce", "PrtID=" & sFrmFC!UOPrtID, and (2) it probably need to bring a single value back, therefor this is simple Scalar Function and if you want then you can implement it.

    * SQL SErver is using T-SQL language. I will try mow to go over your function and give you comments on how to simulate each "command"/"build in action", in your original function using T-SQL.

    On Error GoTo
    SQL Server does not have anything like Visual Basic's "On Error GoTo" construct, but it is has something like "On Error Resume Next" statement + GoTo statement. Together it give you the ability to implement the same action as in VBA code.

    There is GoTo in T-sql just like in VBA, but i recommend not to use it almost ever! You can implement GoTO like this example:

    GOTO Branch_Two --Jumps to the second branch & akip Branch_One.
    Branch_One:
        SELECT 'Select Branch One.'
    Branch_Two:
        SELECT 'Select Branch Two.'
    Branch_Three:
        SELECT 'Select Branch Three.'

    handling Erors can be done using the Variable @@ERROR, And/Or using try/catch block, as shown in those links:
    http://stackoverflow.com/questions/11141814/bad-practice-to-use-sql-servers-goto-for-error-handling
    http://www.novicksoftware.com/tipsandtricks/tips-erorr-handling-in-a-stored-procedure.htm

    Do Until rsRel.EOF

    A "do until" loop can be implement using a WHILE loop in SQL Server, as shown here:
    http://technet.microsoft.com/en-us/library/ms178642.aspx

    rsRel.MoveNext

    as I mentioned there is an implementation of looping record by record in SQL Server (and again it is not recommended most time) using a cursor looping as shown in this link:
    http://www.dotnet-tricks.com/Tutorial/sqlserver/4L7I050512-SQL-Server-Basics-of-Cursors.html

    the implementing of rsRel.MoveNext in this case will be the "FETCH NEXT" command.

    I hope this give you a starting point :-). Please make sure you do not try to copy the logic from the VBA code, but implement your needs using SQL Server logic using records set. If you need more help please explaing what you need to get without using your old code.


    [Personal Site] [Blog] [Facebook]signature

    Thursday, January 23, 2014 9:27 AM
    Moderator