Does Ms Access(2003) Have Anything Comparable to Stored Procedure. I Want to Run a Complex Query in Ms Acceess

Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess

You can concatenate the records with a User Defined Function (UDF).

The code below can be pasted 'as is' into a standard module. The SQL for you example would be:

SELECT tbl.A, Concatenate("SELECT B  FROM tbl
        WHERE A = " & [A]) AS ConcA
FROM tbl
GROUP BY tbl.A

This code is by DHookom, Access MVP, and is taken from http://www.tek-tips.com/faqs.cfm?fid=4233

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ") _
            As String
    'example
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID, FirstName, DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    '    John, Mary, Susan
    'in a Query
    '(This SQL statement assumes FamID is numeric)
    '===================================
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    '     WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '===================================
    '
    'If the FamID is a string then the SQL would be
    '===================================
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    '     WHERE FamID =""" & [FamID] & """") as FirstNames
    'FROM tblFamily
    '===================================

    '======For DAO uncomment next 4 lines=======
    '======     comment out ADO below    =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

    '======For ADO uncomment next two lines=====
    '======     comment out DAO above     ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                    .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    '====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function 

Create stored procedure in MS Access?

I know it is possible to create a Stored Procedure directly in access but it isn't pretty to do.

Your best options from here would be to :

• Simulate a Stored Procedured using a function. I found a tutorial here. But I haven't tried this approach.
• Another Popular approach seems to be to import an SQLServer Stored Procedure and Import it in Acess. Try this link if you like this approach.
• If your using MS Access 2010, StoredProcedures will be listed in the tables tab under Named Macros.

Hope this helps.

Using MS Access VBA Function using Query in VB6

You cannot use user defined functions (UDFs) outside of Access.

many-to-many into summary column

This may help Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess

How do I make a stored procedure in MS Access?

Access 2010 has both stored procedures, and also has table triggers. And, both features are available even when you not using a server (so, in 100% file based mode).

If you using SQL Server with Access, then of course the stored procedures are built using SQL Server and not Access.

For Access 2010, you open up the table (non-design view), and then choose the table tab. You see options there to create store procedures and table triggers.

For example:

Note that the stored procedure language is its own flavor just like Oracle or SQL Server (T-SQL). Here is example code to update an inventory of fruits as a result of an update in the fruit order table

Keep in mind these are true engine-level table triggers. In fact if you open up that table with VB6, VB.NET, FoxPro or even modify the table on a computer WITHOUT Access having been installed, the procedural code and the trigger at the table level will execute. So, this is a new feature of the data engine jet (now called ACE) for Access 2010. As noted, this is procedural code that runs, not just a single statement.

Speed up MSAccess 2003 and MSSQL

Actually, in most cases Access does a decent job. If your query has some joins, but ESPECIALLY has some aggregate group by, then simply create a view, and then link to that view. It will likely run as fast as a pass-through query, but without the hassle and effort on the Access side.

So in most cases, you should be able obtain excellent performance when using Access as a front end to SQL server.

So, for calling store procedures, or for some reports, you can consider using pass-through query.

However, to save time and effort on your part, then for quires with a join and especially those with aggregates like group by or count, then just create a view server side and then link to that.

You find the performance first rate when you do this.

Invoke MS Access Query which calls a VBA Function in a Module from Delphi ADO Component

You can't.

Queries using user-defined VBA functions can only be executed by the Access Application object, and only through the DoCmd.OpenQuery and DoCmd.RunSQL methods.

Instead, use COM and the Access Application object to automate the task. Do note this will cause considerable overhead. Or, when possible, do the calculation in the SQL clause.

They're not available through ODBC or OLEDB, thus not available in ADO.

If the full version of Access (not runtime version or database engine) isn't available, you unfortunately can't run queries that use user-defined VBA functions.

Query too complex

It looks like you need an aggregation query, instead of this complex one. E.g.

select companyName, assetName, year, 
   Sum(DatapointValue) as MPPOilRevised
from PEBaseQuery 
where DatapointID in (2032, 2034, 2042, 2036)
group by companyName, assetName, year

The only issue is multiplying to 1000000 for the first Data Point. You could try IIF for that:

select companyName, assetName, year, 
   Sum(IIf(DatapointID=2003,DatapointValue*1000000,DatapointValue)) as MPPOilRevised
from PEBaseQuery 
where DatapointID in (2032, 2034, 2042, 2036)
group by companyName, assetName, year

Also please try such 'crazy' query, with sub query for this particular DatapointID, without IIF:

select companyName, assetName, year, SUM(DatapointValue) 
        + (select SUM(DatapointValue * 1000000) from  PEBaseQuery q2 
           where q2.companyName = q1.companyName 
              and q2.assetName= q1.assetName
              and q2.year= q1.year
              and q2.DatapointID = 2003
           group by companyName, assetName, year)
    from PEBaseQuery q1
    where DatapointID in (2032, 2034, 2042, 2036)
    group by companyName, assetName, year

Update for 'Production Max Potential'. Please try following:

select b.companyName, b.assetName, IIf(b.calculationResult > mp.calculationResult,b.calculationResult,mp.calculationResult)  as MPPOilRevised
from 
    (select companyName, assetName, year, Sum(IIf(DatapointID=2003,DatapointValue*1000000,DatapointValue)) as calculationResult
        from PEBaseQuery 
        where DatapointID in (2032, 2034, 2042, 2036) 
        group by companyName, assetName, year) b --Base
     left join 
     (select companyName, assetName, year, 
        Sum(DatapointValue) as calculationResult
        from PEBaseQuery 
        where DatapointID = 2218
        group by companyName, assetName, year) mp -- Max Potential
    on b.companyName= mp.companyName
        and b.assetName = mp.assetName
        and b.year = mp.year

Example of calculation with substract logic. Updated with final crazy SQL. Please also note that I would go with application logic or stored procedures for such kind of things:

    select b.companyName, b.assetName, IIf(b.calculationResult > mp.calculationResult,b.calculationResult,mp.calculationResult)  as MPPOilRevised
from 
    (select companyName, assetName, year, SUM(DatapointValue) 
        + (select SUM(DatapointValue * 1000000) from  PEBaseQuery q2 
           where q2.companyName = q1.companyName 
              and q2.assetName= q1.assetName
              and q2.year= q1.year
              and q2.DatapointID = 2003
           group by companyName, assetName, year)
        - (select SUM(DatapointValue) from  PEBaseQuery q2 
           where q2.companyName = q1.companyName 
              and q2.assetName= q1.assetName
              and q2.year= q1.year
              and q2.DatapointID = 2029 
           group by companyName, assetName, year)
    from PEBaseQuery q1
    where DatapointID in (2032, 2034, 2042, 2036)
    group by companyName, assetName, year) b --Base
     left join 
     (select companyName, assetName, year, 
        Sum(DatapointValue) as calculationResult
        from PEBaseQuery 
        where DatapointID = 2218
        group by companyName, assetName, year) mp -- Max Potential
    on b.companyName= mp.companyName
        and b.assetName = mp.assetName
        and b.year = mp.year

---

Related Topics