Results 1 to 3 of 3

Moving some processing from Access 2010 to SQL Server 2008 R2

  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,015

    Moving some processing from Access 2010 to SQL Server 2008 R2

    I have an Access Database that generates some reports from multiple SQL Servers including some Commercial products such as EZ-Cap. In order for the reports to work I have to run a process in Access that takes a full day to process this was scheduled to run Sunday morning @ 6 AM. Our Corporate Database team has made some changes and this process is no longer viable. Our local upper management relies on these reports and I have to get them back up ASAP. I'm trying to move most of the processing from my local Access DB to our Local SQL Server Database but not clear on a couple of things. I have a function in Access that calculates a specific benchmark our providers are tracked on (code is below). I'm not sure how to do this in a SQL Server Function Code I have so far is below just not sure where to go from there.





    Code:
    Public Function benchmark(ByRef strloB As String, Typeid As Long) As String
        Dim rs As New ADODB.Recordset
        Dim strsql As String, tmpstr
        strsql = "Select Benchmark from tbl_benchmarks2 Where LOB = """ & strloB & """ and ID In "
        
        Select Case Typeid
            Case Is = 1
                tmpstr = "(2,16,1)"
            Case Is = 2
                tmpstr = "(10,15,12)"
            Case Is = 3
                tmpstr = "(3,4)"
            Case Is = 4
                tmpstr = "(9,14)"
            Case Is = 5
                tmpstr = "(11,13)"
        End Select
        strsql = strsql & tmpstr
        rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
        benchmark = ""
        Do Until rs.EOF
            benchmark = str(rs!benchmark)
            rs.MoveNext
        Loop
        
        rs.Close
        Set rs = Nothing
        
        
    
    
    
    End Function

    SQL Server
    Code:
    CREATE FUNCTION BENCHMARK
    (
    	-- Add the parameters for the function here
    	@lob varchar(10,
    	@type numeric(18,0)
    )
    RETURNS varchar(50)
    AS
    BEGIN
    	-- Declare the return variable here
    	DECLARE @bch varchar(50)
    
    	-- Add the T-SQL statements to compute the return value here
    	Select Benchmark from tbl_benchmarks2 Where LOB = strloB and ID In "
    		Case Typeid
    			when = 1 then (2,16,1) 
    			when = 2 then (10,15,12)
    			when = 3 then (3,4)
    			when = 4 then (9,14)
    			when = 5 then (11,13)
        			
    
    	-- Return the result of the function
    	RETURN <@ResultVar, sysname, @Result>
    
    END
    GO

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,626
    I'm not familiar with that return syntax. Here's an example from a db I'm working on:

    Code:
    ALTER function [dbo].[funcGetDriverDayOff](@Driver varchar(9), @DORDate datetime)
    returns varchar(9) as
    begin
    declare    @DriverDayOff varchar(9)
    
    
      SELECT @DriverDayOff = (
        SELECT TOP 1 Days.ReasonCode
        FROM [Bell-SQL64].[Nextgen].[dbo].[tbDriverDaysOff] Days
          LEFT JOIN [Bell-SQL64].[Nextgen].[dbo].[tbDrivers] Drivers on Days.DriverAcctNo = drivers.DriverAcctNo
        WHERE drivers.DriverNo = @Driver AND @DORDate Between Days.FromDate And Days.ToDate
    )
    
    
    return    @DriverDayOff
    end
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    636
    Hi,

    you could try a procedure, example:

    Code:
    -- =============================================
    CREATE procedure [dbo].[pMyProc] 
    
     @lob varchar(10),
    
        @typeID 
    integer,
    
        @Result 
    nvarchar(max)=''output
    
    
     
    
    AS
    
    BEGIN
    
        
    declare @SQL nvarchar(max)=''
    
        
    declare @bch nvarchar(50)
    
        
    declare @BM nvarchar(50)
    
    
        
    select @bch =case @TypeID 
    
                
    when  1 then'(2,16,1)'
    
                
    when  2 then'(10,15,12)'
    
                
    when  3 then'(3,4)'
    
                
    when  4 then'(9,14)'
    
                
    when  5 then'(11,13)'
    
                
    end
    
        
    
        
    create table #BM( benchmark nvarchar(50))--create temp table in memory
    
        
    set @SQL ='insert into #BM(benchmark) select Benchmark from tbl_benchmarks2 Where LOB = '''+ @lob +''' and ID In '+ @bch +''
    
    
        
    execsp_executesql@statement = @SQL
    
        
    DECLARE cBench CURSOR FAST_FORWARD FOR
    
    select    benchmark from #BM
        
    OPEN cBench
        
    FETCHNEXTFROM @BM INTO @BM;
        
    WHILE@@fetch_status= 0
        
    BEGIN
            
    --do here whet you need to do for each record
        
    end
        
    CLOSE cBench;
       
    DEALLOCATE cBench    ;
        
    drop table #BM
    
    END
    
    
    GO
    
    

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

Similar Threads

  1. Replies: 1
    Last Post: 12-03-2015, 08:39 PM
  2. Access 2010 to SQL Server 2008 R2
    By bigroo in forum SQL Server
    Replies: 0
    Last Post: 03-21-2013, 07:02 PM
  3. Replies: 3
    Last Post: 05-17-2012, 05:41 AM
  4. Access 2010 through VPN and Win Server 2008
    By feguillen in forum Misc
    Replies: 1
    Last Post: 12-01-2011, 06:20 PM
  5. Can't connect Access 2010 to SQL Server 2008 R2
    By LAazsx in forum Import/Export Data
    Replies: 6
    Last Post: 12-10-2010, 08:44 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