Results 1 to 3 of 3
  1. #1
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    Starting to use SQL Server -> Stuck

    Starting to learn SQL Server, ..... and I'm in the deep end with no floties...
    So here goes:


    I'm trying to get a report query moved to a SP in SQLSE 2014. There are lots of YouTube videos on HOW to write a basic (no pun intended) SP, but nothing on HOW to use them.
    After watching countless YouTube videos and reading so many articles that I have paper cuts on all of my fingers, I have found on HOW to use the SP for a report record source (how do the results get back to the Access report?).
    I have a report, I have a SP - how do I connect them. Right now the SQL tables are linked (via ODBC) so the Access report query executes (in Access) and the results are displayed in the report.
    (Future - how to use a SP for Form record sources)

    I want to be able to have SQLSE 2014 do the filtering, not having to transfer 1.2 million records across the network when only 30 records would be in the report.




    Some background:
    ------------------
    The dB was designed so we could have multiple companies in it. Now the Big Dog (aka He who has the check book) wants the clients to enter their own data. I have to ensure one client can only see their own data and nothing else.

    The idea (currently) is that we would get a cloud space, have a VM for SQL Server, each client would have their own VM with a copy of the FE (accde). Of course, our company's 3 main "brains" would also have our own VMs.


    We started out with an Excel Workbook with ~ 18 sheets. (concept design)
    Had help to create a normalized Access dB.
    Split the dB (Access FE/ Access BE)
    Then moved the tables (approx 34 tables) from the Access BE to SQL Server Express.
    The dB performed well for over 5 years. Largest table was 1.5 records.


    I converted (mostly) the Access report query to T-SQL. I have 2 parameters to pass right now: CompanyID_PK (LongInt) and EffectiveDate (DateTime). There will be more parameters later. Right now I am comparing the Access query results with the SQL query results.



    If I have 3 databases in SQLSE 2014 - say "MalofyDev", "BongvoProd" and "ToztiTesting":

    1) Are stored procedures specific to a dB?
    2) Is it necessary to have "Use Bongvo" at the top of the SP to ensure the data is from the dB Bongvo?

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What's the reports query / what query in access speak are you trying to archive in the SP?

    SP's are specific to the database they are created in.
    You can get complicated with "joining" databases together but that certainly isn't beginner stuff.

    An SP can take a number of parameters and either perform an action or return a result set.
    The normal route is to use a pass though query to run the SP return a result to access.

    I use a generic function for this - first create a pass through query - I've called it qPT_Generic.

    This sub sets up the query and executes it.
    Code:
    Public Sub sSendToPT_Generic(strQuery As String, bReturnRecs As Boolean)
        Dim db As Database
        Dim qDEF As QueryDef
        
        Set db = CurrentDb()
        
        Set qDEF = db.QueryDefs("qPT_Generic")
        qDEF.Connect = db.TableDefs("tb_A_TableThatIsConnectedToYourBE_Database").Connect
        qDEF.SQL = strQuery
        qDEF.ReturnsRecords = bReturnRecs
        If Not bReturnRecs Then
            db.Execute "qPT_Generic", dbFailOnError
        End If
        
        Set qDEF = Nothing
        Set db = Nothing
    
    
    End Sub
    Now to use it you simply pass the exec statement to it;

    So to delete a tables contents quickly;
    Code:
       strSQL = "TRUNCATE TABLE tt_MyTemporaryTable"
       sSendToPT_Generic strSQL, False
    To turn your qPT_Generic into the recordsource for something;
    This runs an SP called sp_Report_DailyComparison and uses a date parameter
    Code:
            strSQL = "EXEC sp_Report_DailyComparison @dStartDate = '" & Format(dtRunDate, "yyyy-mm-dd") & "'"
            Call sSendToPT_Generic(strSQL, True)
            Set rstData = db.OpenRecordset("qPT_Generic", dbOpenSnapshot)
    Note the way the parameter is passed in - it's named - this is an optional thing.
    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
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    another way you can work: use the stored procedure as a dataset for a SQL Server report directly published from SQL server to a website. There the users can fill in the parameters and view the report or download it to excel, pdf, ... .

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

Similar Threads

  1. Replies: 3
    Last Post: 09-30-2021, 11:53 AM
  2. Just starting out - need advice
    By NauticalGent65 in forum SQL Server
    Replies: 13
    Last Post: 03-11-2018, 10:25 AM
  3. Starting Access using VBA
    By DHIGHTO in forum Modules
    Replies: 3
    Last Post: 02-19-2015, 09:48 AM
  4. just starting out - need help
    By abomb987 in forum Access
    Replies: 2
    Last Post: 05-16-2014, 06:54 AM
  5. Starting a new database
    By JFo in forum Access
    Replies: 9
    Last Post: 08-25-2011, 11:00 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
  •  
Other Forums: Microsoft Office Forums