locked
declaring a function RRS feed

  • Question

  • Hello, I am using the following "SELECT ProjectID FROM ChargeAccountsQ WHERE ID = dbo.POGetChargeAccountID(" & PONumber & ")"
    in a select statement.  The dbo.POGetChargeAccountID gets an error saying it is not declared.  It is a sql server scalar function. 

    How does one declare something like this?

    John

    Wednesday, November 24, 2010 4:20 PM

Answers

  • "tuk"; "tu" wrote in message news:3c249b3a-1062-4e52-813d-03fb4cdb94ad@communitybridge.codeplex.com...

    Hello, I am using the following "SELECT ProjectID FROM ChargeAccountsQ WHERE ID = dbo.POGetChargeAccountID(" & PONumber & ")"
    in a select statement.  The dbo.POGetChargeAccountID gets an error saying it is not declared.  It is a sql server scalar function.

    How does one declare something like this?

    John

    When using linked tables to SQL server, you most certainly can not use any sql server function. JET never allowed this, and thus using an scalar function in that fashion will also not work. This is not a change from trying to use any SQL server syntax or any general SQL server function inside of a query that you build in the access query builder via linked tables. So how this works is not changed for any SQL server function, let alone custom ones.
     
    What you can do however though, is create a passthrough query in the access query builder, then the above syntax will work. However, the WHOLE string MUST be created before you pass it to the server, and I assume in the above PONumber is a local value on the local form that is to be passed to sql server. So, again such a local value cannot possibly be seen by the server system.
     
    So what I would suggest you do is create a pass through query of anything that works correctly in your application, then use the following syntax:
     
     
       Dim qdfPass       As DAO.QueryDef
       Dim rst           As DAO.Recordset
       Dim strSql        As string
       Dim rst           AS DAO.RecordSet
     
     
       strSql = "SELECT ProjectID FROM ChargeAccountsQ WHERE ID = dbo.POGetChargeAccountID(" & PONumber & ")"
       Set qdfPass = CurrentDb.QueryDefs("MyPass")
       qdfPass.SQL = strSql
       
       set rst = qdfPass.OpenRecordSet
     
    To just keep in mind that the sql function you speak of is running on the server side. These sql server functions could never be used and run by the local jet engine when using ODBC linked tables to SQL server.  You can�??t just toss up sql server functions right in the middle of your query syntax when using linked tables.
     
    So, you can use an ADO connection string, but if you not using ADO in your applicaion, then I suggest the above. I think the above pass-through idea above is quite easy and is little code. Simply just create a new query in the query builder, and designated as pass-through. You can use the connection builder, or simply grab a existing connection from a currently linked table. In the debug window just go:
     
    ? currentdb.TableDefs("dbo_custsql").Connect
     
    You get something like:
     
    ODBC;Description=test local;DRIVER=SQL Server;SERVER=albertlaptop;UID=AlbertKallal;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=Database21SQL
     
    Your connection string will be somewhat different, but simply take the current connection string you get, and simply cut and paste this into the ODBC connection in Property sheet setting for the pass-through query. This approached thus eliminates the need for you to go through the handstands of having to build and setup the connection string for the pass-through query. Once you've built such a passthrough query, you can pretty much use it everywhere in your whole application, and change the SQL four on the fly as you please, and this also will work for reports.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
    • Proposed as answer by Mike_HelpYou Friday, December 3, 2010 9:43 AM
    • Marked as answer by Bruce Song Tuesday, December 7, 2010 10:32 AM
    Wednesday, November 24, 2010 8:20 PM

