Results 1 to 7 of 7
  1. #1
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35

    Converting SQL to Access

    So, this may be a little too much. Currently I have the following procedure in SQL. I am trying to replicate in Access and having all kinds of issues. Are there any types of programs that will convert from SQL to Access?

    USE [Enertia_Working]
    GO
    /****** Object: StoredProcedure [dbo].[sp_DoiMonitorFirstProdSchedule] Script Date: 3/20/2017 1:31:31 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO






    -- =============================================
    -- Create date: 04/09/2014
    -- Description: DOI Monitor Query
    -- Start with a complete list of wells that are Oasis Operated and have
    -- a first production date within the last 365 days
    -- If a well has revenue then exclude else display
    -- First Production, WellId, WellName, State, Prospect, Days from First Production


    -- and NewDeck Incident Status
    -- =============================================
    ALTER PROCEDURE [dbo].[sp_DoiMonitorFirstProdSchedule]
    -- Add the parameters for the stored procedure here
    AS
    BEGIN


    SET NOCOUNT ON;
    IF OBJECT_ID('tempdb..#tmp_DeckIncident') IS NOT NULL
    DROP TABLE #tmp_DeckIncident


    IF OBJECT_ID('tempdb..#tmp_DeckIncidentAction ') IS NOT NULL
    DROP TABLE #tmp_DeckIncidentAction


    IF OBJECT_ID('tempdb..#tmp_OwnNoPdRevenue ') IS NOT NULL
    DROP TABLE #tmp_OwnNoPdRevenue


    IF OBJECT_ID('tempdb..#tmp_firstproddate ') IS NOT NULL
    DROP TABLE #tmp_firstproddate

    IF OBJECT_ID('tempdb..#tmp_table ') IS NOT NULL
    DROP TABLE #tmp_table

    IF OBJECT_ID('tempdb..#tmp_txn ') IS NOT NULL
    DROP TABLE #tmp_txn


    ---temp table to get first prod date within the last year
    SELECT WellCompHid as 'CompId'
    , Comp.WellCompTid
    , MIN(WcFirstProdDate) as FirstProdDate
    INTO #tmp_firstproddate
    FROM Enertia.dbo.pdMasWellCompletion Comp
    INNER JOIN Enertia.dbo.pdMasWellCompFirstDate CompDt
    ON Comp.WellCompTID = CompDt.WellCompTID
    WHERE WCFirstProdDate > DATEADD(year,-1,GETDATE())
    GROUP BY WellCompHid, Comp.WellCompTID





    CREATE TABLE #tmp_OwnNoPdRevenue
    (OwnPmtStatCode varchar(10) NULL
    ,OwnPropHid int)


    SELECT TxnDtlTid, H.TxnHdrTid, TxnRevPropHid, TxnDeckCode, TxnAcctDate, TxnProdDate
    INTO #tmp_txn
    FROM Enertia.dbo.rvTxnDtl d
    INNER JOIN Enertia.dbo.rvTxnHdr h
    ON D.TxnHdrTID = H.TxnHdrTID
    WHERE TxnCorpHid = 1014630 -- 342


    INSERT INTO #tmp_OwnNoPdRevenue
    SELECT DISTINCT NULL, OwnPropHid
    FROM Enertia.dbo.rvBalOwnTxn
    WHERE OwnPropHid IN (SELECT T.txnRevPropHid
    FROM Enertia.dbo.rvMasDeckDcml d
    INNER JOIN (SELECT DISTINCT TxnDeckCode, TxnRevPropHid
    --, TxnDeckCode, TxnAcctDate, txnRevPropHid
    FROM #tmp_txn D
    WHERE TxnAcctDate = (SELECT MAX(TxnAcctDate) FROM #tmp_txn a where txnRevPropHid = D.TxnRevPropHid )
    AND TxnProdDate = (SELECT MAX(TxnProdDate) FROM #tmp_txn a where txnRevPropHid = D.TxnRevPropHid )
    and TxnDeckCode IS NOT NULL ) T
    ON D.DcmlDeckCode = T.TxnDeckCode
    where DcmlOwnHid = '1002871'
    and DcmlExpired= 0 )


    --------------------------------------------------
    --This section was altered for performance reasons
    --------------------------------------------------


    INSERT INTO #tmp_OwnNoPdRevenue
    --SELECT DISTINCT OwnPmtStatCode, OwnPropHid
    --FROM Enertia.dbo.rvBalOwnTxn
    --WHERE (OwnPmtStatCode = 'PD')
    -- AND OwnPropHID NOT IN (SELECT OwnPropHid FROM #tmp_OwnNoPdRevenue)


    SELECT DISTINCT a.OwnPmtStatCode, a.OwnPropHid
    FROM Enertia.dbo.rvBalOwnTxn a
    left join #tmp_OwnNoPdRevenue b
    on a.OwnPropHID = b.OwnPropHID
    WHERE (a.OwnPmtStatCode = 'PD')
    and b.OwnPropHID is NULL


    --Get A comp, deck, incident, incident actions codes
    SELECT DISTINCT
    Rev.DeckRevPropHID,
    Inc.IncidentTID,
    ActionCode,
    Inc.RecByDate,
    IncidentType,
    (SELECT FirstProdDate FROM #tmp_firstproddate WHERE CompId = Rev.DeckRevPropHID) as 'FirstProdDate'
    INTO #tmp_DeckIncident
    FROM Enertia.dbo.rvMasDeckProp Rev
    INNER JOIN Enertia.dbo.aaMasIncidentDeckAprv IncDec
    ON Rev.DeckCode = IncDec.DeckCode
    INNER JOIN Enertia.dbo.rvMasDeckProp RevDeck
    ON RevDeck.DeckCode = IncDec.DeckCode
    INNER JOIN Enertia.dbo.aaMasIncident Inc
    ON IncDec.IncidentTID = Inc.IncidentTid
    INNER JOIN Enertia.dbo.aaMasIncidentAction Act
    ON Inc.IncidentTID = Act.IncidentTID
    WHERE Inc.IncidentType = 'NEWDECK'
    ORDER BY Rev.DeckRevPropHid


    --- Goal is to get action code and list of comp that might or might not have incidents
    SELECT DeckRevPropHID,
    (SELECT Count(1) FROM #tmp_DeckIncident WHERE ActionCode = 'ACCTGREV' AND DeckRevPropHID = temp.DeckRevPropHID) as ActionCount
    INTO #tmp_DeckIncidentAction
    FROM #tmp_DeckIncident temp
    GROUP BY DeckRevPropHID



    SELECT
    HdrHid
    , HdrCode
    , HdrName
    , Oper.OperatorHid
    , FirstProdDate
    , (SELECT MapState FROM Enertia.dbo.aaMasMapLocation WHERE MapUseRev = 1 AND MapHdrHid = Comp.HdrHid GROUP BY MapHdrHid, MapState) as 'State'
    , (SELECT R1.HdrName AS P
    FROM Enertia.dbo.fbMasHdr H
    LEFT JOIN Enertia.dbo.fbMasHdrRelation C ON H.HdrHID = C.HdrRelChildHID
    LEFT JOIN Enertia.dbo.fbMasHdr R ON C.HdrRelParentHID = R.HdrHID
    LEFT JOIN Enertia.dbo.fbMasHdrType T2 ON R.HdrTypeTID = T2.HdrTypeTID
    LEFT JOIN Enertia.dbo.fbMasHdrRelation C1 ON R.HdrHID = C1.HdrRelChildHID
    LEFT JOIN Enertia.dbo.fbMasHdr R1 ON C1.HdrRelParentHID = R1.HdrHID
    LEFT JOIN Enertia.dbo.fbMasHdrType T4 ON R1.HdrTypeTID = T4.HdrTypeTID
    WHERE H.HdrHID = Comp.HdrHid
    AND T2.HdrTypeCode IN ('Well')
    AND T4.HdrTypeCode IN ('Prospect')
    ) AS 'Prospect'
    , (SELECT DATEDIFF (DAY, FirstProdDate,GETDATE()) FROM #tmp_firstproddate WHERE CompId = Comp.HdrHid) as 'DaysFromFirstProdDate'
    , ActionCount
    , (SELECT ActionCode = CASE WHEN ActionCount = 0 THEN 'Finalized By Accounting'
    WHEN ActionCount >= 1 THEN 'Completed By DO'
    ELSE 'Not Started' END) NewDeckIncidentStatus
    , OwnPmtStatCode
    INTO #tmp_table
    FROM Enertia.dbo.fbMAsHdr Comp
    INNER JOIN Enertia.dbo.pdMasOperated Oper
    ON Comp.HdrHid = Oper.OperatedHID
    AND HdrTypeTid = '400065' -- Oasis
    AND Oper.OperatedEffEnd = '2078-12-31 00:00:00'
    AND OperatorHID = 1014630
    LEFT JOIN #tmp_OwnNoPdRevenue Rev
    ON Rev.OwnPropHid = Comp.HdrHid
    INNER JOIN #tmp_firstproddate Fp
    ON FP.CompId = Comp.HdrHid
    LEFT JOIN #tmp_DeckIncidentAction Act
    ON Act.DeckRevPropHID = Comp.HdrHid
    --WHERE Comp.HdrCode IN ('15812.1')
    AND OwnPmtStatCode IS NULL OR OwnPmtStatCode = ''
    ORDER BY HdrCode


    SELECT
    FirstProdDate
    , HdrCode as 'Well ID'
    , HdrName as 'Well Name'
    , State
    , Prospect
    , DaysFromFirstProdDate
    , NewDeckIncidentStatus
    , ActionCount
    FROM #tmp_table
    WHERE OwnPmtStatCode IS NULL
    ORDER BY FirstProdDate asc, HdrName asc


    END

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    No translator so far as I know. I presume you are taking a sql server based database and moving it to access backend and want the same functionality. If just want to run this from acces on a sql server database, you can still run it from access.

    Access SQL is very similar to TSQL and will often just copy across, subject to table and field names. but does not have stored procedures, for that you need to use VBA to run each sql statement separately.

    Access does not have DROP, instead you use vba code to delete a table

    sql like this

    SELECT DeckRevPropHID,
    (SELECT Count(1) FROM #tmp_DeckIncident WHERE ActionCode = 'ACCTGREV' AND DeckRevPropHID = temp.DeckRevPropHID) as ActionCount
    INTO #tmp_DeckIncidentAction
    FROM #tmp_DeckIncident temp
    GROUP BY DeckRevPropHID

    should pretty much run as is. You don't have virtual tables like #tmp_DeckIncidentAction, but you would create a temporary table instead, most likely in a temporary backend

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just an aside
    Access supports the SQL DROP as in the following.
    -CurrentProject.Connection.Execute "Drop table tblToDrop", dbFailOnError
    - DoCmd.RunSQL ("Drop table tblToDrop")

  4. #4
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35
    Can I run the SQL Server process from Access and get the results on a report in access? That would be much easer than recreating everything.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, look at pass-through queries. Create one with the appropriate connection string and your SQL is simply:

    Exec sp_DoiMonitorFirstProdSchedule
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35
    Can I put in the query the connection to use?

  7. #7
    cbramsey is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2017
    Posts
    35
    nevermind, found it on the property sheet

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

Similar Threads

  1. Replies: 7
    Last Post: 06-01-2015, 04:04 PM
  2. Replies: 2
    Last Post: 02-11-2014, 09:41 AM
  3. Replies: 7
    Last Post: 05-13-2013, 11:37 AM
  4. Converting Access Backend from Access 2003 to SQL Question
    By seattlebrew in forum Database Design
    Replies: 0
    Last Post: 03-07-2013, 07:14 PM
  5. Converting Excel VBA to Access VBA
    By jo15765 in forum Programming
    Replies: 10
    Last Post: 10-13-2011, 07:59 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