Results 1 to 3 of 3

understanding Access form FE to SQL Server BE

  1. #1
    erich_18 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    11

    understanding Access form FE to SQL Server BE

    Hi Everyone,

    I made a small Access databse, its a split database, I have the tables back end shared in a network folder and the front end on the desktops. in the local network this option offers very good performance, easy to maintain and very basic. but when sharing it over a WAN its terribly slow (makes sense).

    I migrated the tables to SQLExpress2017, the forms can connect without any problems and the performance is a lot better over the WAN, but still slow. I read that its because all my queries are being calculated on the forms and should instead be calculated in the SQL server. Then I bumped into something called query pass-through, easy enough but the queries in my forms (all of them) have combo boxes as criterias. unfortuntately (so it seems) this does not work in SQL because SQL does not see the Forms, so the criteria part of my query has to be removed.

    then I read about pass-values and stored-procedures. But I really need to know if I'm understanding this correctly so I can continue my research (this is where you all come in):
    basically its telling my combo boxes to 'pass-value' the value selected by the user to the SQL stored-procedure created in the SQL server. the stored-procedure would then find the value needed from the tables and pass the value on to my access form.

    is this how it works?

    if so, how the heck do I tell the combo box to send the value to the store procedure, and tell the stored procedure to send the value found to the text box in the form? I just need an idea and I can figure out the rest.



    tks

    Eric

  2. #2
    Join Date
    Apr 2017
    Posts
    857
    Set up VPN connection from your laptop/home desktop to computer (a desktop computer, or a profile in Terminal Server) in your LAN. Install Access into LAN computer.

    When you want to work with your DB over WAN, connect to LAN computer and start your front-end from there. You will send only keystrokes/mouse-movements to LAN, and you get only image from LAN. All real data exchange is happening in LAN - between LAN computer and SQL Server.

  3. #3
    Richard H is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    USA
    Posts
    1

    use ADODB to execute a stored procedure and use the output parameter for your result

    Hey Eric,

    So, assuming you have a command button with code that runs after you have made your combo box selections. Your command button code would take the combo box values and pass them to a function, like the one below. The function would execute the store procedure passing the input parameters(values from your combo boxes) and then the stored procedure would return the output parameter to your function. The function in turn would return the value the command button process that called it and it would assign that returned value to your text box.

    The function below has a call to Connection_get which you would need to replace with your own connection string. other than that changing the name of the stored procedure and changing the parameters should make it work for you.

    Hope that helps,
    Richard


    Function Patient_MCD_NUM_get(ByVal sPAT_ID As String) As String
    On Error GoTo Errhandler

    Const sProcedure As String = sModuleID & ".Patient_MCD_NUM_get"
    '-----------------------------------------------------------------------
    Dim oConn As ADODB.Connection
    Dim oCmd As ADODB.Command
    Dim oPrm As ADODB.Parameter
    Dim sSP_Name As String
    '-----------------------------------------------------------------------
    ' call SP to get Subscriber Number from PatientID
    '-----------------------------------------------------------------------
    sSP_Name = "PROC_Patient_MCD_NUM_GET"

    Set oConn = Connection_get(g_iConnectionType, 0, 0)
    Set oCmd = New ADODB.Command
    oCmd.ActiveConnection = oConn
    oCmd.CommandText = sSP_Name
    oCmd.CommandType = adCmdStoredProc
    '-----------------------------------------------------------------------
    ' set parameters
    '-----------------------------------------------------------------------
    Set oPrm = oCmd.CreateParameter("@PAT_ID", adVarChar, _
    adParamInput, 18, sPAT_ID)
    oCmd.Parameters.Append oPrm
    Set oPrm = oCmd.CreateParameter("@SUBSCR_NUM", adVarChar, _
    adParamOutput, 50)
    oCmd.Parameters.Append oPrm
    '-----------------------------------------------------------------------
    oCmd.Execute
    '-----------------------------------------------------------------------
    Patient_MCD_NUM_get = IIf(IsNull(oCmd.Parameters("@SUBSCR_NUM")), "", oCmd.Parameters("@SUBSCR_NUM"))
    '-----------------------------------------------------------------------
    Exit Function

    Errhandler:
    Err.Source = sProcedure & "/" & Err.Source
    Err.Raise Err.Number, Err.Source, Err.Description
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 06-14-2016, 12:51 AM
  2. Understanding the Form width property
    By Access_Novice in forum Forms
    Replies: 5
    Last Post: 01-18-2014, 06:03 PM
  3. understanding access queries
    By scamper in forum Queries
    Replies: 2
    Last Post: 01-03-2012, 08:20 AM
  4. understanding excel for access purposes
    By metokushika in forum Programming
    Replies: 1
    Last Post: 10-24-2011, 10:51 PM
  5. Help Understanding Join Syntax In Access
    By zephaneas in forum Queries
    Replies: 5
    Last Post: 06-22-2011, 07:32 PM

Posting Permissions

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