All replies

  • hi John,

    Are you using an .adp or an .accdb? If you're using an .adp then you need the appropriate permissions on that function. Otherwise it should work like this:

    USE [AdventureWorksLT2008R2] ;
    GO
    
    IF EXISTS ( SELECT  *
                FROM    sys.objects
                WHERE   object_id = OBJECT_ID(N'[dbo].[ufnGetOne]')
                        AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ) )
        DROP FUNCTION [dbo].[ufnGetOne] ;
    GO
    
    CREATE FUNCTION ufnGetOne ( )
    RETURNS INT
    AS BEGIN
        RETURN 1 ;
       END
    GO
    
    SELECT  SalesLT.Customer.*
    FROM    SalesLT.Customer
    WHERE   ( CustomerID = dbo.ufnGetOne() ) ;
    GO

    Otherwise you need to explain in more detail what and where you're trying to execute your SQL statement.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, November 24, 2010 4:32 PM
  • I am doing a .mdb.  The dbo is a sql server 2005 scalar function the dba provided that I need to use.  If I use DAO I get an error saying it is not declared.  If I use ADO it is fine.  I might need to use DAO and if so how is it declared in the vba code similar to a variable error saying it is not declared?

    I am using several others in A2007 but they are using ADO and work fine.  Just that the one mentioned in the post might be part of a DAO rather than ADO.

    Thanks.  John

    Wednesday, November 24, 2010 4:53 PM
  • hi John,

    Using DAO or ADO should make no difference as long as you connect to your SQL Server.

    How do you invoke this SQL statement? Code please.

    You may use an passthrough-query to call that function, e.g.

    SELECT dbo.POGetChargeAccountID(123);

    using the appropriate connect string.

    E.g.

    Public Sub MakeAccountIDQuery(APONumber As Long)
       Const CONNECT_STRING As String = _
        "ODBC;DRIVER=SQL Server;SERVER=yourServer;DATABASE=yourDatabase"
       Const PASSTHROUGH_QUERY_NAME As String = "yourQueryNameHere"
       On Local Error Resume Next
       Dim qdf As DAO.QueryDef
       CurrentDbC.QueryDefs.Refresh
      CurrentDbC.QueryDefs.Delete PASSTHROUGH_QUERY_NAME
       Set qdf = New DAO.QueryDef
      With qdf
        .Connect = CONNECT_STRING
        .Name = PASSTHROUGH_QUERY_NAME
        .ReturnsRecords = True
        .Sql = "SELECT dbo.POGetChargeAccountID(" & APONumber & ");"
      End With
       CurrentDbC.QueryDefs.Refresh
      CurrentDbC.QueryDefs.Append qdf
    
    End Sub

    The connect string must be the same as used for your linked tables. You may retrieve it from an linked table as

    ConnectString = CurrentDbC.TableDefs.Item("linkedTableName").Connect

    CurrentDbC is Michel Kaplan's implementation (place it in a standard module):

    Private m_CurrentDb As DAO.Database
    
    Public Property Get CurrentDbC() As DAO.Database
       If m_CurrentDb Is Nothing Then
        Set m_CurrentDb = CurrentDb
      End If
      Set CurrentDbC = m_CurrentDb
    
    End Property

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, November 24, 2010 5:22 PM
  • Stefan, thanks for following along on the post. 

    What I have done is the following but is ADO rather then DAO.  Now this works okay and I have several other dbo the DBA provided that are used this way (msgbox is to see what is returned).  But, this might become part of another piece of existing code that is already DAO. 

      Dim conn As ADODB.Connection
      Dim approved_charge_account As ADODB.Recordset
      Dim sql_po As String
      Set conn = New ADODB.Connection
      conn.Open "Driver={SQL Server};Server=xxxxx;Database=xxxx;User ID=xxxxx;Password=xxxxx;" 

      sql_po = "SELECT ProjectID FROM ChargeAccountsQ WHERE dbo.POGetChargeAccountID(" & PONumber & ")"
      Set approved_charge_account = New ADODB.Recordset
      approved_charge_account.Open sql_po, conn, adOpenKeyset, adLockReadOnly
     
      MsgBox approved_charge_account(0)

    Thanks...John

    Wednesday, November 24, 2010 5:37 PM
  • "tuk"; "tu" wrote in message
    news:3c249b3a-1062-4e52-813d-03fb4cdb94ad@communitybridge.codeplex.com...
    > Hello, I am using the following "SELECT ProjectID FROM ChargeAccountsQ
    > WHERE ID = dbo.POGetChargeAccountID(" & PONumber & ")"
    > in a select statement.  The dbo.POGetChargeAccountID gets an error saying
    > it is not declared.  It is a sql server scalar function.
     
    >
     If you're trying to use that SQL in a normal Access query, it's going to
    have to be a pass-through query.  If you want to execute it solely in code,
    and use DAO for the purpose as you mentioned in a different message, then I
    think you're probably going to have to create a temporary querydef, provide
    the connection information, and mark it as a pass-through query before
    executing it.
     
     

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    Wednesday, November 24, 2010 5:53 PM
  • "tuk"; "tu" wrote in message news:3c249b3a-1062-4e52-813d-03fb4cdb94ad@communitybridge.codeplex.com...

    Hello, I am using the following "SELECT ProjectID FROM ChargeAccountsQ WHERE ID = dbo.POGetChargeAccountID(" & PONumber & ")"
    in a select statement.  The dbo.POGetChargeAccountID gets an error saying it is not declared.  It is a sql server scalar function.

    How does one declare something like this?

    John

    When using linked tables to SQL server, you most certainly can not use any sql server function. JET never allowed this, and thus using an scalar function in that fashion will also not work. This is not a change from trying to use any SQL server syntax or any general SQL server function inside of a query that you build in the access query builder via linked tables. So how this works is not changed for any SQL server function, let alone custom ones.
     
    What you can do however though, is create a passthrough query in the access query builder, then the above syntax will work. However, the WHOLE string MUST be created before you pass it to the server, and I assume in the above PONumber is a local value on the local form that is to be passed to sql server. So, again such a local value cannot possibly be seen by the server system.
     
    So what I would suggest you do is create a pass through query of anything that works correctly in your application, then use the following syntax:
     
     
       Dim qdfPass       As DAO.QueryDef
       Dim rst           As DAO.Recordset
       Dim strSql        As string
       Dim rst           AS DAO.RecordSet
     
     
       strSql = "SELECT ProjectID FROM ChargeAccountsQ WHERE ID = dbo.POGetChargeAccountID(" & PONumber & ")"
       Set qdfPass = CurrentDb.QueryDefs("MyPass")
       qdfPass.SQL = strSql
       
       set rst = qdfPass.OpenRecordSet
     
    To just keep in mind that the sql function you speak of is running on the server side. These sql server functions could never be used and run by the local jet engine when using ODBC linked tables to SQL server.  You can�??t just toss up sql server functions right in the middle of your query syntax when using linked tables.
     
    So, you can use an ADO connection string, but if you not using ADO in your applicaion, then I suggest the above. I think the above pass-through idea above is quite easy and is little code. Simply just create a new query in the query builder, and designated as pass-through. You can use the connection builder, or simply grab a existing connection from a currently linked table. In the debug window just go:
     
    ? currentdb.TableDefs("dbo_custsql").Connect
     
    You get something like:
     
    ODBC;Description=test local;DRIVER=SQL Server;SERVER=albertlaptop;UID=AlbertKallal;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=Database21SQL
     
    Your connection string will be somewhat different, but simply take the current connection string you get, and simply cut and paste this into the ODBC connection in Property sheet setting for the pass-through query. This approached thus eliminates the need for you to go through the handstands of having to build and setup the connection string for the pass-through query. Once you've built such a passthrough query, you can pretty much use it everywhere in your whole application, and change the SQL four on the fly as you please, and this also will work for reports.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
    • Proposed as answer by Mike_HelpYou Friday, December 3, 2010 9:43 AM
    • Marked as answer by Bruce Song Tuesday, December 7, 2010 10:32 AM
    Wednesday, November 24, 2010 8:20 PM
  • hi John,

    so is this the piece of code throwing the error or is this finally a working version?

    If it is still failing, what is the exact error message?


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, November 25, 2010 9:44 AM
  • Hi Jhon,

          I am writing to check the status of the issue on your side. Could you please let me know if the suggestions help you or not? If you have any concerns or questions, please feel free to let us know. We will be more than happy to be of assistance.

    Best Regards,

    Bruce Song


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, December 1, 2010 2:45 AM