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

    Execute SP from Access as datasource

    Hi Guys,

    i need a little help here,

    I am trying to execute my stored Procedure from SQL Server and use it as data source.

    My code is:

    Code:
    Sub ConToSQL()
    
    
    Dim rs As Object
    Dim con As Object
    
    
    Set rs = CreateObject("ADODB.Recordset")
    Set con = CreateObject("ADODB.connection")
     
    With con
    .ConnectionString = "Provider=SQLOLEDB;Data Source=NT-WRO1-PLAT01\SQLEX2014;Initial Catalog=Sody;User ID=MyID;Password=MyPass"
    .Open
    If .State = 0 Then
    MsgBox "Nie mogę otworzyć polączenia", vbInformation, "Błąd:"
    End If
    End With
     
    With rs
    .ActiveConnection = con
    .CursorLocation = 3
    .CursorType = 3
    .LockType = 1
    .Open "Select * from tbl_Slownik"
    End With
    
    
    End Sub
    my SP name is SP_SQL.

    Instead of using:

    Code:
    .Open "Select * from tbl_Slownik"
    i want to use stored procedure here and pass the result into Access recordset.

    How can i do it?



    Jacek

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The simple way is to use a pass through query, if you set the pass through to Return Results then it will behave exactly the same as a read only query.

    The Pass through with a date parameter for the SP called Dated_Holiday_Report would look like

    Code:
    EXEC dbo.Dated_Holiday_Report  '2017-12-31'
    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
    Aa ok, pretty simple

    How can i pass variable "'2017-12-31'" into my created query using VBA?

    I do not want to create new query but input new SQL command into query.

    Thank you,
    Jacek

  4. #4
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    So to create / amend the process dynamically in VBA use something like ;
    Code:
        Dim db               As DAO.Database    
        Dim rs               As DAO.Recordset
        Dim qdfpt            As QueryDef
    
        Set db = CurrentDb
        Set qdfpt = db.QueryDefs("YourPassThroughQuery")
        qdfpt.Connect = "ODBC;DRIVER=SQL Server;SERVER=YourSever;;DATABASE=YourDatabse;UseTrustedConnection=True"
        qdfpt.SQL = "EXEC dbo.Dated_Holiday_Report  '" & Format(Me.txtStartDate, "yyyy-mm-dd") & "' "
    
        db.Close
    
        DoCmd.OpenQuery "YourPassThroughQuery", acViewNormal, acReadOnly
    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 ↓↓

  5. #5
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you Minty !!!

    You are the best!

    Jacek

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

Similar Threads

  1. QUERY in forms datasource wont work ??
    By ksor in forum Queries
    Replies: 3
    Last Post: 02-11-2017, 04:54 PM
  2. RunSQL vs. QueryDef.Execute vs. CurrentDB.Execute
    By caubetiep1337 in forum Queries
    Replies: 8
    Last Post: 12-16-2015, 05:35 PM
  3. Combobox datasource SQL question
    By hascons in forum Queries
    Replies: 3
    Last Post: 04-17-2014, 01:32 PM
  4. Change Datasource in Subform field
    By goodguy in forum Database Design
    Replies: 2
    Last Post: 04-08-2013, 03:00 PM
  5. Using an sql string as the datasource for a textbox.
    By rghollenbeck in forum Queries
    Replies: 5
    Last Post: 10-08-2011, 07:09 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