Results 1 to 4 of 4
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    Use stored procedures from database

    Hi,

    i am wondering what is the best way to use SP in postgresql/ms sql sever.

    Create new pass through query and if variables change - change SQL within this query?

    Or always use connection string and connect to database in VBA?



    How can i run command from VBA?

    Best,
    Jacek

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The simplest route is to use a pass through query and adjust the parameters on the fly. Something like this works with SQL Server;

    Code:
        Set db = CurrentDb
        Set qdfpt = db.QueryDefs("passYourPassThroughQuery")
        qdfpt.SQL = "EXEC dbo.YourStoredProcedure @YourParameter = " & Me.YourValue & ""
    
    
        db.Close
    Make sure you have set it to return records if it needs to return a result set.

    Doing it with a saved query means you can play with the syntax first.
    The use of the named parameter isn't strictly needed, but does help keep track of the usage months down the line, when something doesn't work.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Minty,

    thank you very much ! Great idea. I think that this is the quicker way !

    Best,
    Jacek

  4. #4
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Guys,

    i created pass through query and trying to run it:

    Code:
    Dim db As Object
    Dim qdfpt As QueryDef
    Dim SpecificRole, schemaVer As String
    
    
    SpecificRole = Environ("username")
    schemaVer = "test"
    
    
        Set db = CurrentDb
        Set qdfpt = db.QueryDefs("CreateSchema")
        qdfpt.SQL = "select * from public.alterrole('" & SpecificRole & "','" & schemaVer & "')"
    
    
        db.Close
    
    
    End Sub
    How to execute this sql? I have select query (i can not use exec in psotgresql).

    Best,
    Jacek

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

Similar Threads

  1. Replies: 4
    Last Post: 11-29-2017, 04:24 PM
  2. Replies: 3
    Last Post: 10-14-2017, 05:01 AM
  3. Queries, Views, Stored Procedures, Oh my!
    By ssanfu in forum SQL Server
    Replies: 7
    Last Post: 10-08-2017, 12:02 PM
  4. Set date parameters within Stored Procedures
    By Brian62 in forum SQL Server
    Replies: 0
    Last Post: 02-15-2012, 04:40 PM
  5. stored procedures failure
    By MrGrinch12 in forum Programming
    Replies: 1
    Last Post: 06-23-2010, 12:54 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