Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267

    Unanswered: Writing Pass Through Queries in VBA

    I'm wondering if there is a way to write an SQL Pass Through Query using VBA? The reason that I want to do it this way is so that I can change the Connection String "on the fly". Or is there a way to access the Connection String using a Query Def object? Thanks.

    Canupus
    Last edited by canupus; 07-31-04 at 21:24. Reason: Spelling Error

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    in DAO, yes to both!

    dim wksp as dao.workspace
    dim dabs as dao.database
    dim strSQL as string
    dim strCon as strin
    strSQL = "UPDATE tblName SET tblName.Field = 99"
    strCon = "your connection string"
    set wksp = dbengine(0)
    set dabs = wksp.opendatabase("", False, False, strCon)
    dabs.execute strSQL, dbSQLpassThrough


    or via querydef:
    qdef.connection = "your connection string"


    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Aug 2005
    Location
    Manchester, England
    Posts
    3
    There is a better way to create a dynamic PassThrough query using VBA to connect to SQL Server 2005 in this case.



    Sub RunPassThrough(strSQL As String)

    Dim qdfPassThrough As DAO.QueryDef, MyDB As Database
    Dim strConnect As String

    If Not IsNull(CurrentDb.QueryDefs("qrySQLPass").SQL) Then 'doesn't exist
    CurrentDb.QueryDefs.Delete "qrySQLPass"
    End If

    Set MyDB = CurrentDb()

    Set qdfPassThrough = MyDB.CreateQueryDef("qrySQLPass")

    strConnect = "DRIVER=SQL Server;SERVER=Your_server_name;DATABASE=Your_datab ase_name;Uid=Your_userid;Pwd=Your_password;"

    qdfPassThrough.Connect = "ODBC;" & strConnect
    qdfPassThrough.SQL = strSQL
    qdfPassThrough.ReturnsRecords = False
    qdfPassThrough.Close

    Application.RefreshDatabaseWindow

    DoCmd.OpenQuery "qrySQLPass", acViewNormal, acReadOnly
    DoCmd.Maximize

    End Sub


    The code above will create a passthrough query and run it, as long as the connection string is correct. You also need to ensure you set to FALSE the 'ReturnsRecords' property otherwise you might get a message back saying 'property not found'

    Thanks

    Eddy Jawed
    Last edited by EddyJawed; 12-17-10 at 13:32.

  4. #4
    Join Date
    Apr 2011
    Posts
    10

    MS Access Pass-thru query

    Quote Originally Posted by EddyJawed View Post
    There is a better way to create a dynamic PassThrough query using VBA to connect to SQL Server 2005 in this case.



    Sub RunPassThrough(strSQL As String)

    Dim qdfPassThrough As DAO.QueryDef, MyDB As Database
    Dim strConnect As String

    If Not IsNull(CurrentDb.QueryDefs("qrySQLPass").SQL) Then 'doesn't exist
    CurrentDb.QueryDefs.Delete "qrySQLPass"
    End If

    Set MyDB = CurrentDb()

    Set qdfPassThrough = MyDB.CreateQueryDef("qrySQLPass")

    strConnect = "DRIVER=SQL Server;SERVER=Your_server_name;DATABASE=Your_datab ase_name;Uid=Your_userid;Pwd=Your_password;"

    qdfPassThrough.Connect = "ODBC;" & strConnect
    qdfPassThrough.SQL = strSQL
    qdfPassThrough.ReturnsRecords = False
    qdfPassThrough.Close

    Application.RefreshDatabaseWindow

    DoCmd.OpenQuery "qrySQLPass", acViewNormal, acReadOnly
    DoCmd.Maximize

    End Sub


    The code above will create a passthrough query and run it, as long as the connection string is correct. You also need to ensure you set to FALSE the 'ReturnsRecords' property otherwise you might get a message back saying 'property not found'

    Thanks

    Eddy Jawed

    I am complete novice in using this one in access, any can give me a sample MDB using this one, like displaying the created query in a form or report?

    thank you,
    OcaVid

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's another example.

    - If you provide the parameter QueryName a query will be created with the provided name (this is useful for a SELECT statement as you expect to retrieve the resulting data set).

    - If QueryName is not provided or is an empty string ( = "" ) no query object will be created but the SQL statement or stored procedure will be executed (useful for DELETE, INSERT, UPDATE statements).
    Code:
    Function Test_SQL_PassThrough(ByVal ConnectionString As String, _
                                  ByVal SQL As String, _
                                  Optional ByVal QueryName As String)
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef
        With qdf
            .Name = QueryName
            .Connect = ConnectionString
            .SQL = SQL
            .ReturnsRecords = (Len(QueryName) > 0)
            If .ReturnsRecords = False Then
                .Execute
            Else
                If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName
                dbs.QueryDefs.Append qdf
            End If
            .Close
        End With
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Function
    Have a nice day!

  6. #6
    Join Date
    Apr 2011
    Posts
    10
    Quote Originally Posted by Sinndho View Post
    Here's another example.

    - If you provide the parameter QueryName a query will be created with the provided name (this is useful for a SELECT statement as you expect to retrieve the resulting data set).

    - If QueryName is not provided or is an empty string ( = "" ) no query object will be created but the SQL statement or stored procedure will be executed (useful for DELETE, INSERT, UPDATE statements).
    Code:
    Function Test_SQL_PassThrough(ByVal ConnectionString As String, _
                                  ByVal SQL As String, _
                                  Optional ByVal QueryName As String)
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef
        With qdf
            .Name = QueryName
            .Connect = ConnectionString
            .SQL = SQL
            .ReturnsRecords = (Len(QueryName) > 0)
            If .ReturnsRecords = False Then
                .Execute
            Else
                If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName
                dbs.QueryDefs.Append qdf
            End If
            .Close
        End With
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Function
    I am very Sorry... but how do i use this function?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You copy it into a module then call it from wherever you need to send SQL statements to the server. Ex (creating a persistent query object):
    Code:
    Private Sub Form_Load()
    
        Dim strConnection As String
        Dim strSQL As String
        Dim strQueryName As String
        
        strConnection = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;TRUSTED_CONNECTION=Yes;"
        strSQL = "SELECT SysCounter, FK_Tbl_Users, Area_Name, Comment " & _
                 "FROM Tbl_Areas " & _
                 "WHERE Inactive = 0 " & _
                 "ORDER BY Area_Name;"
        strQueryName = "Qry_Select_From_Tbl_Area"
        If Test_SQL_PassThrough(strConnection, strSQL, strQueryName) = True Then Me.RecordSource = strQueryName
        
    End Sub
    Or (sending a SQL statement to the server):
    Code:
    Private Sub Command_Delete_Click()
    
        Dim strConnection As String
        Dim strSQL As String
        Dim strQueryName As String
        
        strConnection = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;TRUSTED_CONNECTION=Yes;"
        strSQL = "DELETE FROM Tbl_Areas WHERE Tbl_Areas.SysCounter = " & Me.SysCounter.Value & ";"
        If ExecuteSQL(strConnection, strSQL, strQueryName) = True Then MsgBox "Row deleted.", vbInformation, "Done"
    
    End Sub
    Or (calling a stored procedure):
    Code:
    Private Sub Command_Archive_Click()
    
        Dim strConnection As String
        Dim strSQL As String
        Dim strQueryName As String
        
        strConnection = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;TRUSTED_CONNECTION=Yes;"
        strSQL = "SP_Tbl_Areas_Archive"
        If ExecuteSQL(strConnection, strSQL, strQueryName) = True Then MsgBox "Table archived.", vbInformation, "Done"
    
    End Sub
    Have a nice day!

  8. #8
    Join Date
    Apr 2011
    Posts
    10

    Thumbs up

    Quote Originally Posted by Sinndho View Post
    You copy it into a module then call it from wherever you need to send SQL statements to the server. Ex (creating a persistent query object):
    Code:
    Private Sub Form_Load()
    
        Dim strConnection As String
        Dim strSQL As String
        Dim strQueryName As String
        
        strConnection = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;TRUSTED_CONNECTION=Yes;"
        strSQL = "SELECT SysCounter, FK_Tbl_Users, Area_Name, Comment " & _
                 "FROM Tbl_Areas " & _
                 "WHERE Inactive = 0 " & _
                 "ORDER BY Area_Name;"
        strQueryName = "Qry_Select_From_Tbl_Area"
        If Test_SQL_PassThrough(strConnection, strSQL, strQueryName) = True Then Me.RecordSource = strQueryName
        
    End Sub
    Or (sending a SQL statement to the server):
    Code:
    Private Sub Command_Delete_Click()
    
        Dim strConnection As String
        Dim strSQL As String
        Dim strQueryName As String
        
        strConnection = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;TRUSTED_CONNECTION=Yes;"
        strSQL = "DELETE FROM Tbl_Areas WHERE Tbl_Areas.SysCounter = " & Me.SysCounter.Value & ";"
        If ExecuteSQL(strConnection, strSQL, strQueryName) = True Then MsgBox "Row deleted.", vbInformation, "Done"
    
    End Sub
    Or (calling a stored procedure):
    Code:
    Private Sub Command_Archive_Click()
    
        Dim strConnection As String
        Dim strSQL As String
        Dim strQueryName As String
        
        strConnection = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;TRUSTED_CONNECTION=Yes;"
        strSQL = "SP_Tbl_Areas_Archive"
        If ExecuteSQL(strConnection, strSQL, strQueryName) = True Then MsgBox "Table archived.", vbInformation, "Done"
    
    End Sub
    Thank you, it works beautifully!

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  10. #10
    Join Date
    Nov 2013
    Posts
    1
    Quote Originally Posted by Sinndho View Post
    You copy it into a module then call it from wherever you need to send SQL statements to the server. Ex (creating a persistent query object):
    Code:
    Private Sub Form_Load()
    
        Dim strConnection As String
        Dim strSQL As String
        Dim strQueryName As String
        
        strConnection = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;TRUSTED_CONNECTION=Yes;"
        strSQL = "SELECT SysCounter, FK_Tbl_Users, Area_Name, Comment " & _
                 "FROM Tbl_Areas " & _
                 "WHERE Inactive = 0 " & _
                 "ORDER BY Area_Name;"
        strQueryName = "Qry_Select_From_Tbl_Area"
        If Test_SQL_PassThrough(strConnection, strSQL, strQueryName) = True Then Me.RecordSource = strQueryName
        
    End Sub
    Hi,

    In the example you provided, the call to the function Test_SQL_PassThrough expects the return of some success indicator; however, the function code does not seem to return anything.

    Quote Originally Posted by Sinndho View Post
    Here's another example.

    - If you provide the parameter QueryName a query will be created with the provided name (this is useful for a SELECT statement as you expect to retrieve the resulting data set).

    - If QueryName is not provided or is an empty string ( = "" ) no query object will be created but the SQL statement or stored procedure will be executed (useful for DELETE, INSERT, UPDATE statements).
    Code:
    Function Test_SQL_PassThrough(ByVal ConnectionString As String, _
                                  ByVal SQL As String, _
                                  Optional ByVal QueryName As String)
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef
        With qdf
            .Name = QueryName
            .Connect = ConnectionString
            .SQL = SQL
            .ReturnsRecords = (Len(QueryName) > 0)
            If .ReturnsRecords = False Then
                .Execute
            Else
                If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName
                dbs.QueryDefs.Append qdf
            End If
            .Close
        End With
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Function
    Where/how in the function should I test for success?

    Thanks,

    Pedro

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can trap errors at the VBA level (On Error Goto...), then return a boolean value, for instance:
    Code:
    FunctionName = (Err.Number = 0)  ' Returns True when no error occured, False otherwise.
    You can also trap the error at the SQL level when working with stored procedures. Here's an example of what I usually use:
    Code:
    CREATE PROCEDURE [dbo].[Proc_Tbl_Users_Insert]
    (
           @User_Name NVARCHAR (128)
         , @User_Alias NVARCHAR (50)
         , @Department NVARCHAR (50)
         , @Comment NVARCHAR (255)
    )
    AS
    BEGIN
        DECLARE @ErrorMsg NVARCHAR(2000) 
        DECLARE @ProcName NVARCHAR(128)   
        DECLARE @ReturnValue INT
        DECLARE @Status INT
        BEGIN TRY
            INSERT INTO [Tbl_Users]   (
                                          [User_Name]
                                        , User_Alias
                                        , Department
                                        , Comment
                                      )
                               VALUES (
                                          @User_Name
                                        , @User_Alias
                                        , @Department
                                        , @Comment
                                      )
            SET @ReturnValue = IDENT_CURRENT('Tbl_Users')
            SET @Status = -1
        END TRY
        BEGIN CATCH
            SET @ProcName = OBJECT_NAME(@@PROCID);
            SET @ErrorMsg = ERROR_MESSAGE() 
            SET @ReturnValue = ERROR_NUMBER()
            SET @Status = 0
            EXEC Proc_Log_ProcedureEvent @ProcName, @ReturnValue , @ErrorMsg
        END CATCH
        SELECT @ReturnValue AS ReturnValue, @Status AS [Status]
        RETURN @Status
    END
    With Proc_Log_ProcedureEvent being a stored procedure that writes info about the error into a table named Tbl_Log_Procedures:
    Code:
    CREATE PROCEDURE [dbo].[Proc_Log_ProcedureEvent]
    (
          @ProcedureName NVARCHAR(127)
        , @ErrorCode INT = 0
        , @ErrorMessage NVARCHAR(2048) = ''
        , @LogCode INT = 0
        , @LogCredentials INT = 0
        , @LogMessage NVARCHAR(1024) = ''
    )
    AS
    BEGIN
        BEGIN TRY
            INSERT INTO [Tbl_Log_Procedures] 
                      ( [Procedure_Name]
                      , [Error_Code]
                      , [Error_Message]
                      , [Log_Code]
                      , [Log_Credentials]
                      , [Log_Message] )
                 VALUES 
                      ( @ProcedureName
                      , @ErrorCode
                      , @ErrorMessage
                      , @LogCode
                      , @LogCredentials
                      , @LogMessage )
        END TRY
        BEGIN CATCH
            INSERT INTO [Tbl_Log_Procedures] 
                      ( [Procedure_Name]
                      , [Error_Code]
                      , [Error_Message] )
                 VALUES 
                      ( 'Proc_Log_ProcedureEvent'
                      , ERROR_NUMBER()
                      , ERROR_MESSAGE() )
        END CATCH
    END
    When the procedure succeeds, it returns the Identity of the new row and -1 (True in VBA). When an error occurs, it returns the SQL error code and 0 (False in VBA). You can retrieve the info as follows:
    Code:
    Dim qdf As DAO.QueryDef
    Dim var as Variant
    
    Set qdf = CurrentDb.CreateQuerydef("") ' Temporary (nameless) querydef.
    With qdf
        .Connect = strConnection ' ex. "ODBC;DRIVER={SQL Server};SERVER=SANDBOX;DATABASE=Sales;Trusted_Connection=Yes;"
        .SQL = strSQL ' ex. "Proc_Tbl_Users_Insert @User_Name='SomeOne', @User_Alias='Newbie', @Department='Sales', @Comment='New User'"
        var = .OpenRecordset.GetRows
        .Close
    End With
    Set qdf = Nothing
    If var(1, 0) = True Then    ' Success.
        lngNewRowId = var(0,0)
    Else                        ' Error while executing strSQL.
        lngSQLError = var(0, 0)
    End If
    Have a nice day!

  12. #12
    Join Date
    Apr 2013
    Posts
    1

    What about using a Table Defined Function as a Report Recordsource

    Sinndho your posts are extremely helpful but I was wondering if it's possible to use a UDF Table Value function as a record source for a report in Access Project? I inherited this database and I am not experienced in using server side functions as record source objects in vba. If you need any of my code to view I will be happy to post or if you have and example of using a UDF that would be very helpful.

    Thanks!!!!
    Mike

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I know (I'm not what I would call a SQL Server expert), you cannot retrieve anything in Access from a UDF. You must use a stored procedure that acts as a relay (carrier?) between the UDF and Access. Ex.
    Code:
    CREATE FUNCTION [dbo].[Fn_Get_User_ShortName]
    (
    ) 
    RETURNS NVARCHAR (50)
    AS
    BEGIN
    	DECLARE @FullUserName NVARCHAR(128)
            DECLARE @Separator INT
            DECLARE @UserName NVARCHAR (128)
            
            SET @FullUserName = SUSER_SNAME()
            SET @Separator = CHARINDEX('\', @FullUserName) + 1
            SET @UserName = SUBSTRING(@FullUserName, @Separator, 50)
        RETURN (@UserName)
    END
    
    GO
    If you want to call this function in Access you can use:
    Code:
    CREATE PROCEDURE [dbo].[Proc_Get_User_ShortName]
    AS
    BEGIN
        DECLARE @ErrorMsg NVARCHAR(2000) 
        DECLARE @ProcName NVARCHAR(128)   
        DECLARE @Status INT
        DECLARE @ReturnValue INT
        BEGIN TRY
            SELECT dbo.Fn_Get_User_ShortName();
            SET @ReturnValue = 0;
            SET @Status = -1;
        END TRY
        BEGIN CATCH
            SET @ProcName = OBJECT_NAME(@@PROCID);
            SET @ErrorMsg = ERROR_MESSAGE(); 
            SET @ReturnValue = ERROR_NUMBER()
            SET @Status = 0
            EXEC Proc_Log_ProcedureEvent @ProcName, @ReturnValue , @ErrorMsg;
        END CATCH
        SELECT @ReturnValue AS ReturnValue, @Status AS [Status];
        RETURN @Status;
    END
    
    GO
    or create a pass-through query in Access:
    Code:
    Dim qdf As DAO.QueryDef
    Dim var as Variant
    
    Set qdf = CurrentDb.CreateQuerydef("") ' Temporary (nameless) querydef.
    With qdf
        .Connect = strConnection ' ex. "ODBC;DRIVER={SQL Server};SERVER=SANDBOX;DATABASE=Sales;Trusted_Connection=Yes;"
        .SQL = "SELECT dbo.Fn_Get_User_ShortName();"
        var = .OpenRecordset.GetRows
        .Close
    End With
    Set qdf = Nothing
    Usually I tend to avoid the second solution. In the applications I write, the VBA Class used to communicate with the SQL server ignores the actual names of the stored procedures it is able to call. The names of these SP are stored into a table names Tbl_Procedures_Catalog on the server, associated with the class name and a symbolic name. Ex.
    Class name: Cls_UsersManager
    Table Tbl_Procedures_Catalog:
    Code:
    SysCounter  |    Form_Name      |    Role_Name   |    Procedure_Name         |  Parameters |  Inactive
    ------------+-------------------+----------------+---------------------------+-------------+-----------
    104         |  Cls_UsersManager |   GetList      |   Proc_Tbl_Users_Select   | NULL        |   0
    105         |  Cls_UsersManager |   GetRow       |   Proc_Tbl_Users_Select   | NULL        |   0
    106         |  Cls_UsersManager |   ActivateRow  |   Proc_Tbl_Users_Activate | NULL        |   0
    107         |  Cls_UsersManager |   InsertRow    |   Proc_Tbl_Users_Insert   | NULL        |   0
    108         |  Cls_UsersManager |   UpdateRow    |   Proc_Tbl_Users_Update   | NULL        |   0
    109         |  Cls_UsersManager |   DeleteRow    |   Proc_Tbl_Users_Delete   | NULL        |   0
    110         |  Cls_UsersManager |   FindRow      |   Proc_Tbl_Users_Find     | NULL        |   0
    116         |  Cls_UsersManager |   GetShortName |   Proc_Get_User_ShortName | NULL        |   0
    When the Class Cls_UsersManager is instanciated, it calls a stored procedure named Proc_Get_Procedures_Catalog (through a Cls_Catalog Class that can be used by any SQL Server interface class such as Cls_UsersManager):
    Code:
    CREATE PROCEDURE [dbo].[Proc_Get_Procedures_Catalog]
    (
         @ApplicationName NVARCHAR(127)
       , @ClassName NVARCHAR(127) 
    )
    AS
    BEGIN
        DECLARE @ErrorMsg NVARCHAR(2000) 
        DECLARE @ProcName NVARCHAR(128)   
        DECLARE @Status INT
        DECLARE @ReturnValue INT
        DECLARE @UserCredentials INT
    
        BEGIN TRY
            IF (SELECT [dbo].[Fn_Authorize](@ApplicationName, NULL)) > 0
                BEGIN
                    SELECT Tbl_Procedures_Catalog.Form_Name
                         , Tbl_Procedures_Catalog.Role_Name
                         , Tbl_Procedures_Catalog.[Procedure_Name]
                         , CASE WHEN ISNULL(Tbl_Procedures_Catalog.[Parameters], '') = ''
                                THEN [dbo].[Fn_Get_Procedure_Parameters](Tbl_Procedures_Catalog.[Procedure_Name]) 
                                ELSE Tbl_Procedures_Catalog.[Parameters]
                           END AS [Parameters]
                         , Tbl_Procedures_Catalog.VectParams  
                      FROM Tbl_Procedures_Catalog
                     WHERE (  Tbl_Procedures_Catalog.Form_Name LIKE @ClassName AND
                              Tbl_Procedures_Catalog.Inactive = 0
                           );
                     SET @ReturnValue = @@ROWCOUNT
                     SET @Status = -1
                END
            ELSE
                BEGIN
                    SET @ProcName = OBJECT_NAME(@@PROCID);
                    SET @ReturnValue = 70
                    SET @Status = 0
                    EXEC Proc_Log_ProcedureEvent @ProcedureName = @ProcName
                                               , @LogCode = @ReturnValue
                                               , @LogCredentials = @UserCredentials
                                               , @LogMessage = 'Permission denied'            
                END
        END TRY
        BEGIN CATCH
            SET @ProcName = OBJECT_NAME(@@PROCID);
            SET @ErrorMsg = ERROR_MESSAGE() 
            SET @ReturnValue = ERROR_NUMBER()
            SET @Status = 0
            EXEC Proc_Log_ProcedureEvent @ProcName, @ReturnValue , @ErrorMsg
        END CATCH
        SELECT @ReturnValue AS ReturnValue, @Status AS [Status]
        RETURN @Status
    END
    
    GO
    This procedure will return the actual name of each SP that the class is allowed to call, the symbolic name associated with each SP and the list of parameters expected by each SP. A UDF verifies that the calling class is from an application allowed to use the database (through dbo.Fn_Authorize) and another one (dbo.Fn_Get_Procedure_Parameters) interrogates the system views of the database to retrieve the parameters if the column Parameters of the table is blank or Null.
    Have a nice day!

  14. #14
    Join Date
    Jan 2006
    Posts
    2

    PassThru qustions

    Quote Originally Posted by Sinndho View Post
    Here's another example.

    - If you provide the parameter QueryName a query will be created with the provided name (this is useful for a SELECT statement as you expect to retrieve the resulting data set).

    - If QueryName is not provided or is an empty string ( = "" ) no query object will be created but the SQL statement or stored procedure will be executed (useful for DELETE, INSERT, UPDATE statements).
    Code:
    Function Test_SQL_PassThrough(ByVal ConnectionString As String, _
                                  ByVal SQL As String, _
                                  Optional ByVal QueryName As String)
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef
        With qdf
            .Name = QueryName
            .Connect = ConnectionString
            .SQL = SQL
            .ReturnsRecords = (Len(QueryName) > 0)
            If .ReturnsRecords = False Then
                .Execute
            Else
                If Not IsNull(dbs.QueryDefs(QueryName).Name) Then dbs.QueryDefs.Delete QueryName
                dbs.QueryDefs.Append qdf
            End If
            .Close
        End With
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Function

    I like the above code sample and have a couple questions. I'm using Access 2003, but can upgrade if needed.

    I need to understand the pass through queries because I get an ODBC timeout when using
    ADO calls, and the application I'm connecting to Sage 100, recommends using a pass
    through query. I've been programming for 25 years and have never created or needed a pass
    through query...now I'm excited about using this technique within Access.

    My first question:
    If I call your function like so Test_SQL_PassThrough("valid_connection_string", "select * from item_master", "select_item_master")

    Am I simply defining a query to use later in my code?

    Regards,

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First, I'm sorry for this very late answer but I was severely ill in the last three months.

    When you create a query in Access, using a QueryDef object, you can create a temporary query, that is a query that will only exists in the procedure (Sub of Function) in which you instanciate a QueryDef object. You do that by providing an empy string ("") as the name for the query (nameless query).
    Code:
    CurrentDb.CreateQueryDef("")
    Such a query will never appear in the database window and cannot be later used by another object (Form, Report or another query).

    When you provide a name for the QueryDef object:
    Code:
    CurrentDb.CreateQueryDef("MyQueryName")
    Here, you create a permanent query object in the database, i.e. this query will be visible in the database window and can be later used by another object (Form, Report or another query).
    However, as the procedure that creates this persistent (or permanent) query can be called more than once, you must check that the procedure does not try to crete a query that already exists, otherwise an error will occur.

    There are several ways of checking for the existence of a query:
    Code:
    If DCount("*", "MSysObjects", "name='" & "MyQueryName" & "'") > 0 Then
        ' --> a query named "MyQueryName" already exists in the database.
    Else
        '  --> a query named "MyQueryName" does not exist in the database.
    End If
    Or:
    Code:
    Public Function QueryExist(ByVal QueryName As String) As Boolean
    
        Dim qdf As QueryDef
        
        For Each qdf In CurrentDb.QueryDefs
            If qdf.Name = QueryName Then
                QueryExist = True
                Exit For
            End If
        Next qdf
        
    End Function
    Then:
    Code:
    If QueryExist"MyQueryName" & "'") = True Then
        ' --> a query named "MyQueryName" already exists in the database.
    Else
        '  --> a query named "MyQueryName" does not exist in the database.
    End If
    Please do not hesitate to come back for more if you need further explanations.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •